Solved

Access/VB ADO run time error 91.

Posted on 2002-07-11
7
338 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:cjwinks
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Did you try deleting the LDB file? Is your code working now?

0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now