 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
VB Forum / Database Access / January 2007HOWTO: Detrmine Connection Count
If we create a global connection variable called g_oConn, open this connection, then do the following, have we created two distinct connections to the datasource? We're doing this as one possible means of dealing with problems encountered when the user's machine hibernates and the ado connection is closed. Id this works, and does not create an additional connection to the db, then we might want to do the following (which avoids changing a lot of code in our app): Global g_oConn as ADODB.Connection Sub Form_Load g_oConn. Open "XXX..." End Sub 'Now everytime the oConn object is referenced in our code, the following runs... Function oConn() as ADODB.Connection If g_oConn Is Nothing OR g_oConn.Status = adStatusClosed Then g_oConn. Open "XXX..." End If Set oConn = g_oConn End Function Thanks, Dan When you assign an ado connection object variable to another ado connection object variable, do you create a distinct second connection to the related database? I think the answer is no, but would like confirmation, and secondly to know if there are any downsides to making such an assignment. Thanks, Dan > If we create a global connection variable called g_oConn, open this > connection, then do the following, have we created two distinct connections [quoted text clipped - 25 lines] > > Thanks, Dan > When you assign an ado connection object variable to another ado connection > object variable, do you create a distinct second connection to the related [quoted text clipped - 4 lines] > > Dan If I understand you correctly ... Dim Junk As ADODB.Connection Dim Junky As ADODB.Connection Set Junk = New ADODB.Connection ' the one and only object Set Junky = Junk ' two reference variables pointing to the same object then the answer is no. Then will the following work when needing some kind of way to check and see if WinXP is in hibernation, or has otherwise closed the connection? If Not Junk Is Nothing then If Junk.Status = adStatusClosed then 'Reopen connection End If Else 'Reopen connection End If > > When you assign an ado connection object variable to another ado > connection [quoted text clipped - 14 lines] > > then the answer is no. > Then will the following work when needing some kind of way to check and see > if WinXP is in hibernation, or has otherwise closed the connection? [quoted text clipped - 6 lines] > 'Reopen connection > End If <snipped> Ok, I'm with you now. It thought you just wanted to count connections. Here's the bad news. ADO was not designed (like most libraries of the time) were not inherently designed to operate in an disconnected state. (Unlike ADO.Net) You have to write your own framework to manage it. What you have above will likely appear in your solution, but it isn't the whole story. Here are some general 'rules' or guidelines. Open and Close connections immediately before and after you request something. Grab and keep as much data as possible in some intermediate storage - arrays, disconnected recordsets, in the control, etc. ie, get in, grab, and get out. Have one Connection object or at least one 'Connection Manager'. Have it detect hibernation and do whatever it has to do to to wake up again. Confining the code to one spot will make it easier. Never assume any connection is valid across messages, events, or at times even functions. (Error handlers are your friend. <g>) hth -ralph > Have one Connection object or at least one 'Connection Manager'. Have it > detect hibernation and do whatever it has to do to to wake up again. > Confining the code to one spot will make it easier. So, having said that, if the following code summary is my "ConnectionManager", will it do the job as far as knowing that a connection has been lost, whether as a result of someone pulling the ethernet cable, hibernation, or whatever? Maybe another way to say it -- will the Status property always give me the correct result? (I ask because I thought I remembered reading some time ago that this wasn't a foolproof method). If Not Junk Is Nothing then If Junk.Status = adStatusClosed then 'Reopen connection End If Else 'Reopen connection End If Thanks, Dan > > Have one Connection object or at least one 'Connection Manager'. Have it > > detect hibernation and do whatever it has to do to to wake up again. [quoted text clipped - 18 lines] > > Dan Answered in order... No, no, no, or no. Or another way to put it, no. <g> You can search in this group for answers to each of your questions. In general don't expect you can just continue on as though nothing has happened. Best to assume you have to rebuild, test what is missing and restore if necessary. 1) Th Just to be boorish let me amplify on this a bit. There is often a bit of confusion over the word 'Connection' as it is used in two similar but distinct contexts. There is a 'physical' (for lack of a better term) connection - the point to point connection between the provider and the database engine. And there are ADO Connection objects which set up these connections. ADO uses 'connection pooling' which means you can actually have more than one physical connection for any one Connection Object or you can have only one connection at a time for several Connection Objects. That's how two reference variables pointing to the same object can give the appearance of the same or new connection. You have limited control over physical connections - you can create and destroy ADO Connection objects and you can signal the provider to 'release' or 'fetch next' (.Close/.Open) from the pool. But Physical connections and the Pool are purely a Provider to Engine thingy. Even if you tell ADO to not use a pool, you may get more than one connection - depends on the scenario (query), the provider, the engine (connection manager). You can keep track of the number of ADO Opens and Closes in your program, but tracking actual physical connections can only be done accurately by an outside utility and results are often unexpected. <g> hth -ralph |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.