ADODB Excel to Access - .Item question

blazonQC

Board Regular
Joined
Jun 6, 2007
Messages
92
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:

Code:
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,235
Office Version
  1. 365
Platform
  1. Windows
Perhaps
Code:
rs.MoveNext
And also a check for EOF.

You might also want to look at CopyFromRecordSet.
 

blazonQC

Board Regular
Joined
Jun 6, 2007
Messages
92
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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:

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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,093
Members
414,501
Latest member
mdhaumyu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top