Excel Query Timeout - Set globally?

LostInDaJungle

New Member
Joined
Feb 27, 2012
Messages
47
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.
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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

New Member
Joined
Feb 27, 2012
Messages
47
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:

Code:
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.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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

New Member
Joined
Feb 27, 2012
Messages
47
I ended up copying the results to a new table and then just calling that DB.

SELECT * FROM `reports`.`GPacctBalByPeriod`;

Easy enough.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,949
Messages
5,599,025
Members
414,274
Latest member
LisaGreen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top