ADO connection problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Len - 11 Mar 2008 04:33 GMT

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.

Reply to this Message

Tom van Stiphout - 11 Mar 2008 06:20 GMT

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 am getting an error # 3219 Invalid operation when opening a connection. The
>code is as follows:
[quoted text clipped - 7 lines]
>
>What can be the problem? Thanks.

Reply to this Message

Len - 11 Mar 2008 17:31 GMT

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.

> When I try your code in A2007 I get:
> The database has been placed in a state by user 'Admin' on machine
[quoted text clipped - 18 lines]
> >
> >What can be the problem? Thanks.

Reply to this Message

Douglas J. Steele - 11 Mar 2008 12:25 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>I am getting an error # 3219 Invalid operation when opening a connection.
>The
[quoted text clipped - 8 lines]
>
> What can be the problem? Thanks.

Reply to this Message

Robert Morley - 11 Mar 2008 16:43 GMT

> 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

Reply to this Message

Len - 11 Mar 2008 21:08 GMT

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

> > I don't believe that CurrentProject.Connection returns a connection string.
> > Instead, it returns a reference to the actual connection itself.
[quoted text clipped - 19 lines]
> Thanks,
> Rob

Reply to this Message

Robert Morley - 11 Mar 2008 22:35 GMT

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

Reply to this Message

Len - 11 Mar 2008 17:45 GMT

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

> I don't believe that CurrentProject.Connection returns a connection string.
> Instead, it returns a reference to the actual connection itself.
[quoted text clipped - 27 lines]
> >
> > What can be the problem? Thanks.

Reply to this Message

Douglas J. Steele - 11 Mar 2008 19:23 GMT

Did you read Robert's post to this thread?

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Doug, I've tried both of your suggestions, but as a result the same error
> has
[quoted text clipped - 33 lines]
>> >
>> > What can be the problem? Thanks.

Reply to this Message

Ralph - 11 Mar 2008 21:11 GMT

Why use a connection string?

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

> Did you read Robert's post to this thread?
>
[quoted text clipped - 35 lines]
> >> >
> >> > What can be the problem? Thanks.

Reply to this Message