ODBC Link to SQL Server - test

[ukaquanaut]

Hi Guys

My Access App connects to an SQL Server to collect product data that we print 2D matrix barcodes for. In general that all works really well. However, currently if the ODBC link is down for any reason the App Errors out and crashes. I have put trapping code in now to check the Internet is up, and the SQL Server is available with a ping and lastly I want to just check and trap errors with the OBDC link.

I have a bit of VBA that connect through the ODBC, with a connection string that works from the table it fetches for the product data, but I'd like to perform some action on the SQL Db to trigger any issues and trap them.

I don't really know how to do this but I've cobbled together the VBA below.

Public Function fnTestODBC(TestConnectionString As String) As Boolean

On Error GoTo ODBCTestErrHandler

Dim qdf As DAO.QueryDef
Dim strQuery, strSQL As String

Set qdf = CurrentDb.CreateQueryDef(strQuery)
strSQL = "SELECT TOP 1 NAME FROM sysobjects"
With qdf
.Connect = TestConnectionString
.Execute strSQL
.Close
End With

fnTestODBC = True
Exit Function

ODBCExitProcedure:
Set qdf = Nothing
Exit Function

ODBCTestErrHandler:

Select Case Err.Number
Case 3376, 3010, 7874, 2059, 7873
MsgBox "DEBUG: (2210) " & conConnectivityQry & vbCrLf & Err.DESCRIPTION, vbInformation, "Error"
fnTestODBC = False
Resume Next
Case Else
MsgBox "DEBUG: (2230) " & conConnectivityQry & vbCrLf & Err.DESCRIPTION, vbInformation, "Error"
fnTestODBC = False
GoTo ODBCExitProcedure
End Select

End Function

The problem is the line with .Execute strSQL which does not appear to work I get a data type conversion error. The theory as I understand it making an ODBC connection doesn't actually prove anything, you have to execute some query and this "SELECT TOP 1 NAME FROM sysobjects" was offered by web peeps on other sites, but not being as SQL Server guru, I'm taking it a face value.

I wonder if anyone could point me in the right direction please? Thanks in advance

Regards
Jerry

 

[Minty]

Have a read of this thread - many options given https://www.access-programmers.co.uk/forums/threads/check-if-odbc-linked-table-is-reachable.237710/

 

[cheekybuddha]

You are using a SELECT statement that presumably should return records.

So you must use .OpenRecordset instead of .Execute.

In DAO .Execute is only for action statements (UPDATE, INSERT, DELETE)

' ...
  Set qdf = CurrentDb.CreateQueryDef(strQuery)
  strSQL = "SELECT TOP 1 NAME FROM sysobjects"
  With qdf
    .Connect = TestConnectionString
    .ReturnsRecords = True
    With .OpenRecordset(strSQL)
      fnTestODBC = Not (.BOF And .EOF)
      .Close
    End With
  End With
' ...

hth,

d

 

[ukaquanaut]

Hi All, thanks for your input.

I implemented your suggestion with the With .OpenRecordset(strSQL)

But I'm still getting some sort of data type conversion error 3421

Any thoughts

Many Thanks
Jerry

 

[cheekybuddha]

Try changing the SQL:

  strSQL = "SELECT TOP 1 [NAME] FROM sysobjects"

 

[cheekybuddha]

Do you have linked tables in your db?

I guess so.

If so, then perhaps the last post in the thread linked by Minty would be the answer?

 

[Isaac]

I was wondering

1. do you have SQL Server Management Studio? If so, can you open a new query window on the same database which is the default catalog specified in your TestConnectionString, and execute SELECT TOP 1 [NAME] FROM sysobjects ?
2. if you don't have SSMS, can you create a pass through query with the same connection string and execute SELECT TOP 1 [NAME] FROM sysobjects?

I ask because in my past experience, sometimes I've been given nifty solutions relating to the system tables on SQL server and found out I do not have permissions to execute such queries at the moment, even the schema views some times. You should step back and verify you even have select permissions on this object before troubleshooting too much farther I would think.

 

[cheekybuddha]

if you don't have SSMS, can you create a pass through query with the same connection string and execute SELECT TOP 1 [NAME] FROM sysobjects?

That's what the code above is doing!

 

[Isaac]

That's what the code above is doing!

I thought possibly if they created the query the old fashioned way, and not in code, and tried to run it, the result/error might be totally different (and who knows? perhaps more informative), than the error when a recordset is handling it in code. It was just an idea.
ODBC errors are vague enough - I was just trying to think of any way to re-color the situation and provide more clarity.

 

[cheekybuddha]

I thought possibly if they created the query the old fashioned way, and not in code, and tried to run it, the result/error might be totally different (and who knows? perhaps more informative), than the error when a recordset is handling it in code.

