Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ODBC error handling on lost connection

    Just wondering anyone has any thoughts on Error handling DSNless ODBC connections, I've got a number of linked tables to SQL servers in MS-Access 2013, on loss of connection the DB basically locks (into cycling hourglass) instead of handling the error (it can recover but after minutes probably when the linked table becomes available), i'm using the below routine to check for connection prior to querying tables, but it seems I'm still hanging, my assumption at this point is that Access is waiting for something, any ideas or improvements to below?

    Public Function IsODBCConnected(TableName As String) As Boolean
    If Not TableExists(TableName) Then Exit Function

    Dim rst As DAO.Recordset

    On Error Resume Next
    Set rst = CurrentDb.OpenRecordset(TableName)
    IsODBCConnected = (Err.Number <> 3151 and Err.Number <> 3146)
    rst.Close
    End Function

    Any assistance would be much appreciated.

  2. #2
    Star Lounger
    Join Date
    Mar 2010
    Location
    Winston-Salem, NC USA
    Posts
    65
    Thanks
    12
    Thanked 4 Times in 4 Posts
    I believe the standard connection timeout is 30 seconds. You can change that to keep the hang-up to whatever minimum that works for you.
    Do your part!!! Report SPAM to http://www.spamcop.net/

  3. #3
    New Lounger
    Join Date
    May 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Its longer than the standard timeout, so what would you suggest as a optimum, currently set to defaults:

    Time out defaults.png

  4. #4
    Star Lounger
    Join Date
    Mar 2010
    Location
    Winston-Salem, NC USA
    Posts
    65
    Thanks
    12
    Thanked 4 Times in 4 Posts
    That's something that you're going to have to experiment with. Start with a low number, say 5 seconds for example, and see if it hooks up under normal conditions (with the database accessible). If you get a bunch of nuisance timeouts then you're going to have to increase the value until you feel that it's stable.
    Do your part!!! Report SPAM to http://www.spamcop.net/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •