Here's my newbie questions o' the day:

I'm using ADO to connect to a SQL database. Here's my
connection string:

cn.Open "Driver={SQL
Server};Server=MyIPAddress;Database=DatabaseName;U ID=MyID;
PWD=MyPassword"

There may be times when the database is not available.
Currently it just gives an automation error. Is there a
way I can use code to first test for the availability of
the database?

tod

Hi

Use a small function for this. Rewrite the SQL in it to do a search for a
single indexed number or a primary key from a small table so it executes
fast:

Function DatabaseOK() As Boolean
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
On Error GoTo TheEnd
cn.Open "Driver={SQLServer};Server=MyIPAddress;" & _
"Database=DatabaseName;UID=MyID;PWD=MyPassword "
rst.Open _
"SELECT ID FROM Customers WHERE ID=1", _
cn, adOpenForwardOnly, adLockReadOnly
DoEvents
rst.Close
DatabaseOK = True
Exit Function
TheEnd:
DatabaseOK = False
On Error Resume Next
rst.Close
cn.Close
End Function

And in your main code:

If DatabaseOK = False then
Msgbox "System is down. Miller time!"
Exit sub
end if

HTH. Best wishes Harald

"Tod" skrev i melding
...
Here's my newbie questions o' the day:

I'm using ADO to connect to a SQL database. Here's my
connection string:

cn.Open "Driver={SQL
Server};Server=MyIPAddress;Database=DatabaseName;U ID=MyID;
PWD=MyPassword"

There may be times when the database is not available.
Currently it just gives an automation error. Is there a
way I can use code to first test for the availability of
the database?

tod