Type Mismatch on db.OpenRecordset

[Bilbo_Baggins_Esq]

Argh, I know I am missing something simple, but for some reason, I am getting a "Type Mismatch" error on this code:

Dim ws As Workspace, db As Database, rs As Recordset
Set ws = DBEngine.Workspaces(0)
Set db = ws.Databases(0)
[COLOR="Red"][B]Set rs = db.OpenRecordset("T_I_Adjust_Count_Of_Renters", dbOpenDynaset)[/B][/COLOR]

This is an Access 2003 DB running in Access 2007 on XP
The query being called is a "Totals" query.
Both it and the underlying table are "visible".

as always, any help is appreciated.

 

[pbaldy]

Make sure the DAO reference is checked in Tools/References and then disambiguate the declarations:

Dim ws As Workspace, db As DAO.Database, rs As DAO.Recordset

 

[midmented]

Maybe the unscores/spaces in the name are causing the issue?

I agree, make sure you have the reference for DAO. Open a VBA code module, Tools, References, Microsoft DAO x.x Object Library.

Dim rs as DAO.Recordset
Set rs = Currentdb.OpenRecordset("T_I_Adjust_Count_Of_Renters")

Is T_I_Adjust_Count_Of_Renters a table or a query?

 

[Bilbo_Baggins_Esq]

Microsoft DAO 3.6 Object Library is checked
new code:

Dim ws As Workspace, db As DAO.Database, rs As DAO.Recordset
Set ws = DBEngine.Workspaces(0)
Set db = ws.Databases(0)
Set rs = db.OpenRecordset("T_I_Adjust_Count_Of_Renters", dbOpenDynaset)

Still no joy...

8^(

Interestingly, if I change dbOpenDynaset to Dynaset, I get "data Type Conversion Error".

T_I_Adjust_Count_Of_Renters is a Totals Query and it is not hidden
There is only one underlying table and it is not hidden either.
(there are many tables and queries which are hidden, but not these)

For troubleshooting, I have tried to get it to use a straight select query with the same result.

I have the same exact code in anotehr db and it works fine.

Argh!

what the bleep is the issue here?

 

[pbaldy]

For giggles, let's take the workspace out of it and just do:

Set db = CurrentDb()

I assume the query opens normally from the db window?

 

[Bilbo_Baggins_Esq]

Hmmm, I should known to check this, but forgot that I've seen this happen before.

I unchecked everything and clicked OK
Then I rechecked everything and now it works.

d'OH!

now checked library references are:
Visual Basic for Applications
Microsoft Access 12.0 Object Library
OLE Automation
Microsoft ActiveX Data Object 2.1 Library
Microsoft DAO 3.6 Object Library
Microsoft Jet and Replication Objects 2.6 Library

Same as before, but now it works.
I've seen this happen before in some official cases, but wish I knew what caused it.

 

[pbaldy]

Ah, good catch. Thanks for posting the final solution.

 

[midmented]

Yes, I've had it happen myself. Interestly enough, I've fixed this in another way also.
I changed the order of the references and have sometimes had to change the order back. Yes, good catch!
I do have a question about the code posted though. I've always felt "less code is better" (minus error traps) and have been not using all of the set statements. Taking out set db, ws, and simply using rs.
Does it help performance or help in any other way to "spell it out" using 4/5 lines instead of the 2? I'm not knocking on the code but was just wondering if there was a difference.

Dim ws As Workspace, db As DAO.Database, rs As DAO.Recordset
Set ws = DBEngine.Workspaces(0)
Set db = ws.Databases(0)
Set rs = db.OpenRecordset("T_I_Adjust_Count_Of_Renters", dbOpenDynaset)

Vs:

Dim rs as DAO.Recordset
Set rs = Currentdb.OpenRecordset("T_I_Adjust_Count_Of_Renters")

 

[dfenton]

Bilbo_Baggins_Esq listed these references:

x Visual Basic for Applications
x Microsoft Access 12.0 Object Library
OLE Automation
Microsoft ActiveX Data Object 2.1 Library
x Microsoft DAO 3.6 Object Library
Microsoft Jet and Replication Objects 2.6 Library

Are you really using all those libraries? The ones with the x by them are the minimum for any Access application. If you're not using ADO, why have a reference to it? If you *are* using ADO, then I have to ask WHY?

The OLE Automation reference is necessary only if you're using things like the Office File Open dialog, which can easily be avoided by using an API call for File Open operations.

JRO is a pile of crap that should not exist in the first place, and wouldn't exist if not for Microsoft's stupid campaign to replace DAO with ADO -- JRO had to be created to support Jet-specific functionality that was not a part of ADO (which was a generic db abstraction level intended for all database engines). The only possible reason I can think for using JRO is to initiate an indirect synch in code without using an outside component like the TSI Synchronizer. Otherwise, it simply has no functionality at all that is not already provided by DAO.

All of my apps use only the 3 default references and any other library that I need is accessed via late binding. This makes it much, much less likely for the app to break when moved from machine to machine.

 

[dfenton]

Bilbo_Baggins_Esq listed these references:

x Visual Basic for Applications
x Microsoft Access 12.0 Object Library
OLE Automation
Microsoft ActiveX Data Object 2.1 Library
x Microsoft DAO 3.6 Object Library
Microsoft Jet and Replication Objects 2.6 Library

Are you really using all those libraries? The ones with the x by them are the minimum for any Access application. If you're not using ADO, why have a reference to it? If you *are* using ADO, then I have to ask WHY?

The OLE Automation reference is necessary only if you're using things like the Office File Open dialog, which can easily be avoided by using an API call for File Open operations.

JRO is a pile of crap that should not exist in the first place, and wouldn't exist if not for Microsoft's stupid campaign to replace DAO with ADO -- JRO had to be created to support Jet-specific functionality that was not a part of ADO (which was a generic db abstraction level intended for all database engines). The only possible reason I can think for using JRO is to initiate an indirect synch in code without using an outside component like the TSI Synchronizer. Otherwise, it simply has no functionality at all that is not already provided by DAO.

All of my apps use only the 3 default references and any other library that I need is accessed via late binding. This makes it much, much less likely for the app to break when moved from machine to machine.