Excel VBA - Access to Excel Pivot Table - Speed Up Query

[Harry Flashman]

I have a pivot table which is linked to a query in Access. The trouble is it can be very slow updating.

I update this pivot table via VBA. But I think originally I just manually made the connection, while using the macro recorder. And the code I use to refresh the data is a stripped down version of that recorded code.

Sub SQLQueryWithVBA2()
    
    MySource = "C:\MyDataBase.accdb"
     
    MyCommand = "SELECT * FROM D_Current_Extracts"
    
    
    With ActiveWorkbook.Connections("DCurrentExtracts").OLEDBConnection ' this is the name of the Pivot Table source data connection
        .CommandText = MyCommand
        .CommandType = xlCmdSql
        .Connection = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MySource
    End With
    
    ActiveWorkbook.Connections("DCurrentExtracts").Refresh
End Sub

I have previously written code to fetch data from Access to an Excel worksheet using an ADODB.Connection, and I found by changing certain parameters of the ADODB.Recordset I was able to dramatically speed up the amount of time it took to fetch the data, specifically by changing the CursorType to adOpenForwardOnly and the LockType to adLockReadOnly.

I was hoping there might be some way of changing my code above in a similar way but the recorded code makes no mention of an ADODB.Connection or an ADODB.Recordset and I have thus I don't know how to change the CursorType or LockType.

Can someone give me a few pointers please?

 

[Harry Flashman]

I have taken a stab at writing some code. I believe what I have written will fetch the data from Access.

What do I need to add to the code the put this fetched data into an existing pivot table?

Sub GetDataFromAccessIntoPivotTable()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strDataSource As String, strQuery As String

strDataSource = "C:\MyDataBase.accdb"
strQuery = "SELECT * FROM D_Current_Extracts"

Set cnn = New ADODB.Connection
With cnn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Properties("Data Source") = strDataSource
    .Open
End With

Set rst = New ADODB.Recordset

rst.Open strQuery, cnn, adOpenForwardOnly, adLockReadOnly


Dim ws As Worksheet
Dim pt As PivotTable

Set ws = Worksheets("My Pivot Table")
Set pt = ws.PivotTables(1)


' How to I take the data I have just fetched and put it into the pivot table????

End Sub

Am I on the right track? How do I finish this off. Any assistance would be greatly appreciated. Cheers.

 

[Harry Flashman]

Thus far I have been unsuccessful in solving my problem in the way I originally planned, andI *think* I know why (not 100% sure).

The current pivot table this type of Connection Type: Microsoft Access Database, which I think might be the wrong type of connection to update with type of routine that I have written.

However if I forget about updating the data in the existing Pivot Table and simply create a new pivot table I have no problems.

Here is my code:'

Sub GetDataFromAccessIntoPivotTable()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strDataSource As String, strQuery As String

strDataSource = "C:\MyDataBase.accdb"
strQuery = "SELECT * FROM D_Current_Extracts"

Set cnn = New ADODB.Connection
With cnn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Properties("Data Source") = strDataSource
    .Open
End With

Set rst = New ADODB.Recordset

rst.Open strQuery, cnn, adOpenForwardOnly, adLockReadOnly


Dim ws As Worksheet
Dim pt As PivotTable
Dim ptCache As PivotCache

Set ws = Worksheets("Sheet1")
Set ptCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal)
Set ptCache.Recordset = rst
ptCache.CreatePivotTable TableDestination:=ws.Range("B2")

End Sub

If I examine the connection properties of this new pivot table I see it has a different sort of Connection Type:= ADO Recordset

Previously tried changing the Recordset property of my existing to pivotcache to the recordset that I had fetched (rst), but that resulted in an error. Maybe this was because my existing connection was the wrong type?

 

[Harry Flashman]

So that last routine created a pivot table with a different sort of Connection Type, namely a ADO Recordset.

I would prefer not to have to delete and recreate my pivot table each time. I would like to simply update the data in the PivotCache. But how can I do this?

Still working with most of the same code in my first routine I have come up with this:

Sub GetDataFromAccessIntoPivotTable2()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strDataSource As String, strQuery As String

strDataSource = strDataSource = "C:\MyDataBase.accdb"

strQuery = "SELECT * FROM D_Current_Extracts"

Set cnn = New ADODB.Connection
With cnn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Properties("Data Source") = strDataSource
    .Open
End With

Set rst = New ADODB.Recordset

rst.Open strQuery, cnn, adOpenForwardOnly, adLockReadOnly


Dim ws As Worksheet
Dim pt As PivotTable
Dim ptCache As PivotCache

Set ws = Worksheets("Sheet1")
Set pt = ws.PivotTables(1)

' THIS LINE THROWS UP AN ERROR
pt.PivotCache.Recordset = rst
' ERROR: Wrong number of arguments or invalid property assignment
End Sub

But I am getting an error message: Wrong number of arguments or invalid property assignment

Can anyone see where I am going wrong?

 

[Harry Flashman]

Okay I have figure out where I was going wrong. I don't really understand but my new code works. The following code successfully refreshes the data in my pivot table with an ADO Connection.

Sub GetDataFromAccessIntoPivotTable2()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strDataSource As String, strQuery As String

strDataSource = "\\MBMELFIL703\Melbourne\Precis\Clients\Australian Auto\Auto gen\Auto General Workings\DMII Databases\DCurrentExtracts.accdb"

strQuery = "SELECT * FROM D_Current_Extracts"

Set cnn = New ADODB.Connection
With cnn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Properties("Data Source") = strDataSource
    .Open
End With

Set rst = New ADODB.Recordset

rst.Open strQuery, cnn, adOpenForwardOnly, adLockReadOnly


Dim ws As Worksheet
Dim pt As PivotTable
Dim ptCache As PivotCache

Set ws = Worksheets("Sheet2")
Set pt = ws.PivotTables(1)

' ****This is the bit I changed****
Set ptCache = pt.PivotCache
Set ptCache.Recordset = rst
ptCache.Refresh

cnn.Close
Set cnn = Nothing
Set rst = Nothing

End Sub

For some reason I had to use the Set keyword....

Set ptCache.Recordset = rst

This I don't really understand because I thought Set was used to assign an object variable. I know that I used ptCache as a variable, but I don't understand how ptCache.Recordset is a variable too. Is it?

Whatever the reason, that was the syntax I had to use. Problem solved. Illuminating comments welcome. Cheers.