Excel Query Timeout - Set globally?

[LostInDaJungle]

I have an SQL query that Takes a minute to complete. I have optimized the query as much as possible, but it has several Joins that just eat up the time and the server it's on is old.

I'm trying to import the data into Excel using the standard OBDC connections... No VBA code written as yet. I'm running up against the Excel timeout after 30 seconds and no data returns.

Is there an option to increase the timeout? Is there VBA code to increase the global timeout? I really don't want to have to rewrite the entire thing just to set the timeout to 90 seconds.

 

[Fazza]

Please debug.print [from the VBE (Visual Basic Editor, ALT-F11 from Excel and then CTRL-G for the immediate window)] the connection string and see if it includes the timeout. If so, edit it & set to the new value.

If you're in Excel 2003 and have used a query table, the syntax would be
? activesheet.querytables(1).connection

if that returns something like
"ODBC..........;PageTimeout=5;"

edit the timeout to whatever you want and then set it (can edit what you have. deleted the "? " at the start of the debug print, add "=" to the end of it, delete the line feed so that the edited connection string is after the "=", then enter. the line would be like
activesheet.querytables(1).connection="ODBC.......;pagetimeout=100;"

for newer versions of excel the required object reference is different. that is, not sheet.querytables

hth

 

[LostInDaJungle]

Thanks for the reply. I've been looking at it today and really haven't had massive amounts of time to devote. (Been one of those days...)

I'm on Excel 2010.

When I open the connection properties, I see the DSN there:

DSN=Dynamics GP;UID=********;PWD=********;APP=Microsoft Office 2010;WSID=********;AutoTranslate=No;QuotedId=No;AnsiNPW=No;

I have tried adding the pagetimeout param in the GUI and it just goes *poof* when I hit OK. Maybe that's just MS automagically fixing it for me.

So, I tried doing it this way:

Sub IHateExcel()   
    MsgBox Sheets("Acct").QueryTables.Count
    Sheets("Acct").QueryTables(1).Connection = "ODBC;DSN=Dynamics GP;UID=********;;APP=Microsoft Office 2010;WSID=********;AutoTranslate=No;QuotedId=No;AnsiNPW=No;pagetimeout=1800;"
    Debug.Print "Connection: " & Sheets("Acct").QueryTables(1).Connection
    
    ' This bombs out with a runtime error
    'Dim ret As String
    'ret = Sheets("Acct").QueryTables(1).Refresh()
    
    ActiveWorkbook.RefreshAll
End Sub

It doesn't seem to change the behavior. The function still times out without returning data. (But does prompt me for the password. So it's connecting, right?) Did I miss what you were trying to tell me?

Thanks for your help.

 

[LostInDaJungle]

OOPS! double post - This space intentionally left blank

 

[Fazza]

Did I miss what you were trying to tell me?

Maybe.

The code I posted was specific to Excel 2003 (& earlier); as mentioned before, the object reference for later versions is different. (I now understand you're using Excel 2010.) Maybe Google if you can't find it in Excel VBA help, or the object browser, or via the macro recorder. I think you need to get that right first. 'List object' or something like that ...

What did you debug.print show? (Cause if that works, my earlier comments are off track & things should be easy. Just do like I originally posted.)

I use Excel 2003 exclusively, btw.

regards

 

[LostInDaJungle]

I ended up copying the results to a new table and then just calling that DB.

SELECT * FROM `reports`.`GPacctBalByPeriod`;

Easy enough.