Need help with ADODB connection. Copying its value.

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.
spoowizAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

spoowizAuthor Commented:
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?
0
superg65Commented:
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?

0
spoowizAuthor Commented:
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"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

superg65Commented:
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

;)
0
spoowizAuthor Commented:
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?
thanks
0
superg65Commented:
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
0
superg65Commented:
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= "

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

      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.

;)



0
spoowizAuthor Commented:
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.
thanks
0
superg65Commented:
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
:)
0
cubixSoftwareCommented:
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


0
spoowizAuthor Commented:
I found that the easiest way to "get it to work" is:
   set cnn = cnADB
or
   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.
0
KnobiKnobiCommented:
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"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
spoowizAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.