Access 2007->Excel 2007 Q

londonbairn

londonbairn

londonbairn

Soldato
Joined
29 May 2005
Posts
5,622
Location
West London
I have an Excel workbook which gets Data from Excel (simply using a ODBC connection, using the Data->From Access function)

I have a simple Macro which refreshes the table source (through Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False) in order to faciliate an end user tactical Excel Application.

The problem is, when you run it on the machine the data connection was setup, it works perfect. When you try on another machine, it fails to work and comes up with numerous legacy ODBC dialogues, that look Win 95 esque.

Any ideas so that the link is maintained between machines? I am using an absolute network path (eg \\SERVER\blah\blah.accdb) rather than V:\blach.accdb.
 
Yes they do.

The error is "The Microsoft Jet database engine cannot open the file 'file path'. It is already opened exclusively by another user, or you need permission to view its data."

ODBC String is (removed the file share link). I think the problem may be the string in bold?

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=\\accdb;Mode=Read;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=1;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
 
For some reason, I have to completely shut down some of my Access DBs that I'm linking to Excel via ODBC, before I refresh the query in Excel, otherwise I get some kind of permissions error.

So all I can suggest is that you make sure the Access DB isn't still open on the other PC and try again.
 
You must log in or register to reply here.
Back
Top Bottom