Solved

Access/VB ADO run time error 91.

Posted on 2002-07-11
7
342 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

740 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