ADO connection problem

[Len]

I am getting an error # 3219 Invalid operation when opening a connection. The
code is as follows:

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
With cn
.ConnectionString = CurrentProject.Connection
.Open
End With

What can be the problem? Thanks.

 

[Tom van Stiphout]

On Mon, 10 Mar 2008 20:33:01 -0700, Len

When I try your code in A2007 I get:
The database has been placed in a state by user 'Admin' on machine
'DEV99' that prevents it from being opened or locked.

I don't know why someone would want to open a second connection, if
CurrentProject.Connection was already available.
Another observation is that typically we would not want to open
ourselves, but our back-end database.

-Tom.

 

[Douglas J. Steele]

I don't believe that CurrentProject.Connection returns a connection string.
Instead, it returns a reference to the actual connection itself.

Try either

Dim cn As ADODB.Connection

Set cn = CurrentProject.Connection

or

Dim cn As ADODB.Connection

Set cn = New ADODB.Connection
With cn
.ConnectionString = CurrentProject.Connection.ConnectionString
.Open
End With

 

[Robert Morley]

I don't believe that CurrentProject.Connection returns a connection string.
Instead, it returns a reference to the actual connection itself.

This is one of those "it does, but it doesn't" things. The reason is that
the default property for the Connection object is ConnectionString. Since
the assignment is using a standard "=" instead of a "Set ... =", the default
property is retrieved, not the Connection object itself; it's just not
obvious that that's what's going on when you look at the code. Your second
suggestion is definitely more explicit and easier to understand.

You can prove this simply by doing a "Debug.Print
CurrentProject.Connection". So, all things being equal, the code as-written
*should* work fine (and does for me).

To the OP: Access does occasionally do some funky things with it's
connection string. If Doug's first suggestion doesn't work, or is
inappropriate for some reason (i.e., you want to open a second connection,
as opposed to re-using the same connection), can you please post the results of:

Debug.Print CurrentProject.Connection.ConnectionString


Thanks,
Rob

 

[Len]

I am opening connection in order to execute action queries. I do not have
problems using DAO to do that. But my action (parameter) queries are applied
to SQL Server data. So, I am forced to use ADO.

 

[Len]

Doug, I've tried both of your suggestions, but as a result the same error has
occured.

 

[Douglas J. Steele]

Did you read Robert's post to this thread?

 

[Len]

Please find below the results:

Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data
Source=G:\SQL\Test.mdb;Mode=Share Deny None;Extended Properties="";Jet
OLEDB:System database=C:\Documents and Settings\leonid.GELSPICE\Application
Data\Microsoft\Access\System.mdw;Jet OLEDB:Registry
Path=SOFTWARE\Microsoft\Office\10.0\Access\Jet\4.0;Jet OLEDBatabase
Password="";Jet OLEDB:Engine Type=5;Jet OLEDBatabase Locking Mode=1;Jet
OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet
OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet
OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet
OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False

Thanks,
Len

 

[Ralph]

Why use a connection string?

dim cn as new adodb.connection
set cn=currentproject.connection
'do something
cn.close

 

[Robert Morley]

It's been a while since I've used Jet, but as I recall, some of these are
incompatible with being assigned to a new connection string. Try manually
trimming the string down to this and see if it works:

Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data
Source=G:\SQL\Test.mdb;Jet OLEDB:System database=C:\Documents and
Settings\leonid.GELSPICE\Application Data\Microsoft\Access\System.mdw

This is probably a little bit of a drastic cut, but if this doesn't work,
probably nothing will.

If it *does* work, if you really want to, you can try re-instating the
various entries (separated by semicolons) and see if you can figure out
which one is the problem. At that point, you can more easily use the
existing ConnectionString and just filter out the offending part.

That said, Douglas & Ralph have a strong point that unless you really NEED
to use a second connection (which is slower and uses more system resources),
you're best off using the existing connection with the aforementioned "Set
cn = CurrentProject.Connection".


Rob