ADODB Excel to Access - .Item question

[blazonQC]

Hi,

I am in the process of learning how to connect to an access dbase with ADO and i'm kinda stuck in a spot right now. From examples i've found around the net I came up with this block of code to read user info from the dbase:

Public Sub Retrieve_UserInfo()
    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sql As String
    Dim sMdbPath As String
    Dim i As Long
    Dim buf As Variant

    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                         & "Data Source=" & sFilename
    cnn.Open

    sql = "SELECT * FROM User_Info"
    rs.Open sql, cnn, adOpenStatic, adLockOptimistic
    

    
    With rs.Fields
         Worksheets(3).Cells(1, 1).Value = .Item("User").Name
         Worksheets(3).Cells(1, 2).Value = .Item("User").Value
         Worksheets(3).Cells(2, 1).Value = .Item("Key").Name
         Worksheets(3).Cells(2, 2).Value = .Item("Key").Value
    End With
    

    
    rs.Close
    cnn.Close
    
    Set rs = Nothing
    Set cnn = Nothing

End Sub

The code does exactly what it is supposed to. It gives no errors and returns the information requested. My problem is this though. In the dbase the "User_Info" table contains a list of usernames, user keys, login time and logout time like so:

| User | Key | Login | Logout |
| Marty C | m890c | etc. | etc. |
| Jimmy R | j734r | etc. | etc. |

The code above would only return:

| User | Marty C |
| Key | m890c |

Using an index number for .Item().Name/Value seems to be a column index. How would I go about moving down rows to retrieve additional Users and their keys? Thanks

Chad

 

[Norie]

Perhaps

rs.MoveNext

And also a check for EOF.

You might also want to look at CopyFromRecordSet.

 

[blazonQC]

Thanks Norie! If I ever get famous or write a book one day you'll definitley get a shout out from me. You deserve it

Chad

 

[SydneyGeek]

Good to see it's working for you. If I could make another suggestion:

Downloading data from Access to Excel is more efficient if you use the CopyFromRecordset method. Instead of having to loop through each record, you transfer data in bulk. You won't see much difference with a few records, but with thousands it makes a difference. Sample code:

TransferTableFromAccess()
   Dim cnn As ADODB.Connection
   Dim rst As ADODB.Recordset
   Dim fld As ADODB.Field
   Dim MyConn
   Dim i As Long
   Dim ShDest As Worksheet
   Const TARGET_DB = "DB_test1.mdb"

   'define the output sheet   
   Set ShDest = Sheets("Table download")

   Set cnn = New ADODB.Connection
   MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB

   With cnn
     .Provider = "Microsoft.Jet.OLEDB.4.0"
     .Open MyConn
   End With

   Set rst = New ADODB.Recordset
   rst.CursorLocation = adUseServer
   rst.Open Source:="tblUsers", _
            ActiveConnection:=cnn, _
            CursorType:=adOpenDynamic, _ 
            LockType:=adLockOptimistic, _
            Options:=adCmdTable

   'clear existing data on the sheet
   ShDest.Activate
   Range("A1").CurrentRegion.ClearContents

   'create field headers
   i = 0
   With Range("A1")
     For Each fld In rst.Fields
      .Offset(0, i).Value = fld.Name
      i = i + 1
    Next fld
   End With

   'transfer data to Excel
   Range("A2").CopyFromRecordset rst

   ' Close the connection and clean up references
   rst.Close
   cnn.Close
   Set rst = Nothing
   Set cnn = Nothing

End Sub

Denis