Access and ADO?

  • Thread starter Thread starter J S
  • Start date Start date
J

J S

I am new Microsoft Access and I have just started creating a database.

I am writing some VBA code in Access to help customize my database. In the
past I have always used the Abstract database object to query databases and
I would like to continue to use it when deal with the data in the access
database. Is there a way to do this?

I tried to connect to the access database via the following commands:
------------------------------------------------------------
Dim MyConn As New ADODB.Connection
Dim strConn As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
CurrentProject.FullName & ";Jet OLEDB:Database"
MyConn.Open strConn
------------------------------------------------------------

But I recieved the following error:
------------------------------------------------------------
The database has been pplaced in a state by user 'admin' that precents it
from being opened or locked.
------------------------------------------------------------

I assume this is because the database is already open and will always be
open anytime VBA code INSIDE the database is being used.

Any ideas?

-J
 
J S said:
I am new Microsoft Access and I have just started creating a database.

I am writing some VBA code in Access to help customize my database.
In the past I have always used the Abstract database object to query
databases and I would like to continue to use it when deal with the
data in the access database. Is there a way to do this?

I tried to connect to the access database via the following commands:
------------------------------------------------------------
Dim MyConn As New ADODB.Connection
Dim strConn As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
CurrentProject.FullName & ";Jet OLEDB:Database"
MyConn.Open strConn
------------------------------------------------------------

But I recieved the following error:
------------------------------------------------------------
The database has been pplaced in a state by user 'admin' that
precents it from being opened or locked.
------------------------------------------------------------

I assume this is because the database is already open and will always
be open anytime VBA code INSIDE the database is being used.

Any ideas?

-J
Click to expand...

If you really want to use ADO, you can use the existing open connection
to the database, instead of opening a new one, by way of code like this:

Dim MyConn As ADODB.Connection
Set MyConn = CurrentProject.Connection

However, so long as you're working in an .mdb file and not an .adp, you
can still use DAO code to work with the current database. DAO is more
efficient and more powerful than ADO when working with Jet databases.
Although Access 2000 and 2002 don't include a reference to DAO by
default, you can add one via the VB Editor's Tools -> References...
dialog -- just put a check mark next to Microsoft DAO 3.6 Object
Library.

If you plan to use DAO *instead* of ADO, you can remove the check mark
next to the ActiveX Data Objects 2.x Library. If you intend to use
both, then you should leave the ADO reference intact but you must be
sure to qualify the declarations of those objects that appear in both
libraries: Connection, Error, Errors, Field, Fields, Parameter,
Parameters, Property, Properties and Recordset. So instead of

Dim rs As Recordset

you would write either

Dim rs As DAO.Recordset
or
Dim rs As ADODB.Recordset
 
Back
Top