Change OLEDB Pivot Connection from an Access-Database to another

[ULSWK]

Hi all,
I'm using an Access Database as Source for Power Pivot in my Excel Sheet.
In this example the access database is this: "C:\Temp\Test.accdb"
Trying to change this connection to eg. "C:\Temp\Test2.accdb", I receive error 1004.

Any clue how to change an OLEDB Access Connection as source for a pivot table?

Sub ChangeConnection()
Dim wbk As Workbook
Dim cn As WorkbookConnection
Dim oledbCn As OLEDBConnection
Dim strConn$

Set wbk = ThisWorkbook
For Each cn In wbk.Connections

If cn.Type = xlConnectionTypeOLEDB Then
Set oledbCn = cn.OLEDBConnection
strConn = oledbCn.Connection

Debug.Print strConn
' Result:
' OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\Test.accdb;Persist Security Info=false

oledbCn.Connection = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\Test2.accdb;Persist Security Info=False;"
' Result :
' ERROR 1004: Application or object oriented error

End If
Next
End Sub

 

[alansidman]

Power Pivot allows you to import directly from MS Access without the need for VBA. Look at this link for how to do this easily with no programming required.

 

[ULSWK]

Hi alansidman,

thank's for that advice, I know this, but this doesn't solve my problem.

I have to duplicate excel workbooks (with included power pivot table) many times and have to change the source access database in each resulting workbook to a different access database.
This makes sure, that every single recipient of the workbooks receives their suitable data portion.
So everything is equal, but not the used database.
Therefore I'm looking for a possibility to change the source database via vba and not manually....

 

[alansidman]

I'm not really skilled in VBA beyond some basic looping. If I were to do this based upon what you have just provided, I would build a parameter query in Power Query. Then close and load to the Data Model where I would then build my pivots. I understand your need for VBA. Hopefully, someone with better skills than me will jump in here soon. Good Luck.