Help in connecting to a Recordset in Access 2002

[Graham M Haines]

In access 97 I could connect to a table or query using the code below:

Private Sub Command0_Click()
Dim mdb As Database
Dim rst As Recordset

Set mdb = CurrentDb
Set rst = mdb.OpenRecordset("Table 1", dbOpenDynaset)

With rst
'Perform A Loop on the records
End With

Set rst = Nothing
Set mdb = Nothing

End Sub

But in Access 2002 this comes up with an "Type Mismatch" error at the Set
rst code, even though the code when typed in seems ok

Regards

Graham

Graham Haines

 

[Dirk Goldgar]

In access 97 I could connect to a table or query using the code below:

Private Sub Command0_Click()
Dim mdb As Database
Dim rst As Recordset

Set mdb = CurrentDb
Set rst = mdb.OpenRecordset("Table 1", dbOpenDynaset)

With rst
'Perform A Loop on the records
End With

Set rst = Nothing
Set mdb = Nothing

End Sub

But in Access 2002 this comes up with an "Type Mismatch" error at the Set
rst code, even though the code when typed in seems ok

That's because Access 2002 doesn't include a reference to the DAO object
library by default -- you have to add it. A2002 does automatically include
a reference to the ADO (ActiveX Data Objects) library, and that library also
defines a Recordset object, but one that is not compatible with the DAO
recordset object that CurrentDb.OpenRecordset returns. Here's my standard
write-up on the subject:

Both the DAO and the ADO object libraries define a Recordset object. By
default, Access 2000-2002 sets a reference to ADO and not to DAO. Even if
you later add a reference to DAO, it defaults to a lower priority than the
ADO reference, though you can move it up in the priority list.

Therefore, by default, a declaration such as "Dim rs As Recordset" is going
to be declaring an ADO recordset. However, the Recordset and RecordsetClone
of a form in an MDB file are DAO recordsets. Hence, you get a type mismatch
when you try to assign the form's (DAO)
RecordsetClone to the (ADO) recordset you've declared.

To correct this, be sure you've added a reference to the Microsoft DAO 3.6
Object Library (via the Tools -> References... dialog in the VB Editor), and
either remove the ADO (Microsoft ActiveX Data Objects) reference -- if
you're not going to use it -- or qualify your
declaration of DAO objects with the "DAO." prefix, like this:

Dim rs As DAO.Recordset

Incidentally, ADO also defines some other object names that exist in the DAO
library as well. These, too, have to be disambiguated if you use them when
you have references set to both libraries:

Connection
Error
Errors
Field
Fields
Parameter
Parameters
Property
Properties
Recordset

Note: the following objects exist with the same names in the ADOX and DAO
models as well:

Group
Groups
Index
Indexes
Property
Properties
User
Users

 

[James A. Fortune]

In access 97 I could connect to a table or query using the code below:

Private Sub Command0_Click()
Dim mdb As Database
Dim rst As Recordset

Set mdb = CurrentDb
Set rst = mdb.OpenRecordset("Table 1", dbOpenDynaset)

With rst
'Perform A Loop on the records
End With

Set rst = Nothing
Set mdb = Nothing

End Sub

But in Access 2002 this comes up with an "Type Mismatch" error at the
Set rst code, even though the code when typed in seems ok

Regards

Graham

Graham Haines

Try:

Dim mdb As DAO.Database
Dim rst As DAO.Recordset

You probably have the reference for ADO above the reference for DAO
(Note: you don't have to change their order when using the code above).
Also, make sure that the reference for DAO is checked. Your problem
is common in conversions from A97 to A02 when objects are imported into
a new A02 database without using the built-in database conversion
option. Using that option should make the changes above unnecessary.

James A. Fortune
(e-mail address removed)