Pivot table connection to access database - update data source with vb

djredden73

New Member
Joined
Aug 10, 2012
Messages
29
I have 2 existing Pivot Tables that connect to 2 separate tables from the same Access Database. (all on Sheet2
On Sheet3 in cell D5 I give the user the ability to type in the source of where they placed the source Access Database.

I tried to utilize VB to look at Sheet3.Range("D5") to update the source; but it basically keeps the same source I originally created. I have a feeling it is due to the Connections("STARS_DASHBOARD7") in red font below. But I don't know how else to accomplish this. Please help! :)

Rich (BB code):
 ActiveSheet.PivotTables("PivotTable1").PivotSelect "Contract[All]", xlLabelOnly _
        , True
    Range("B6").Select
    With ActiveWorkbook.Connections("STARS_DASHBOARD7").OLEDBConnection
        .BackgroundQuery = True
        .CommandText = Array("Identified_Data")
        .CommandType = xlCmdTable
        .Connection = Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & Sheet3.Range("D5") & "" _
        , _
        ";Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Data" _
        , _
        "base Password="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global " _
        , _
        "Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;J" _
        , _
        "et OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support" _
        , " Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False")
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("STARS_DASHBOARD7")
        .Name = "STARS_DASHBOARD7"
        .Description = ""
    End With
    ActiveWorkbook.Connections("STARS_DASHBOARD7").Refresh
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Watch MrExcel Video

Forum statistics

Threads
1,122,670
Messages
5,597,483
Members
414,146
Latest member
marginmakerb

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