[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Need help with ADODB connection. Copying its value.

Posted on 2006-03-20
13
Medium Priority
?
270 Views
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.
0
Comment
Question by:spoowiz
13 Comments
 

Author Comment

by:spoowiz
ID: 16243116
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
 
LVL 2

Expert Comment

by:superg65
ID: 16244046
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
 

Author Comment

by:spoowiz
ID: 16244080
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:superg65
ID: 16244107
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
 

Author Comment

by:spoowiz
ID: 16244161
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
 
LVL 2

Expert Comment

by:superg65
ID: 16244261
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
 
LVL 2

Expert Comment

by:superg65
ID: 16244296
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
 

Author Comment

by:spoowiz
ID: 16244351
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
 
LVL 2

Assisted Solution

by:superg65
superg65 earned 200 total points
ID: 16244397
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
 
LVL 6

Assisted Solution

by:cubixSoftware
cubixSoftware earned 200 total points
ID: 16246147
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
 

Author Comment

by:spoowiz
ID: 16248056
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
 
LVL 2

Accepted Solution

by:
KnobiKnobi earned 600 total points
ID: 16249493
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
 

Author Comment

by:spoowiz
ID: 16253856
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question