recordset question

[jlstrick]

I am new to VBA and Access. I'm trying to write a VBA app within Access. I need to query a database and step thru the results one record at a time for processing. I've picked up that I can do this with ADO and a recordset. Does anyone have specific example code to do this? I would greatly appreciate any help .

[M Owen]

Search this forum or goto www.codeguru.com in the VB forum there and do a search There is TONS! of ADO code floating around ...

[alex_read]

' You'll need to go upto the Project > References menu & set
' the Microsoft ActiveX Data Objects x.0 box on for this to work
 
Private Sub Form_Load()
    Dim cnnAccessDb As ADODB.Connection
    Dim rsCartoons As ADODB.Recordset
    Dim strCnnString As String
    Dim strSQL As String
    
    ' Setup an SQL statement to filter the information returned
    ' from the database, then the connection string to tell ADO
    ' where & how to connect to the database (if you don't know
    ' hoiw to set these, do a forum seach for 'UDL' with my name).
    strSQL = "SELECT * FROM field1 WHERE field1 = 'DBZ'"
    strCnnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\AccessDatabaseName.mdb"
    
    ' Setup the connection to the database
    Set cnnAccessDb = New ADODB.Connection
    
    With cnnAccessDb
        .CursorLocation = adUseClient
        .ConnectionString = strCnnString
        .Open
    End With
    
    ' Now setup the recordset of information.
    Set rsCartoons = New ADODB.Recordset
    
    rsCartoons.Open strSQL, cnnAccessDb
    
    ' Perform a loop to go through each of the records/rows in the
    ' database until the End Of File, or last record is reached. For
    ' each one found, print the value of the first column named field1
    ' into the immediate window, then move to the next record.
    Do Until rsCartoons.EOF
        Debug.Print rsCartoons!Field1
        rsCartoons.MoveNext
    Loop
    
    rsCartoons.Close
    Set rsCartoons = Nothing
    
    cnnAccessDb.Close
    Set cnnAccessDb = Nothing
End Sub

[jlstrick]

That worked beautifully...thanks!!!