Quick Reference- Working with Recordsets VBA

Sub testSqlRuns()

    'Purpose:   Open a recordset using ADO.

    Dim rs As New ADODB.recordSet

    Dim cn As New ADODB.Connection

   

    Dim strSQL As String

    Dim strConnString As String

   

    strConnString = "Provider=SQLOLEDB;Data Source=US440;" & _

                  "Initial Catalog=Test;" & _

                  "User Id=user;" & _

                  "Password=pwd;"

                 

    strSQL = "SET NOCOUNT ON;" & vbCrLf & _

            "SELECT TOP 5 * FROM Issue"

   

    cn.Open strConnString

   

    ' record count will be -1 for forward only recordsets

    ' adOpenForwardOnly is default and fastest. You can go through the recordset start to finish once.

    '       Once you reach EOF you can not use the recordset.

    '       Best for when you need to do one pass through the data

    rs.Open strSQL, cn, adOpenForwardOnly

    Debug.Print rs.RecordCount

    rs.Close

    Set rs = Nothing

   

    Set rs = New ADODB.recordSet

    ' You need to specify adUseClient to tell data provider that you are going to be browsing records

    '       on the excel client (vs SQL Server)

    rs.CursorLocation = adUseClient

    ' adOpenStatic takes a snapshot of data as of the time command was run.

    '       Allows parsing recordset any way you want. Will not reflect changes to data,
     '      but this is ok.

    

    rs.Open strSQL, cn, adOpenStatic

    Debug.Print rs.RecordCount

   

    Do While Not rs.EOF

        Debug.Print rs.Index & ";" & rs!issue_name

        rs.MoveNext

    Loop

   

    rs.Close

    Set rs = Nothing

End Sub