Verify Connection

[yawnzzzz]

Hi,

I have some VBA code that connects to a SQL Database that's on an internal network. This code does other things where they don't need access to the Database. So, when an individual is at home, I'd like to be able to detect in the quickest way possible whether they can reach the Database or not.

Right now, my code catches the error when it tries to open the connection. When it can't find the DB, it returns the error:
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access is denied

If they're on the network or have a VPN connection, it normally takes under a second to connect, but this error takes 11 seconds, so I'd like to find a way to make it error out sooner.

Please Login or Register  to view this content.

[yawnzzzz]

I've been testing to see if the ConnectionTimeout or CommandTimeout properties would help.

Here's where I added it in my code:

Please Login or Register  to view this content.

If I increase the ConnectionTimeout to 60, then it takes 60 seconds. But, if I decrease to less time than the 11 seconds it currently takes before failing, then it still takes 11 seconds.

[yawnzzzz]

Anyone have any suggestions? There's got to be a quicker way to check if a server exists or not, or to cut off the attempt if it goes so long.

[yawnzzzz]

Bump. Any suggestions at all would be appreciated.

[yawnzzzz]

Still no solution to this. Bumping one last time.

[jordan-s]

Possibly try to ping the server if that's not blocked?
That should give you a timely response, from there you can get into trying to establish the database connection.

Please Login or Register  to view this content.

Source - NastyMatt on Microsoft forums