Test SQL Connection before running (1 Viewer)

[ejstefl]

Hello all,

I have a database that has a SQL connection to some tables stored on a SQL server. The database is updated at a certain frequency with information from these tables. Basically, several update/append queries are run in a certain order. The problem comes in when the SQL tables are unavailable. Certain queries will run, and certain ones (ones depenent upon the SQL connection) will fail. This is not a desirable scenario.

I would like to be able to "test" the SQL connection each time beofore executing the various update/append queires. Can someone please provide me some syntax or a method to do this? I'm not even sure where to start, so any help would be apprciated.

Thanks,
Eric

 

[Dugantrain]

You should be able to use Error-Handling while opening your Recordset to test for error:

sConn = "Provider=SQLOLEDB.1;Password=Somepw;Persist Security Info=True;User ID=Someuid;Initial Catalog=someDB;Data Source=SomeServer"
sSQL =      "SELECT * FROM SomeTable"

on error goto errConnect
rs.Open sSQL,sConn
Exit Function

errConnect:
     msgbox "The database is currently unavailable.  Please try again later."
End Function

 

[ejstefl]

Thanks!! That looks like it will be perfect. I'll try it out tomorrow.

 

[ejstefl]

OK, since I had already linked the tables to my databse, this is the code I used:

Dim db As DAO.Database
Dim rs As DAO.Recordset

On Error GoTo errConnect

Set db = CurrentDb
Set rs = db.OpenRecordset("dbo_ASSET", dbOpenDynaset)

Exit_Sub_Routine:
    Exit Sub
    
errConnect:
    MsgBox "Unavailable!"

It works - except that I get a warning box saying it can't connect, THEN I get my message box saying Unavailable. Is there a way to supress this first error message?