Looping through records (Access 2002)

Topic

New Reply

I want to loop through a set of records, editing where appropriate but keep hitting an error 13 – type mismatch in my code in the line shown below
The sample I am using has single table of names

The code I am using is driven by a button on a simple form as follows

Private Sub test_Click()
Dim db As Database
Dim rst As Recordset

Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset(“tblNames”, dbopentable) – THIS IS THE PROBLEM LINE
Do Until rst.EOF
MsgBox [tblNames].[fldname]
rst.MoveNext
Loop
End Sub

I have set references in Tools/References to the following
Visual basic for applications
MS Access 10.0 object library
OLE automation
MS ActiveX data object 2.1 library
MS DAO 3.6 object library
MS jet and replication object 2.6 library

I’d be grateful for any suggestions as to what I’m doing wrong!

Reply | Quote

Replies

Because you have references to both DAO and ADO (ActiveX Data Objects), the declaration Dim rst As Recordset is ambiguous: Recordset is both a DAO and an ADO object. Since ADO is above DAO in the list of references, VBA interprets it as an ADO recordset, but you can’t use that with the (uniquely DAO) Database object. The solution is to be explicit in your declarations:

Dim db As DAO.Database
Dim rst As DAO.Recordset

Note: Microsoft recommends using CurrentDb instead of DBEngine(0)(0):

Set db = CurrentDb

PS: Do you really need the Jet and Replication Objects library? It is rather specialized.

Reply | Quote

That was the answer – the more I learn the more I realise I need to learn!
I put in the jet reference because it was shown in a screen dump in an Access book talking about references – I’ve removed it again
Thankyou – I will now stop banging my head against the wall

Reply | Quote

That was the answer – the more I learn the more I realise I need to learn!
I put in the jet reference because it was shown in a screen dump in an Access book talking about references – I’ve removed it again
Thankyou – I will now stop banging my head against the wall

Reply | Quote

Because you have references to both DAO and ADO (ActiveX Data Objects), the declaration Dim rst As Recordset is ambiguous: Recordset is both a DAO and an ADO object. Since ADO is above DAO in the list of references, VBA interprets it as an ADO recordset, but you can’t use that with the (uniquely DAO) Database object. The solution is to be explicit in your declarations:

Dim db As DAO.Database
Dim rst As DAO.Recordset

Note: Microsoft recommends using CurrentDb instead of DBEngine(0)(0):

Set db = CurrentDb

PS: Do you really need the Jet and Replication Objects library? It is rather specialized.

Reply | Quote