Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: [ODBC SQL Server Driver]Timeout expired

    Before I am done with this project, I think I will have encountered every SQL Server error known to man. Some I've actually figured out on my own...

    This error occurs in VBA when I call a stored procedure. It does an Insert of one row which should be instantaneous, so I don't want to increase a timeout threshold. That will just make the users wait longer for this same error message.

    Apparently, yesterday it worked, today it doesn't. This whole thing started with an upgrade of Access, which led to an ODBC error - Insert on a linked table failed...so I redesigned that area of the code to run a stored procedure to do the Insert instead (thanks to the advice of people here). Maybe if I had understood the original cause of the problem instead of just trying other ways to accomplish the same end result, I wouldn't still be chasing that elusive fix.

    Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Did you try relinking the tables? When youlink them does it ask you to select a primary key?

  3. #3
    Join Date
    Jul 2006
    Posts
    111
    DCKunkle -
    There is an option on the main screen of this app to relink tables. Unfortunately, there is no history why this was added nor when it's supposed to be executed (a regular user wouldn't know when/why he had to link tables).
    In any event, the code that executes when the button is pressed doesn't seem to fix this particular error:

    Public Function relinkTables()
    On Error GoTo relinkTablesErr

    Dim varReturn As Variant
    Dim strDBDir As String
    Dim strMsg As String
    Dim db As Database
    Dim varFileName As Variant
    Dim tdf As TableDef
    Dim intI As Integer
    Dim intNumTables As Integer
    Dim strProcName As String
    Dim strFilter As String
    Dim lngFlags As Long
    Dim rsConfig As Recordset
    Dim strDataDatabase As String
    Dim cnn As New ADODB.Connection
    Dim strDatabase As String
    Dim strUID As String
    Dim strPassword As String
    Dim strDSN As String
    Dim strConnect As String

    Set db = CurrentDb()
    'Set rsConfig = db.OpenRecordset("Config", dbOpenSnapshot)

    'Rebuild Links. Check for number of tables first.

    intNumTables = db.TableDefs.Count
    varReturn = SysCmd(acSysCmdInitMeter, "Relinking tables", _
    intNumTables)

    ' Loop through all tables.
    ' Reattach those with nonzero-length Connect strings.
    intI = 0

    Set rsConfig = db.OpenRecordset("Config", dbOpenSnapshot)
    strDatabase = rsConfig!DatabaseName
    strUID = rsConfig!UserID
    strPassword = IIf(IsNull(rsConfig!Password), "", rsConfig!Password)
    strDSN = rsConfig!DSN
    rsConfig.Close

    strConnect = "ODBC;DATABASE=" & strDatabase
    strConnect = strConnect & ";UID=" & strUID
    strConnect = strConnect & ";PWD=" & Trim(strPassword)
    strConnect = strConnect & ";DSN=" & strDSN

    For Each tdf In db.TableDefs
    'If connect is blank, its not an Linked table
    'If Len(tdf.Connect) > 0 Then
    If Len(tdf.Connect) > 0 Then
    intI = intI + 1
    tdf.Connect = strConnect
    tdf.RefreshLink
    If Err <> 0 Then
    relinkTables = False
    GoTo relinkTablesDone
    End If
    End If

    varReturn = SysCmd(acSysCmdUpdateMeter, intI + 1)
    Next

    relinkTables = True
    MsgBox "Tables linked successfully."

    relinkTablesDone:
    On Error Resume Next
    varReturn = SysCmd(acSysCmdRemoveMeter)
    On Error GoTo 0
    Exit Function

    relinkTablesErr:
    Select Case Err
    Case Else
    MsgBox "Error#" & Err.Number & ": " & Err.Description, _
    vbOKOnly + vbCritical, strProcName
    End Select
    Resume relinkTablesDone

    End Function

    No message about a primary key...

    Thanks for responding. We're checking into if the timeout occurs before the SQL Server configured timeout, to see where the problem may lie.

    More to come...

  4. #4
    Join Date
    Jul 2006
    Posts
    111
    Never mind...I asked my customer to time how long before the timeout message, she said she couldn't because it's now working for the three people that have the updated Access database.

    Don't you love resolutions like this (not)?

Posting Permissions

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