I am using ADODB code to connect to a SQL Server 2005 database from my VB6 program. The connection is opened in the following :

Code:
    

Public g_SQL_Con As New ADODB.Connection

Public Function OpenSQLConnection() As Boolean
'**************************************************************************
' Purpose   :   Initializes the SQL connection
'
'**************************************************************************

    g_SQL_Con.ConnectionString = _
        "Provider=sqloledb;" & _
        "Data Source=" & g_strSQLServerIP & ";" & _
        "Initial Catalog=catalog;" & _
        "User ID= user;" & _
        "Password=password;" & _
        "Connection Timeout=10"
        
    g_SQL_Con.Open

End Function
When this is completed the g_SQL_Con.State = 1. Throughout the program if I need to execute a command I build the command string and then test g_SQL_Con.State to see if it is 1. Then the command is executed. If not 1 then a call is made to the function OpenSQLConnection.

This all works fine as long as the server is available after the initial g_SQL_Con.Open. If, for some reason, the network is down, the server has stopped, etc., my g_SQL_Con.State doesn't seem to change. It stays at 1. An attempt to execute the command is made, the system hangs for a good 30-40 seconds and an error occurs. I trap the error and execute g_SQL_Con.Close to correct the state. Next time a call is requested it sees the need to open the connection and calls OpenSQLConnection.

My questions are :

  1. If the server isn't available when executing g_SQL_Con.Open the system hangs for 30-40 seconds no matter what I set Connection Timeout to.
  2. If the connection state is incorrectly reported as 1 my command execution hangs for 30-40 seconds no matter what I set Command Timeout to.
  3. Should I be opening and closing the connection each time I need to access the server or continuing to open it and leave it open as long as the program is running?