Create dynamic Connections from MS access for Pivot Table in excel

[amitmse]

This is the first time I am using VBA and MS access.


I developing a dynamic chart in excel for my client.This chart is link with pivot table and pivot is linked with MS access. There are couple of issue which I am facing it.


when I am trying in different system and getting a pop-up message that "MS Access data is not available in my system path but it is available in ThisWorkbook.Path, do you want to continue". Ideally I should not get this message if I am using ThisWorkbook.Path . How to fix it?
Second issue: When I am changing the MS access dataset, it is throwing an error. I think this problem is due to connection (ThisWorkbook.Connections.add "Database35") (ActiveWorkbook.Connections("Database35")).
Below code is working if I am not changing the MS access dataset:


ThisWorkbook.Connections.add "Database35", "", Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=ThisWorkbook.Path & \Database3.accdb;Mode=Share Deny " _
, _
"Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:En" _
, _

"gine 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;Jet 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" _
), Array("94"), 3
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
ActiveWorkbook.Connections("Database35"), Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:=ActiveSheet.Cells(2, 2), TableName:=pvt_name _
, DefaultVersion:=xlPivotTableVersion14






Below code is not working when I am changing the MS access dataset and connection (screen shot is attached):


ThisWorkbook.Connections.add "OMF", "", Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=ThisWorkbook.Path & \test.accdb;Mode=Share Deny " _
, _
"Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:En" _
, _
"gine 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;Jet 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" _
), Array("94"), 3
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
ActiveWorkbook.Connections("OMF"), Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:=ActiveSheet.Cells(2, 2), TableName:=pvt_name _
, DefaultVersion:=xlPivotTableVersion14




Thanks for your help!