Importing data in Excel 2007 using Access query

[sp1234]

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>

 

[Scuderia]

Hi sp

Try adding your networked DB to the Access trusted locations.

In 'Access Options'....'Trust Centre'....'Trust Centre settings'

 

[sp1234]

I added the database folder to my trusted locations and also checked the "Allow trusted locations on my network (not recommended)". However, I'm still getting the same message. Any other idea?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Sebastian<o></o>

 

[Scuderia]

Sebastion

Yes .... tried and tested this time (sorry for misleading you earlier)

At the start of your macro place

Application.displayalerts = False

and then at the end

Application.displayalerts = True

 

[sp1234]

It works perfectly, thanks!