MSSql VB ADODB troubles

[dmckenna]

I've been tasked to upgrade an old system and there's many different versions of VB code that uses MDAC to talk to MSSql. Do you know what the difference is between the two code versions? Is there problems with re-opening connections all the time?



Version 1 of the program is:

 
 
Public cnxEventLog As New ADODB.Connection (defined globally)
 
 
 
<code in a procedure follows>
 
 
If (cnxEventLog.State <> adStateOpen) Then
'Set cnxEventLog = New ADODB.Connection
cnxEventLog.Open strConnection
End If
 

Version 2 of the program is:

 
Public cnxEventLog As ADODB.Connection (defined globally)
 
 
 
<code in a procedure follows>
 
 
'If (cnxEventLog.State <> adStateOpen) Then
Set cnxEventLog = New ADODB.Connection
cnxEventLog.Open strConnection
'End If
 

I appreciate that version 1 checks the connection state before trying to reconnect but it doesn't seem to do all of the connection logic, whereas version 2 always reconnects.



Version 1 is currently running on a machine with VBA. The system runs fine all day calling the connection procedure as shown and then doing an insert, update or query.



Approximately once a day it tries to do 2 database calls (queries, inserts, etc.) and each db call runs the connection procedure first. Once a day, I get the error "ADOB.Command Operation is not allowed on a object referencing a closed or invalid connection". Unfortunately I cannot determine 100% whether that is the only error since my error display logic overwrites with the most recent error, there may or may not be a connection error thrown which is then overwritten with the ADOB.command.



I personally observed this problem and after trying to do inserts, etc. to the database they all failed. I left the system to walk away to get my laptop and when I returned everything worked fine. No changes or actions were taken on the system while I was away.



There are many other times a day (approximately 50) when they do these 2 database calls at the same time and everything works with no problems.



I would really appreciate any help you can provide,



Thanks,



Dave