Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Apr 2006
    Location
    California
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trapping Conection Timeout (Access 2003 SP2)

    I'm attempting to add tables to a number of databases across a network. Everything works great, unless I happen to have a database located on a server which is down.

    I thought the following code would allow me to trap such a situation. However, when pDB_Loc (the full path and database name) points to a server which is down, the code just hangs. What am I missing or doing wrong?

    Set cnn1 = New ADODB.Connection
    cnn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & pDB_Loc & ";"
    cnn1.ConnectionTimeout = 10
    cnn1.Open
    If cnn1.State = adStateOpen Then
    AddTablesToDBs = CreateTableDefX(dbMod, prstERR, pDB_Loc)
    Else
    AddTablesToDBs = False
    End If

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trapping Conection Timeout (Access 2003 SP2)

    Try handling the error like this:

    ...
    On Error Resume Next
    cnn1.Open
    On Error GoTo 0
    If cnn1 Is Nothing Then
    AddTablesToDBs = False
    Else
    AddTablesToDBs = CreateTableDefX(dbMod, prstERR, pDB_Loc)
    End If

    If that doesn't work, we'll have to think of a way to check whether the server is available before trying to open the connection.

  3. #3
    Star Lounger
    Join Date
    Apr 2006
    Location
    California
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trapping Conection Timeout (Access 2003 SP2)

    Hans,

    Gave it a try. Still get Not Responding after a long delay on cnn1.open.

    Marty

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trapping Conection Timeout (Access 2003 SP2)

    I'm not sure it's possible to avoid the delay - Windows will search the network if you specify a path it can't resolve immediately - but you can try the following to trap the error:

    Dim strFile As String
    On Error Resume Next
    strFile = Dir(pDB_Loc)
    If Err Then
    ' server/path not available
    AddTablesToDBs = False
    Else
    ' open connection
    On Error GoTo 0 ' or to an error handler label
    cnn1.Open
    ...
    End If

  5. #5
    Star Lounger
    Join Date
    Apr 2006
    Location
    California
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trapping Conection Timeout (Access 2003 SP2)

    Hans,

    That didn't trap the the problem either. Looks like I'll need to manually check each server and make sure its up before running my updates.

Posting Permissions

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