Ah, I see!

 

[ukaquanaut]

I was wondering

1. do you have SQL Server Management Studio? If so, can you open a new query window on the same database which is the default catalog specified in your TestConnectionString, and execute SELECT TOP 1 [NAME] FROM sysobjects ?
2. if you don't have SSMS, can you create a pass through query with the same connection string and execute SELECT TOP 1 [NAME] FROM sysobjects?

I ask because in my past experience, sometimes I've been given nifty solutions relating to the system tables on SQL server and found out I do not have permissions to execute such queries at the moment, even the schema views some times. You should step back and verify you even have select permissions on this object before troubleshooting too much farther I would think.

Hi Issac/David. Thank you for that nudge and info.

I did change the query as suggested by David to strSQL = "SELECT TOP 1 [NAME] FROM sysobjects"
I do have access to the database through SSMS and I ran that query and I get a result " 1 | sysrcols " which is great.

So I now know I need to look at the construction of the request in my VBA as its still failing with a type conversion error 3421

Its a bit annoying as I don't really care what the result of the query is, I simply want to know if the query through the ODBC link succeeds or fails.

But I think its progressing

Regards
Jerry

 

[Isaac]

Interesting. Thanks for the update. Honestly, I've never coded it in the exact sequence you guys are - I have always assigned a value to the .SQL property of a QueryDef and saved it before doing anything else with it, (then used the database.openrecordset to open that saved querydef).......rather than leaving it empty and then referring to the strSQL in the qdf's .OpenRecordset - anyway cheekybudha probably has more experience in this than I. At least you can try it that way if you feel so led.

 

[cheekybuddha]

Do you have linked tables that work (at least some of the time)?

 

[ukaquanaut]

Hi Folks

Thank you all for your help I did manage to get a function working to test the ODBC connection is usable. I have listed it below, I think its a bit clunky but it does appear to return dependable results.

It fetches a value from the SQL Server 'sysobjects' table. which is placed in a table this is tested for a result, if something is there the ODBC is working else it fails, the On Error also picks up any other failures trying to get the values from SQL Server 'sysobjects'.

It creates a local Access table qryODBC which contains the result.

I use a DSN-less ODBC connections and provide all the connection string parameters as follows:

I hope this helps others wanting to check for ODBC links

If fnTestODBC("ODBC;DRIVER={SQL Server};SERVER=" & SQLip & ";UID=" & SQLid & ";PWD=" & SQLpwd & ";DATABASE=" & SQLdb) Then
…...
End If

------------------------------------------------------------------------------------------------------------------------------
Public Function fnTestODBC(TestConnectionString As String) As Boolean
' Tests if an ODBC connection is valid, the connection string data is supplied as parameter and returns true if successful or false if fails

On Error GoTo ODBCTestErrHandler

Dim qdf As DAO.QueryDef
Dim strQuery, strSQL As String
fnTestODBC = False

strQuery = "qryODBC"
strSQL = "SELECT TOP 1 [NAME] FROM sysobjects"

If fnTableExists("qryODBC") Then
' MsgBox "DEBUG: fnODBCCheck1 - qryODBC exists.", vbOKOnly '<-------------------
' if the query qryODBC is there remove it so it can be recreated without error
DoCmd.DeleteObject acQuery, strQuery
Else
' MsgBox "DEBUG: fnODBCCheck2 - qryODBC does not exist.", vbOKOnly '<----------------------
' if the query qryODBC is not there leave it to be recreated. Do nothing.
End If

' Create query qryODBC to check table to prove ODBC is working
Set qdf = CurrentDb.CreateQueryDef(strQuery)

With qdf
.Connect = TestConnectionString
.sql = strSQL
.Close
End With

If Not IsNull(DLookup("NAME", strQuery)) Or DLookup("NAME", strQuery) <> "" Then
fnTestODBC = True
End If

Exit Function

ODBCExitProcedure:
Set qdf = Nothing
Exit Function

ODBCTestErrHandler:

Select Case Err.Number
Case 3376, 3010, 7874, 2059, 7873
If Not DLookup("NetMsgsOff", "TblGeneralSettings", "ID = 1") Then
MsgBox "DEBUG: fnODBCCheck4 " & conConnectivityQry & vbCrLf & Err.DESCRIPTION, vbInformation, "Error"
End If
fnTestODBC = False
Resume Next
Case Else
If Not DLookup("NetMsgsOff", "TblGeneralSettings", "ID = 1") Then
MsgBox "DEBUG: fnODBCCheck5 " & conConnectivityQry & vbCrLf & Err.DESCRIPTION, vbInformation, "Error"
End If
fnTestODBC = False
GoTo ODBCExitProcedure
End Select

End Function