Hi, I'm running an Excel 2007 macro that imports data from an Access 2007 database by calling an Access query. When the database file is on my computer, everything works perfectly. However, when the file is in a network folder and I run the same macro (after changing the filepath, of course), I get a message asking me: "Do you want to connect to 'O:\EXP\Data\Test\Cycle.mdb'?". If I click "yes", the macro transfers the data correctly. The macro then has to call another query and I'm getting the same message. The process is repeated for 15 queries in two different databases, so getting this message everytime is very annoying. Would anybody know how to fix this? If it cannot be fixed, is it possible to write code to answer yes automatically to this message? Here is an example that is used in my macro to call the query and import the data:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
>>><o> </o>
' Import Access database:<o></o>
<o></o>
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _<o></o>
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=O:\EXP\Data\Test" _<o></o>
, _<o></o>
"\Cycle.mdb;Mode=ReadWrite;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Databas" _<o></o>
, _<o></o>
"e Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bul" _<o></o>
, _<o></o>
"k Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet " _<o></o>
, _<o></o>
"OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Co" _<o></o>
, "mplex Data=False"), Destination:=Range("$AA$4")).QueryTable<o></o>
.CommandType = xlCmdTable<o></o>
.CommandText = Array("qrtTransfer")<o></o>
.RowNumbers = False<o></o>
.FillAdjacentFormulas = False<o></o>
.PreserveFormatting = True<o></o>
.RefreshOnFileOpen = False<o></o>
.BackgroundQuery = True<o></o>
.RefreshStyle = xlInsertDeleteCells<o></o>
.SavePassword = False<o></o>
.SaveData = True<o></o>
.AdjustColumnWidth = True<o></o>
.RefreshPeriod = 0<o></o>
.PreserveColumnInfo = True<o></o>
.SourceDataFile = _<o></o>
"O:\EXP\Data\Test\Cycle.mdb"<o></o>
.ListObject.DisplayName = "Table_Cycle8"<o></o>
.Refresh BackgroundQuery:=False<o></o>
End With<o></o>
<o></o>
>>><o> </o>
<o> </o>
Any help would be greatly appreciated. Thanks.
Sebastian <o></o>
<o> </o>
>>><o> </o>
' Import Access database:<o></o>
<o></o>
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _<o></o>
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=O:\EXP\Data\Test" _<o></o>
, _<o></o>
"\Cycle.mdb;Mode=ReadWrite;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Databas" _<o></o>
, _<o></o>
"e Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bul" _<o></o>
, _<o></o>
"k Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet " _<o></o>
, _<o></o>
"OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Co" _<o></o>
, "mplex Data=False"), Destination:=Range("$AA$4")).QueryTable<o></o>
.CommandType = xlCmdTable<o></o>
.CommandText = Array("qrtTransfer")<o></o>
.RowNumbers = False<o></o>
.FillAdjacentFormulas = False<o></o>
.PreserveFormatting = True<o></o>
.RefreshOnFileOpen = False<o></o>
.BackgroundQuery = True<o></o>
.RefreshStyle = xlInsertDeleteCells<o></o>
.SavePassword = False<o></o>
.SaveData = True<o></o>
.AdjustColumnWidth = True<o></o>
.RefreshPeriod = 0<o></o>
.PreserveColumnInfo = True<o></o>
.SourceDataFile = _<o></o>
"O:\EXP\Data\Test\Cycle.mdb"<o></o>
.ListObject.DisplayName = "Table_Cycle8"<o></o>
.Refresh BackgroundQuery:=False<o></o>
End With<o></o>
<o></o>
>>><o> </o>
<o> </o>
Any help would be greatly appreciated. Thanks.
Sebastian <o></o>