MS Access VBA: Using workspace.OpenDatabase to connect to an unavailable SQL server via an ODBC connection - elegant recovery?

Question

In an application that uses a MS Access form as a front-end to some SQL databases, I use DBEngine.CreateWorkspace to get a workspace, then workspace.OpenDatabase to connect to my remote SQL server via a defined ODBC System DSN. This all works quite nicely, until someone disconnects the remote SQL machine from the network, or shuts it down, or something else similarly ridiculous. (Note: I know there's a lot ridiculous about this setup, but unfortunately it's an inevitability at this point)

My question is: Is there a way to elegantly deal with the timeout and subsequent 'SQL Server does not exist or access denied' error messages that come up, within the VBA code? workspace.OpenDatabase throws an error that I can catch and deal with, but not before two popups come up and stop my VBA code until an operator clicks OK.

DoCmd.SetWarnings False doesn't affect it as the error popups are not actually coming from Access itself - I think they're from the underlying ODBC process or the Jet Engine that drives it.

Any ideas?

Answer 1

I eventually found something that works by searching 'Suppress ODBC connection failure warnings'.

Courtesy of Trevor Best from http://bytes.com/topic/access/answers/201502-how-suppress-odbc-connection-dialog

Some code that uses ADO to make the database connection in a way that allows VBA error trapping to catch the error before the system throws any popups at you.

Function CanOpenSQLDbLB(pstrServer As String, pstrDb As String, pstrUser
As String, pstrPassword As String, Optional pfReportError As Boolean =
True) As Boolean

On Error GoTo CanOpenSQLDbLB_Err

Dim objConn As Object
Dim strConn As String
Dim strError As String, lngErr As Long
Const cstrSQLErr = "[Microsoft][ODBC SQL Server Driver][SQL Server]"

Set objConn = CreateObject("ADODB.Connection")

strConn = strConn & "DRIVER=SQL Server"
strConn = strConn & ";SERVER=" & pstrServer
strConn = strConn & ";APP=" & Application.Name
strConn = strConn & ";WSID=AWorkstation"
strConn = strConn & ";DATABASE=" & pstrDb

objConn.Open strConn, pstrUser, pstrPassword

CanOpenSQLDbLB = True

CanOpenSQLDbLB_Exit:
On Error Resume Next
objConn.Close
Set objConn = Nothing
Exit Function
CanOpenSQLDbLB_Err:
lngErr = Err.Number
strError = Err.Description

If InStr(1, strError, cstrSQLErr) Then
strError = "Error reported by server" & vbCr & vbCr &
Replace(strError, cstrSQLErr, "")
End If

Select Case lngErr
Case Else
If pfReportError Then
MsgBox strError, 16, "Error #" & Err & " Attempting to
open server database"
End If
End Select
Resume CanOpenSQLDbLB_Exit

End Function

Answer 2

A good solution can be found here:

ACC2000: How to Trap ODBC Logon Error Messages http://support.microsoft.com/kb/210319

The above is from Access 2000 and is 14 years old, but as such it still works fine today. The other possible advantage is you don’t have to adopt and introduce ADO into your application. For applications that already use or have ADO, then no big deal, but if your application sticks to one data object model, then you not have to potentially introduce ADO.

The other BIG bonus of the above is this effectively logs you into the database and thus you avoid having user name and passwords in the linked tables. This means you can have different users and logons, and NOT have to re-link or embed the user name or password in your linked tables.

This wonderful trick and result of the above connection trick is outlined here:

Power Tip: Improve the security of database connections

http://blogs.office.com/b/microsoft-access/archive/2011/04/08/power-tip-improve-the-security-of-database-connections.aspx