Linked server to a networked Access database

[Rilez]

This is driving me insane. I have created a link to an access database which resides on a network share using the UNC path. It all works find when administrating on the SQL server itself but will not work when administrating from another machine, giving the error...

Error 7399: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
OLEDB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize returned 0x80004005: ].

...when I click on the tables within the linked server within enterprise manager. If, however I copy the file onto the SQL server machine and link to it using the file path it works fine. Now heres the confusing part. If I use a SQL Server user (ie don't use windows authentication) I can get the table list from the UNC mapped link. I am an Admin and have full control of that directory, the file is not locked. As well as trying UNC I have tried an ODBC link anda network drive mapping.

Short of creating another Access database on that machine, with linked tables to the information I want and then linking THAT database into SQL server is there any way of resolving this?

This is a full Windows 2000/2003 Active Directory setup.

 

[webuser]

I'm not sure if this helps at all but I have had big time problems with Linked Servers (Text and DBfs) running across the Network. I haven't tried it with MDBs so I can't speak about that. It got so frustrating and everything we tried ended up causing intermittent problems that we decided to simply have a routine that copied the text files/dbfs from the remote server to the SQL Server server and then linked to the copy on the SQL Server. This worked for us b/c we only needed to use the data from SQL for look up purposes...

 

[Rilez]

Well it is reassuring that it is not only us that is having problems. ideally I'd like to be able to update the information in that database when needed through the web interface and linked servers would have ment a lot less work than is now on the cards. I have two months to get a very complex product out to the internal market and this problem really hasn't helped - but hey thats the whole point behind being a developer right ???

 

[Rilez]

riight. Big problems with the active directory and the upshot - the linked database now works ... Not going to complain