Solved

Access/VB ADO run time error 91.

Posted on 2002-07-11
7
341 Views
Last Modified: 2012-06-27
Hi,

I just started a project in Access 2000 and I'm having trouble with ADO recordsets.

I added the following code to a form's load sub...

Dim db as New ADODB.Connection
Dim rs as New ADODB.Recordset

db.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source=" & App.Path & "\db1.mdb;" & _
   "Persist Security Info=False"

...

When I try and run the form it bombs out on the db.Open line with  "run-time error 91 -- Object variable or with block variable not set"

I tried various variations all achieve the same result.
Do I need to set anything up in VB or Access to use ADO?
Can anyone help me out?

Cheers
Chris

0
Comment
Question by:cjwinks
7 Comments
 
LVL 11

Expert Comment

by:joekendall
ID: 7146168
Try this:

Dim db as ADODB.Connection
Dim rs as ADODB.Recordset

Set db = New ADODB.Connection
Set rs = New ADODB.Recordset

db.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
 "Data Source=" & App.Path & "\db1.mdb;" & _
  "Persist Security Info=False"

Thanks!

Joe
0
 
LVL 18

Expert Comment

by:deighton
ID: 7146350
are you opening a table in the current db or a second alien database?  If the former, I always use



Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly

rs.Open "select QualName FROM Qualifications WHERE TutorID = " & CStr(TutorId), CurrentProject.Connection


i.e. using CurrentProject.Connection as the exisitng connection to my database that i am in
0
 

Author Comment

by:cjwinks
ID: 7146355
Ta Joe,

Added those lines of code which got rid of the fisrt error, but I'm now gettin the folowing error...

"Run-Time Error '-2147467259(80004005)':

 The database has been placed in a state by user 'Admin' on machine 'WINDSOR' that prevents it from being opened or locked."

Any ideas?

Cheers
Chris
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:cjwinks
ID: 7146406
Ta Joe,

Added those lines of code which got rid of the fisrt error, but I'm now gettin the folowing error...

"Run-Time Error '-2147467259(80004005)':

 The database has been placed in a state by user 'Admin' on machine 'WINDSOR' that prevents it from being opened or locked."

Any ideas?

Cheers
Chris
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 7146476
the original problem arose becase THIS line was missing in the code that you orginally posted:

Set db = New Connection

You had declared db to be a Connection Object, but had NOT actually created an instance of the connection class, so db was UNDEFINED.

the second problem (related to the Open state of the db), usually means that a connection is currently open (maybe even one that you opened),but then the program died, so the connection (actually the .ldb file associated with the .mdb) was not properly closed, which makes it look as though the database is already open in Exclusive mode.
0
 
LVL 11

Expert Comment

by:joekendall
ID: 7146580
You can browse to the directory where your database is stored and find a ldb file with the same name as your database. You can delete this file, then try to run your code again.

Thanks!

Joe
0
 
LVL 11

Accepted Solution

by:
joekendall earned 50 total points
ID: 7146797
Did you try deleting the LDB file? Is your code working now?

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question