Solved

Access/VB ADO run time error 91.

Posted on 2002-07-11
7
339 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

910 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

23 Experts available now in Live!

Get 1:1 Help Now