ADO connection problem

  • Thread starter Thread starter Len
  • Start date Start date
L

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.
 
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.
 
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
 
Douglas said:
I don't believe that CurrentProject.Connection returns a connection string.
Instead, it returns a reference to the actual connection itself.
Click to expand...

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
 
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.
 
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 OLEDB:Database
Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database 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 OLEDB:Don't Copy Locale on Compact=False;Jet
OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False

Thanks,
Len
 
Why use a connection string?

dim cn as new adodb.connection
set cn=currentproject.connection
'do something
cn.close
 
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
 
Back
Top