Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

Access/VB ADO run time error 91.

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
cjwinks
Asked:
cjwinks
1 Solution
 
joekendallCommented:
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
 
deightonCommented:
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
 
cjwinksAuthor Commented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
cjwinksAuthor Commented:
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
 
Arthur_WoodCommented:
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
 
joekendallCommented:
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
 
joekendallCommented:
Did you try deleting the LDB file? Is your code working now?

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now