detect timeout in connect to db for error

Topic

New Reply

I have a routine where i try to connect to SQL Server. if the connection times out, i get a SQL Server connection error and an additional dialog box prompting the user to try and login. I’d like to suppress this default behavior, but I don’t know what kind of error code to look for. It’d be nice NOT to have access display the userID and password (even if masked) on the ‘try to login again’ dialog box.

TIA

Reply | Quote

Replies

If you are using ODBC, it will generate one of the dozen or so errors it throws, but there seems to be considerable inconsistency in what you get for a given situation. It appears that most SQL Server errors are in the 30000 range so if you are using ADO and connect strings, you may have better luck. In any event FMS publishes a list of Access error codes at http://www.fmsinc.com/MicrosoftAccess/Errors/ErrorNumber_Description.html. It will probably require some experimenting to determine what error codes you get in the timeout case in any event.

Reply | Quote

thanks – i tried to do on error goto errorHandler and in errorHander: i did this:

MsgBox “Error: (” & Err.Number & “) ” & Err.Description, vbCritical

this is what happens: the connect fails, and i get the SQL Server timeout reply and the login dialog box (neither of which i’d like to see). the error itself from Access is

Error: (3059) Operation canceled by user.

Now, what appears to be happening is the ODBC call goes “outside” of Access and returns the error notification and login screen. Which makes some sense, as the error is raised in the ODBC call, not Access.

BTW, this is the connectstring:

“ODBC;Driver=SQL SERVER;SERVER=;UID=NFUR_user;PWD=;Database=NFUR;”

No need to implement DSN files – this code does the connection. I was hoping there’d be an option to suppress the login bit but I haven’t found anything along those lines.

Reply | Quote

As long as you are running within Access, I don’t know of anything you can do to control ODBC errors, and as you surmised those are being raised by ODBC, which passes back a more or less unusable error message. There are very technical documents available in TechNet about what you need to do to program ODBC, but it’s far beyond what I would want to dig into. You could try enabling an ODBC trace, but I’m not sure what help that would be.

Reply | Quote

I just finished reading an article from MS that states:

Applications cannot, however, ignore a return of SQL_SUCCESS_WITH_INFO return code on the SQLConnect, SQLDriverConnect, or SQLBrowseConnect. This is because messages other than 5701 and 5703 that do require action may be returned.

in the case of a timeout, the SQL Server error code is 01000, which cannot be suppressed. I read this to mean “since you succeeded in connecting, we kindly provide you with the option to login”. which is what I don’t want, but looks like I’ll have to live with it.

Previously, I got clever and built in a ping component in VBA to test the connection and that worked – meaning I could message out before actually connecting, but unfortunately the network I am on has several nodes that don’t allow ping, so most people couldn’t use the system. Those that couldn’t ping can, however, connect.

Well, thanks for trying!

Reply | Quote