467,077 Members | 1,035 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,077 developers. It's quick & easy.

MSSql VB ADODB troubles

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:



Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. Public cnxEventLog As New ADODB.Connection (defined globally)
  4.  
  5.  
  6.  
  7. <code in a procedure follows>
  8.  
  9.  
  10. If (cnxEventLog.State <> adStateOpen) Then
  11. 'Set cnxEventLog = New ADODB.Connection
  12. cnxEventLog.Open strConnection
  13. End If
  14.  


Version 2 of the program is:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public cnxEventLog As ADODB.Connection (defined globally)
  3.  
  4.  
  5.  
  6. <code in a procedure follows>
  7.  
  8.  
  9. 'If (cnxEventLog.State <> adStateOpen) Then
  10. Set cnxEventLog = New ADODB.Connection
  11. cnxEventLog.Open strConnection
  12. 'End If
  13.  

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
Apr 26 '07 #1
  • viewed: 1469
Share:

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Manuel | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.