Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel query success

Status
Not open for further replies.

DreamerZ

Programmer
Jul 11, 2001
254
US
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:
Code:
    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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top