Test ODBC connection

accessNator

Registered User.
Local time
Today, 14:53
Joined
Oct 17, 2008
Messages
132
I was wondering if its possible to test my ODBC connection using my DSNLess connection to my remote database without creating a query or table link?

Is there a sample VBA code I can look at?

Thanks.
 
Here is some sample VBA code, using ADO, that will do what you want:
Code:
[COLOR="Navy"]Public Sub[/COLOR] ConnectionTest()

    [COLOR="navy"]Dim[/COLOR] cn [COLOR="navy"]As[/COLOR] ADODB.Connection
    [COLOR="navy"]Dim[/COLOR] vError [COLOR="navy"]As Variant
    Dim[/COLOR] sErrors [COLOR="navy"]As String

    Set[/COLOR] cn = [COLOR="navy"]New[/COLOR] ADODB.Connection

    [COLOR="navy"]On Error Resume Next[/COLOR]
    cn.Open "Provider=sqloledb;" _
        & "Data Source=MySqlServerName;" _
        & "Initial Catalog=MyDatabase;" _
        & "User ID=MyUserID;" _
        & "Password=MyPassword;"
    [COLOR="navy"]On Error GoTo 0

    If[/COLOR] cn.State = adStateOpen [COLOR="navy"]Then[/COLOR]
        cn.Close
        MsgBox "Connection Succeeded", vbInformation
    [COLOR="navy"]Else
        For Each[/COLOR] vError [COLOR="navy"]In[/COLOR] cn.Errors
            sErrors = sErrors & vError.Description & vbNewLine
        [COLOR="navy"]Next[/COLOR] vError
        [COLOR="navy"]If[/COLOR] sErrors > "" [COLOR="navy"]Then[/COLOR]
            MsgBox sErrors, vbExclamation
        [COLOR="Navy"]Else[/COLOR]
            MsgBox "Connection Failed", vbExclamation
        [COLOR="navy"]End If
    End If

    Set[/COLOR] cn = [COLOR="navy"]Nothing

End Sub[/COLOR]
 
Here is some sample VBA code, using ADO, that will do what you want:
Code:
[COLOR=Navy]Public Sub[/COLOR] ConnectionTest()

    [COLOR=navy]Dim[/COLOR] cn [COLOR=navy]As[/COLOR] ADODB.Connection
    [COLOR=navy]Dim[/COLOR] vError [COLOR=navy]As Variant
    Dim[/COLOR] sErrors [COLOR=navy]As String

    Set[/COLOR] cn = [COLOR=navy]New[/COLOR] ADODB.Connection

    [COLOR=navy]On Error Resume Next[/COLOR]
    cn.Open "Provider=sqloledb;" _
        & "Data Source=MySqlServerName;" _
        & "Initial Catalog=MyDatabase;" _
        & "User ID=MyUserID;" _
        & "Password=MyPassword;"
    [COLOR=navy]On Error GoTo 0

    If[/COLOR] cn.State = adStateOpen [COLOR=navy]Then[/COLOR]
        cn.Close
        MsgBox "Connection Succeeded", vbInformation
    [COLOR=navy]Else
        For Each[/COLOR] vError [COLOR=navy]In[/COLOR] cn.Errors
            sErrors = sErrors & vError.Description & vbNewLine
        [COLOR=navy]Next[/COLOR] vError
        [COLOR=navy]If[/COLOR] sErrors > "" [COLOR=navy]Then[/COLOR]
            MsgBox sErrors, vbExclamation
        [COLOR=Navy]Else[/COLOR]
            MsgBox "Connection Failed", vbExclamation
        [COLOR=navy]End If
    End If

    Set[/COLOR] cn = [COLOR=navy]Nothing

End Sub[/COLOR]

Thanks for your example, I appreciate it, I tried modifying it using Late Binding for the ADODB connection because I have different versions of Access Users but was having problems when it attempted to check the cn.state = adStateOPen line for some reason, it would not go into this block of code.

