Excel Querytable Refresh only works once

4
votes

I am adding a ListObject to an Excel 2007 Workbook using VBA. The ListObject is to have a QueryTable behind it, linking to an Access database. The code looks like this: 

Dim l As ListObject
Dim c As ADODB.Connection
Dim r As ADODB.Recordset

Set c = New ADODB.Connection
c.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=myAccessDatabasePath;Persist Security Info=False;"

Set r = New ADODB.Recordset
r.Open "Select * From myTable", c

Set l = ActiveSheet.ListObjects.Add(xlSrcQuery, r, True, xlYes, Range("A1"))
l.QueryTable.Refresh False

'this line causes an error
l.QueryTable.Refresh False

Essentially the problem is that I cannot refresh the table more than once. The Refresh button on both the Data and the Table Design ribbons is greyed out. I have tried similar code without using Listobjects (i.e. just QueryTables) and get the same problem. I have tried refreshing the underlying connection object and again, get the same problem.

I've spent all morning Googling to no avail.

Is this a bug, designed behaviour or (most likely) am I doing something stupid?

Many thanks in advance,

Steve

2
excelvbaoledb
Because the First refresh hasn't finished yet. – user2140261
Thanks, but I tried that. Even if I removed both refreshes and refreshed via Excel instead, I can see the data arriving on the worksheet (only a dozen rows or so). However, the option to refresh again is greyed out. Even if I close and reopen it, the option is still greyed out. I think something is happening to the connection object - but I can't work out what. – steveo40
Besides which, the 'false' parameter after the refresh method is supposed to prevent the thing from refreshing in the background (I think). So if my understanding is correct the code ought to wait until it had finished anyway before starting the next row. Thanks though. – steveo40
The background refresh is sometimes overridden if any table in the workbook has the Connection Property of to not Enable Background Refresh. But with the new data my solution is not for your specific issue. Just a thought have you tried changing the Persist Security Info=False to True so that it will retain the security information – user2140261
I've worked a lot with external data and I never knew you could use an ADODB recordset as the Source. But I can confirm that it works, then grays out the refresh button (Win7, XL2010). If you record a macro it doesn't use ADODB, but uses a SourceType of xlSrcExternal and passes in the connection string. I wonder if you did it that way instead of ADODB if that would free up the refresh (and we'd both learn something). – Dick Kusleika

2 Answers

4
votes

Ok, I got it to work. The macro recorder (thanks for the suggestion Dick) was actually useful for once.

Dim s As Worksheet
Dim l As ListObject

Set s = ActiveSheet
Set l = s.ListObjects.Add(xlSrcExternal, "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=myDatabasePath;", True, xlYes, Range("A1"))

l.QueryTable.CommandType = xlCmdTable
l.QueryTable.CommandText = "mytable"

l.QueryTable.Refresh False

'this now works!
l.QueryTable.Refresh False
1
votes

This is UNTESTED but it still should work, it will check if the table is already in a refresh and if it is, it will wait 1 second and check again until it is no longer refreshing then it will continue

Dim l As ListObject
Dim c As ADODB.Connection
Dim r As ADODB.Recordset

Set c = New ADODB.Connection
c.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=myAccessDatabasePath;Persist Security Info=False;"

Set r = New ADODB.Recordset
r.Open "Select * From myTable", c

Set l = ActiveSheet.ListObjects.Add(xlSrcQuery, r, True, xlYes, Range("A1"))
    With l
        .QueryTable.Refresh False

             Do while .Refreshing 
                 Application.Wait Now + TimeValue("00:00:01")
             Loop

         'this line causes an error
        .QueryTable.Refresh False

End With