Linking REMOTE access database to SQL Server

[BradEdwards]

I'm running MSSQL 2000 and trying to link to an Access 97 database on a separate Novell Server. It seems that everything I've read says that the Access db needs to reside locally on the SQL Server. Is there anyway around this??

I have 24 db's that are downloaded on a nightly basis from our branches. I wrote a program that takes the Journal table from each db and uploads it to the SQL Server. I do this by linking to the SQL table in Access and inserting the records. This works fine but it takes about 15 minutes. I'm thinking there has to be a faster/better way.

I tried creating a stored procedure which uses the OPENROWSET function to connect to the Access db but it always gives me an "error 7399: OLE DB Provider 'Microsoft.Jet.OLEDB.4.0' reported an error". I've also tried running the sp_addlinkedserver and it gave me the same error when trying to access it. Is there anything else that I can try without having to have the db's on the same server as SQL. Thanks.

 

[tlbroadbent]

You should be able to link or open the Access database. Keep in mind the security context is the account that you use to start SQL Server. You need to make certain that the SQL Server account has permissions (Novell rights) to the volume and path where the Access databases reside. Frequently, the SQL Server account is a local account with no permissions across the domain or a domain account with limited persmissions. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.