problem while creating a linked server from SQL-Server2000 to an Access97 database

I was attempting to create a linked server from SQL-Server2000 to an Access97 mdb file using the following scripts

EXEC sp_addlinkedserver

@.server='REMOTE_OFFICE',

@.srvproduct='Jet 4.0',

@.provider='Microsoft.Jet.OLEDB.4.0',

@.datasrc='F:\RealEstate_Office1.mdb'

and

EXEC sp_addlinkedsrvlogin

@.rmtsrvname='REMOTE_OFFICE',

@.useself='false',

@.locallogin='sa',

@.rmtuser='Admin',

@.rmtpassword=NULL

And while querying the linked server from the query analyzer using the following select command

SELECT *

FROM REMOTE_OFFICE.RealEstate_Office1.dbo.E_GOV_RE_OK

I got the following error

Server: Msg 7312, Level 16, State 1, Line 1

Invalid use of schema and/or catalog for OLE DB provider 'Microsoft.Jet.OLEDB.4.0'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.

OLE DB error trace [Non-interface error].

could you explain why this happen

The error indicates that Access does not support database or schema names. Try leaving those parts empty. SELECT * FROM REMOTE_OFFICE...E_GOV_RE_OK.

This post belongs in another forum.
|||Moving to the SQL Server Data Access Forum.|||

Thanks JayH,

after a little search I found that it was an article describes how to use a Microsoft SQL Server distributed query to retrieve data from a secured Microsoft Access database and its link is as follows

http://support.microsoft.com/?kbid=246255

Article ID : 246255
Last Review : February 12, 2007
Revision : 4.3

Thanks all.