Linking Access table to SQL Server (1 Viewer)

I need to use an Access table (on a network drive) in a T-SQL script in SQL Server 2000. I used the following code to create a linked server on our server:
EXEC sp_addlinkedserver
@server = 'NPVData',
@srvproduct = 'OLE DB Provider for Jet',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@datasrc = '\\garfield\apps\database\NPV\npvdata.mdb'

To check the connection, I ran this script:
Select *
From NPVData.npvdata.mdb.tblProposals

(tblProposals is a table in npvdata.mdb)

This resulted in error message 7399 (Authentication failed...The workgroup information file is missing or opened exclusively). Why couldn't I read the records in the table? Is it because the server to which I added the linked server does not have Access loaded?

This works for me

EXEC sp_addlinkedserver
@server = 'ACCESS_TRAINING_DB',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Providr for Jet',
@datasrc = '\\argon\data\training\training.mdb'

To query use the complete 4 part definition
i.e. Server.Database.schema.table

This works:-
SELECT * FROM ACCESS_TRAINING_DB...Employee
or
SELECT * FROM ACCESS_TRAINING_DB..dbo.Employee

This works across the network but I am on SQL 2005.

Regards
George Cooper.

Hi

Just out of interest can you not just import the access table into sql server and do away with access?

I tried:
FROM NPVData.npvdata.tblProposals (got invalid object)
then
FROM NPVData.npvdata.dbo.tblProposals (got error 7399)

I can't import the table into SQL because it's a linked table in an Access utility loaded on several dozen PCs and used daily by people who have restricted network access.

So now what?