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