From what I read after much searching was a link at: http://www.ureader.com/msg/103761453.aspx

Near the bottom was a response from Bob Phillips which he stated:
You can't use the library constants if you use late binding, so replace adStateOpen with the value 1
So my revised code looks like this using late binding.

Code:
Function testODBC()

  Dim cn As Object

 Set cn = CreateObject("ADODB.Connection")

cn.Open YourConnectionString
    If cn.state = 1 Then
        MsgBox "Connection Succeeded", vbInformation
    Else
        For Each vError In cn.Errors
            sErrors = sErrors & vError.Description & vbNewLine
        Next vError
        If sErrors > "" Then
            MsgBox sErrors, vbExclamation
        Else
            MsgBox "Connection Failed", vbExclamation
        End If
    End If
  
    cn.Close

End Function
 
After further testing, I a running into a problem and dont quite understand what is going on...please forgive my ignorance.

I wanted to create a test connection to my remote SQL server hence a function to test my connection. This was done so before a user can use a form which calls to create a Pass Through Query to my remote SQL server that if a connection does not exist, they cannot proceed. But here is what I am running into.

I am running Access 2010. If the user is currently using the Access application and for some reason I decide to restart the SQL server, the connection test is successful, but when the USER proceeds to create Pass Through Query on a form action, they get a message box that states "ODBC -- Call failed. Now the only way around this is for the USER to restart Access 2010. Then the User test connection is successful and USER can create a pass through query.

What do I need to do to prevent the USER having to restart the ACCESS application in case I have to restart the SQL server? I hope this made sense.
 
What do I need to do to prevent the USER having to restart the ACCESS application in case I have to restart the SQL server? I hope this made sense.

If you are using ADO type objects, then I would think you need to destroy / remake your ADODB.Connection object.

In my app, I create one of those, passing it the DSN'less settings. (So, issue the .Open event passing in the connection string). I leave that object in existance the entire time the FE is up.

In each class method which does DB interaction, I create / cleanup ADODB.Recordset and ADODB.Command objects. When I create them, I pass the connection object I have floating around globally to the .ActiveConnection and that takes care of hooking the ADO object to the BE connection.

I have never tested the server going down. I do not have IPL control over the server. I assume the user would receive possibly many error boxes as they try to get out of the app if the server suddenly went away. "Tough. Nothing in the FE is persistent. If the server goes away, then the server support staff better fix that situation."
 
If you are using ADO type objects, then I would think you need to destroy / remake your ADODB.Connection object. I think I am but again, Im new to this type of code.

Here is my code for my pass through query. As you can see I am using Late Binding. I am not sure how I destroy the ADODB.Connection.


Code:
Function CreateSPT(SPTQueryName As String, strSQL As String, passStatus As Integer)

  Dim cat As Object
  Dim cmd As Object

  On Error GoTo ErrorHandler

  Set cat = CreateObject("ADOX.Catalog")
  Set cmd = CreateObject("ADODB.Command")

  cat.ActiveConnection = CurrentProject.Connection

  Set cmd.ActiveConnection = cat.ActiveConnection

  cmd.CommandText = strSQL
  cmd.Properties("Jet OLEDB:ODBC Pass-Through Statement") = True
  cmd.Properties _
     ("Jet OLEDB:Pass Through Query Connect String") = SetConnectionString(passStatus)
  cat.Procedures.Append SPTQueryName, cmd

  Set cat = Nothing
  Set cmd = Nothing

  Exit Function

ErrorHandler:
      ' If Query already exists, delete it.
      cat.Procedures.Delete SPTQueryName
      Resume
End Function
 
Just issue that, then recreate the object.

Okay, I did have that already and thought thats what it did. But I am not sure what you mean to recreate it. What happens is that when i execute the pass through query one time, Ill go to my test SQL remote database and look to see if there is an active connection and it lists that 1 is still active and I am 100% sure this function is what creates it but doesnt close it or destroys it for some reason.
 

Users who are viewing this thread

Back
Top Bottom