Catching Microsoft Access ODBC connection errors t

Question

I have a little Access application with linked tables to SQL Server that I use at a couple different sites so the SQL server names and sometimes the SQL database and odbc connection names are different from site to site. However they use the Access Runtime to use it so don't have the ability to relink tables without the full version. I'm struggling to find a way to catch any ODBC connection errors and then offering them the option to relink the tables by typing in the ODBC connection name etc. and then relinking through VBA code which I believe is possible. (http://www.access-programmers.co.uk/forums/showthread.php?t=143180 for example)

The database loads immediately on a form with a record source on a linked table so it needs to catch the error on that form if possible. I guess I can add a main menu with a button to relink tables there but would prefer avoiding introducing a menu form which requires an extra click for users.

Have found these articles but I'm unable to catch the initial odbc failure to connect in the form's On Error event and have tried the Activate event as well. Any suggestions would be highly appreciated!

http://support.microsoft.com/kb/209855

http://www.access-programmers.co.uk/forums/showthread.php?t=198039

Answer 1

Extract the connection information from one of the linked tables, and try to open an ADO connection with it. You can adjust the ConnectionTimeout property as needed.

The ODBC connection information for a link is available from the TableDef.Connect property.

CurrentDb.TableDef("link name").Connect

Ideally you can discard "ODBC;" from the start of that string and use the rest for your ADO connection string. But if yours doesn't work that simply, you can Split() the string on ";" and use the pieces you need.

I included a code outline for opening an ADO connection; you would need to refine it. Then you could call it from a splash form. If the connection is successful, open your main form. When the connection fails, open the form where you request the information needed to re-link.

Dim cn As Object
Dim strConnect As String

On Error GoTo ErrorHandler
    strConnect = Mid(CurrentDb.TableDefs("link name").Connect, 6)
    Set cn = CreateObject("ADODB.Connection")
    cn.ConnectionString = strConnect
    cn.ConnectionTimeout = 5 ' in seconds
    cn.Open
    Debug.Print "success"
    cn.Close
    Set cn = Nothing

ExitHere:
    Set cn = Nothing
    On Error GoTo 0
    Exit Sub

ErrorHandler:
    Debug.Print "failed"
    GoTo ExitHere