Catching ODBC connection failure error. (1 Viewer)

[shwan]

Hello everyone,

I want to address an issue I'm facing with ODBS connect failure error. I am using Access as a front-end application connected to SQL Server as the backend, and I have two machines, one at work and the other at home. Both machines have different names. When I load my application, I need to detect if the server name stored in the application is missing or incorrect. If it is, I want to display a special form that allows me, the user, to enter the new server name. The re-linking form and other logic are already in place and working correctly. However, my main problem is that it takes a very long time for the connection failure message to appear, the application actually freezes for a while, and I'm not able to catch that error to handle it. I have attached a photo of the error message, as you see does not include an error number.

I appreciate any advice or suggestions you may have on how to quickly catch this error and improve the user experience. Thank you for your help.

 

[Josef P.]

I use an ADODB.Connection for this, because you can set the timeout.

with new ADODB.Connection
      .ConnectionString = OleDbConnectionstring
      .ConnectionTimeout = ...
...

Example: https://github.com/AccessCodeLib/Db...source/codelib/data/DbConnectionInfo.cls#L628

 

[Ranman256]

i cycle thru a list of tables to get their record count.
if any error out, then its a broken connection.

on error goto errTbl

while not rst.eof
   vTbl = .fields("TblName").value
   iCt = 0
   iCt = Dcount("*",vTbl)
   if iCt = 0 then msgbox "Err " & vTbl
wend
exit sub

ErrTbl: 
msgbox "Err " & vTbl
end sub