November 1, 2002 at 8:55 am
I am using the code below in Access 97 to check whether the user id is valid before refreshing all the linked tables.
This works fine except that I would like to make two enhancements:
1. Determine whether failure due to wrong user id or because the Server / network is down
2. Get a response when the server is down within 5 seconds rather than current 25 seconds.
Function fCheckServerVersion2() As Boolean
Dim mydb As Database
Dim myq As QueryDef
Dim strConnect As String
Set mydb = CurrentDb()
Set myq = mydb.CreateQueryDef("")
strConnect = "ODBC;DRIVER={SQL Server}" _
& ";SERVER=" & constrServer _
& ";DATABASE=" & constrDatabase _
& ";Trusted_Connection=True"
Set myq = mydb.CreateQueryDef("")
myq.Connect = strConnect
myq.ReturnsRecords = False
myq.SQL = "select AdditionalID from additionals where AdditionalID = ' ';"
myq.Execute
fCheckServerVersion2 = True
ExitRoutine:
Set myq = Nothing
Set mydb = Nothing
Exit Function
Error_Trap:
fCheckServerVersion2 = False
GoTo ExitRoutine
End Function
November 1, 2002 at 11:21 am
You will get an error off of
myq.Connect = strConnect
You will ned to look up the return codes for this method to find out what they mean.
November 2, 2002 at 6:44 am
The best way I know is to use sqldmo. this will tell you if the server has sql server, whether it is started, starting, stopping or paused.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 4, 2002 at 4:09 am
Simon, Access 97 does not recognise the command 'sqldmo'. Is this a program I have to download?
Antares686, I don't get when I assign myq.connect = strconnect?
The error occurs when the line myq.Execute runs.
My error trapping picks this up but the message is a generic ODBC error message.
Also any advice on the other part of my question, reducing the timeout?
November 4, 2002 at 5:17 am
SQLDMO is a set of client library (com objects) that can be obtained from the SQL client install (I believe they are redistributable). SQLDMO does offer a great solution.
Now as for your connection, sorry I wasn't thinking DAO. You might want convert to ADO instead as you can check the state of a connection before continuing. I believe the best you can do in DAO is like so
ON ERROR GOTO Error_Trap
myq.Execute
ON ERROR GOTO 0 'Always turn off error traping when you complete a section you know may fail so you don't skip those you don't and create troubleshooting issues.
Then in your Error_Trap section add a piece to look at the err collection which contains description and error number to find reason.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply