"Set Rst = DB.OpenRecordset(SQL)" results in Type Mismatch error.

[Dor100]

I've been working (at work) in Access 97 for a few years and have used VBA recordset objects perhaps a thousand times. When I tried to do it at home in Access 2002, I got an impassable and inexplicable error. Here's the basic code:

Dim db as Database, rst as Recordset, sql as String
set db = CurrentDB
sql = "select Table.Anything from Table;"
set rst = db.OpenRecordset(sql)
...etc...etc...

When I go to run it, it stops at the "set rst..." line with a "Type Mismatch" error. Does anyone have a clue? I've done this and more complicated ones more times than I could remember in 97, and the 2002 Help documentation shows that this classic syntax should work.

 

[cmmrfrds]

Make sure you have a library reference set to the DAO library since the default in 2000 and above is the ADO library, then explicitly define the data objects with the library.

Dim db as DAO.Database, rst as DAO.Recordset, sql as String

 

[Dor100]

That sounds promising. I did have to check a DAO reference to begin with just to get the Dim...as Database option. I do hope this doesn't mean I'll have to do it that way all the time, though. That would be a drag. Since I think we may be getting 2002 at work soon, I did want to be prepared.

Thanks for this input. I'll try it tonight.

 

[JimInKS]

I am also converting from Access 97 to 2002 and have not really had any problems. I don't remember how I installed, but looking just now at references I have Microsoft DAO 3.6 Object Library loaded and no references to ADO libraries. All my legacy code so far seems to run just fine.

 

[Dor100]

That's the same reference I also checked, but this error is occurring for new code. It's discouraging, but I can't wait to try the suggestion above.

 

[VicM]

Dor

Move the DAO reference above the ActiveX reference. Both DAO and ADO have a recordset object, but they do not have the same arguments. So if the ADO reference is above the DAO reference, Access will choose the ADO recordset object.

But as cmmrfrds suggested, if you precede the objects with DAO it doesn't matter where the reference resides in the References list. However, I find it more efficient to move the DAO above the ADO do I won't have to preface objects with DAO.

Also, if you remove the ADO reference that should work as well, unless somewhere in the code you are unknowingly referencing an ADO object. Then the code will fail again.

HTH,

Vic