MISquared
asked on
MS Access 2010 Project Runtime Error (DAO recordset)
Hello,
I created an MS Access project in 2010. When I use common code to initialize a dao.recordset, I get "Run-time error '91': Object variable or With block variable not set".
My test code is as follows:
__________________________ __________ __________ __________ __________ ____
Private Sub Command0_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblMyTab le", dbOpenDynaset)
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
End Sub
__________________________ __________ __________ __________ __________ _________
This code produces the same runtime error using 64-bit Access on 64-bit Windows and using 32-bit Access on 64-bit Windows. I thought at first it might be an issue with the architecture of the system, but it does not seem to be the case. It bombs out at "set rst = db.openrecordset..."
I also checked to make sure I have the proper references set, which I think I do (see attached screenshot).
Any guidance will be appreciated.
References.jpg
I created an MS Access project in 2010. When I use common code to initialize a dao.recordset, I get "Run-time error '91': Object variable or With block variable not set".
My test code is as follows:
__________________________
Private Sub Command0_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblMyTab
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
End Sub
__________________________
This code produces the same runtime error using 64-bit Access on 64-bit Windows and using 32-bit Access on 64-bit Windows. I thought at first it might be an issue with the architecture of the system, but it does not seem to be the case. It bombs out at "set rst = db.openrecordset..."
I also checked to make sure I have the proper references set, which I think I do (see attached screenshot).
Any guidance will be appreciated.
References.jpg
You need a reference set to the MS DAO Library (not ADO).
Private Sub Command0_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblMyTab le", dbOpenDynaset)
rst.Close
' db.Close ' don't do this. You can't Close CurrentDb - instance of Access open.
Set rst = Nothing
Set db = Nothing
End Sub
Do you have a Reference set (Tools>>References) to DAO ?
mx
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblMyTab
rst.Close
' db.Close ' don't do this. You can't Close CurrentDb - instance of Access open.
Set rst = Nothing
Set db = Nothing
End Sub
Do you have a Reference set (Tools>>References) to DAO ?
mx
ASKER
Do you know what the dll name and path are? I think I have the appropriate references selected. I attached a screenshot of my references...if you could take a look.
I am unable to select DAO 3.6. This is on my 64-bit Win7 system running 64-bit Access.
I am unable to select DAO 3.6. This is on my 64-bit Win7 system running 64-bit Access.
ASKER
From what I understand, "Microsoft Office 14.0 Access Database Engine object" is the DAO reference for my setup, and I have that selected as a reference.
Ah ... 64 Bit Access ... well, not even recommend by Microsoft ...
OK ... I don't see a reference to DAO ... standby for screen shot ...
I would look like Microsoft DAO36 ...
mx
OK ... I don't see a reference to DAO ... standby for screen shot ...
I would look like Microsoft DAO36 ...
mx
I'm checking my Win7 X64 O2010 system now for Reference ...
mx
mx
ASKER
"Ah ... 64 Bit Access ... well, not even recommend by Microsoft ..."
Which is why I mentioned that it also doesn't work in an install of 32-bit that we have on another machine. The OS of the other machine is 64-bit, but that should not come into play.
Which is why I mentioned that it also doesn't work in an install of 32-bit that we have on another machine. The OS of the other machine is 64-bit, but that should not come into play.
ASKER
I am unable to select DAO 3.6. This is on my 64-bit Win7 system running 64-bit Access.
From what I understand, "Microsoft Office 14.0 Access Database Engine object" is the DAO reference for my setup, and I have that selected as a reference.
From what I understand, "Microsoft Office 14.0 Access Database Engine object" is the DAO reference for my setup, and I have that selected as a reference.
OK ... yes, looks like O 14 Access Database Engine ref IS DAO.
So, not sure what is going on. There is nothing I see wrong with that code. My comment about db.Close was a side note btw. That error is usually pretty specific, not usually an indication of a missing Ref, etc. So ... something else is going on ...
Try just this for kicks - instead of the other syntax.
Private Sub Command0_Click()
With CurrentDB.OpenRecordset("t blMyTable" , dbOpenDynaset)
' some code
End With
End Sub
So, not sure what is going on. There is nothing I see wrong with that code. My comment about db.Close was a side note btw. That error is usually pretty specific, not usually an indication of a missing Ref, etc. So ... something else is going on ...
Try just this for kicks - instead of the other syntax.
Private Sub Command0_Click()
With CurrentDB.OpenRecordset("t
' some code
End With
End Sub
ASKER
Good suggestion, but it results in the same error at the OpenRecordset command.
This should be so simple, but I cannot see what's causing this...on two machines. I even built a new .adp on the other machine (to the same SQL backend), created one form with one button, ran it, and received the same error.
Could it be anything with the backend? It loads in just fine when I open the project. I can double click that table and open it from within the project.
This should be so simple, but I cannot see what's causing this...on two machines. I even built a new .adp on the other machine (to the same SQL backend), created one form with one button, ran it, and received the same error.
Could it be anything with the backend? It loads in just fine when I open the project. I can double click that table and open it from within the project.
Sorry, I really don't know. mbizip just posted this in another thread - about this thread:
"Does 64 bit Access 2010 support DAO (my understanding is that it does not)?"
mx
"Does 64 bit Access 2010 support DAO (my understanding is that it does not)?"
mx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> Does 64 bit Access 2010 support DAO
Absolutely.
It'd be laid waste without it.
VBA7 requires some subtly different declarations for API calls in a 64 bit Office installation. And you can kiss ActiveX controls goodbye.
There's no compelling reason to use Office 64bit at this time. I avoid it personally.
Absolutely.
It'd be laid waste without it.
VBA7 requires some subtly different declarations for API calls in a 64 bit Office installation. And you can kiss ActiveX controls goodbye.
There's no compelling reason to use Office 64bit at this time. I avoid it personally.
ASKER
Thanks, I will try this in the morning and let you know what we get.