Using Both Access/SQL and Test of Connection

I have a program which first looks for a SQL db, if that db is offline, it switches to a local copy in Access located on the PCs harddrive. The problem I'm having is finding a way to test for the actual connection to the SQL db. I am using ADO and attempt to connect to the SQL db by using the .open command on my connection; however, if the SQL db is offline, the error that is returned is being returned from ADO and cannot be trapped from within my VB code.

Does anyone know of a way to do this?

well I dont know why you wouldn't be able to trap the error if you had an error handler. But if it didn't then you might be able to check the errors collection off the connection object. I have a procedure that tests for a good connection and then either continues or aborts the program. Heres how i test it. (in a nutshell)

Dim cn as ADODB.Connection
Dim sConnectionString as string

on error resume next ' inline error handling

sConnectionString = (your connection string)

set cn = new ADODB.Connection
cn.ConnectionString = sConnectionString
cn.Open

if Err.Number <> 0 then
'error trap here
End If




That works for me anyway.

Hope this helps,
John

John Pirkey
MCSD
www.ShallowWaterSystems.com