Excel query success

[DreamerZ]

I have had a database connection running for the last few years and everything has seemed to go smoothly. Recently, however, the users have noticed that for some days the query locks up and isn't run. The only change has been the database the query is connecting to. Essentially, the set schedule runs the query and if the database isn't available, the query just sits there and times out after 8 hours (normal timeout duration).

So, here's what I want to do: How do I continually check for a successful connection and if not successful after a certain period of time, have the query start over and try again. Obviously, then after an extended period of time, stop and error?

The query is below:

    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=\\enclr1\bowe\d-mist\MisDB\data\mis.mdb;DefaultDir=\\enclr1\bowe\d-mist\MisDB\data;DriverId=25;FIL=MS Access;MaxBufferSize" _
        ), Array("=2048;PageTimeout=5;")), Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT BST_PAC_Job.FK_SHIFT, BST_PAC_Job.Accounts, BST_PAC_Job.AccountsOK, BST_PAC_Job.FK_JOB_TYP, BST_PAC_Job.FK_SYSTEM, BST_PAC_Job.shiftdate" & _
        Chr(13) & "" & Chr(10) & _
        "FROM `\\enclr1\bowe\d-mist\MisDB\data\mis`.BST_PAC_Job BST_PAC_Job" _
        , _
        "" & Chr(13) & "" & Chr(10) & jobDate & _
        Chr(13) & "" & Chr(10) & "ORDER BY BST_PAC_Job.FK_SHIFT")
        .Name = "Query from MS Access 97 Database"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With

With the above setup, where can I check for a successful connect? I'm not sure where the code is stopping, but I am assuming it's at the .Add.

Thoughts?

DreamerZ


DreamerZ

 

[DreamerZ]

Bump*

No one has any suggestions?



DreamerZ