We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now


Need help with ADODB connection. Copying its value.

spoowiz asked
Medium Priority
Last Modified: 2010-04-07
I just made a change to use cnn connection so I can open either MSAccess or mySql database.
I open a connection to MS Access database (cnADB).
Then I assign its value to another connection variable (Set cnn = cnADB)
Upon some user request, I open another connection to a mySql database (cnVC).

Because I want to start reading from mySql db instead of MS Access db (identical db layouts),
I close cnn connection (cnn.close, set cnn=nothing), then
assign cnVC to cnn (Set cnn=cnADB)

cnn is defined:
Dim cnn     As New ADODB.Connection

cnADB and cnVC are defined in a module as public:
Public cnVC     As New ADODB.Connection
Public cnADB    As New ADODB.Connection

So far so good. Program mostly behaves well.

Where it goes wrong: When I go into another (child) form an try to open another table using cnADB connection, I get an error that says connection is either closed or invalid. (Of course, it worked fine, before I made changes to assign cnADB to cnn).

In debug, cnADB value still looks fine.

Any idea? Thanks.
Watch Question


When I close cnn, am I closing cnADB?
If so, what command do I use to assign the value of cnADB to cnn, yet be independent of each other?

Do you need to close the connection to the Access database if you are going to query it later?
Why don't you just create separate connections for each database and leave them open while your app is open? You can have both of the open at once. You just have to make sure your recordset.open statements etc refer to the appropriate connection.
Maybe I misunderstand?


Yes. That's the other option I already thought about.
But I also think that theoretically, what I am trying to do is possible and should be rather straight forward.
I wanted to know why it didn't work for "future reference"

Oh I see what you are trying to do now- because you have identical DBs you are trying to reuse your code for each one by having a common connection name in the recordset.open etc statements.

I wouldn't think that closing cnn would also close cnADB. However just setting cnn = cnADB may not actually open cnn, you may need to reopen cnn as a connection as the set statement may not do this.

Every time you reset cnn = cnADB or cnVC try testing the state of the connection

set cnn = cnADB
if cnn.state = adStateClosed then cnn.open



closing cnn does close cnADB also. (cnadb.state becomes 0 as soon as cnn.close is executed).
so how do I create another connection (cnn) which is not the same as cnADB?

Why not have a single connection but alter it every time you need to change from your Access to mySQL and back

Put this somewhere like a master form that is always loaded or in a .bas module if you have one in your project
'global standard connection
  Public cnn As ADODB.Connection

Then put a procedure like this somewhere

Public Sub ChangeConnection(bAccess as boolean)
 dim sConnString As String

if bAccess then

  'load connection string
    sDataBaseName = "Quotes.mdb"
    sConnString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Docs\" & sDataBaseName & ";Jet OLEDB:Database Password= "

end sub

      Set dbConn = New Connection
      dbConn.Open sConnString

Hang on - that got posted before I was finished - ignore that,  read this

Why not have a single connection but alter it every time you need to change from your Access to mySQL and back?

Put this somewhere like a master form that is always loaded or in a .bas module if you have one in your project
'global standard connection
  Public cnn As ADODB.Connection
   Set cnn = New ADODB.Connection

or declare it
Public cnn  As New ADODB.Connection
if you prefer

Then put a procedure like this somewhere that is always loaded

Public Sub ChangeConnection(bAccess as boolean)
 dim sConnString As String

if bAccess then
     'load access connection string
     sDataBaseName = "YourDB.mdb"
     sConnString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path   &  sDataBaseName & ";Jet OLEDB:Database Password= "

    'load mySQl connection path
     sDataBaseName = "YourDB""
     sConnString = whatever it is for the mySQl provider

      if cnn.state = adstateopen then cnn.close
       cnn.Open sConnString

end sub

then call the sub from where you need it

ChangeConnection True ' for Access
ChangeConnection False ' for mySQL

Haven't tested this but think it might work.



super - you're going on a tangent. i can make it work by opening 2 connections.
what i'm after now is simply:
how can i copy the cnADB value to cnn? so cnADB doesn't close when cnn is closed.
apparently "set cnn = cnadb" make them equal, and not a copy.
Hmmm sorry - I can't see a way around this, I don't know another way of doing this that doesn't involve setting cnn=cnadb. If closing cnn also closes cnadb then you will need to reopen cnadb programmatically each time you want to use it.  Maybe someone else will have the solution - my apologies for not being of more use

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
If you create a method to swap your connections, and pass the connection ByVal then wouldn't this simply take a copy of the cnAdb (or cnVC) object and use that within the method, therefore cnn does not become cnAdb and therefore closing cnn shouldn't close the other object......(I hope all that was clear!)

Something like

Private Sub ChangeConnection(byval newCnn as ADODB.Connection)
    If cnn.state = adstateopen then cnn.close
    Set cnn = newCnn
End Sub

and then call it like

' at this point we need to swap connections
Call ChangeConnection cnAdb
' now we change again
Call ChangeConnection cnVC


I found that the easiest way to "get it to work" is:
   set cnn = cnADB
   set cnn = cnVC
WITHOUT closing any connection. Works fine, including using cnADB and cnVC at the same time.

I still want to know, if possible, to create a copy of another connection so closing cnn does not close cnADB.
I don´t think that this is possible, cause it should not be possible to make copies of a Connectoin Object
this "set" statement just copies a pointer (reference) to the connection object. So it does not care with  which variable you call "Close" The same Function on the same data will be executed.

The Connection Object stores additional information about the state of a transaction for example. And the ConnectState it self. So you the Set x=y statement would have had to open a second "Connection" to the database.

But you have already 2 open connections. If you don´t want to close the Connection (Terminate Connect between Program and DB) you just have to set cnn=nothing. Remember this is only a Copy of the Pointer to the Connection.
The VB Runtime will destroy the real Connection Object if all references are set to nothing (cnADB=nothing and cnn=nothing)
Of curse it is a good idea to "Close" the Connection before setting your last reference to "nothing"


Can't be done? Too bad.
I'll keep this question open a few days more, in case someone knows of a way.
If not, I'll be splitting points. Thanks.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.