I have added a linked server on my sql 2000 db server, pointing to MS Access 2000 database.
when I fire a select statement :
SELECT * FROM OPENQUERY(NewVerification, 'SELECT id,verifier FROM newverification...accounts')
I get the following error :
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'SELECT id,verifier FROM newverification...accounts'. The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0', Query=SELECT id,verifier FROM newverification...accounts'].
I am able to see all the tables thru EM which to means I am successfully able to connect to that DB. But then why is not letting me query that linked server ?
Is the Access DB local to your SQL server, or on a remote system? I have sometimes had problems accessing remote Access databases, though those were usually on a different subnet, and it was Access preventing the connection because of it's "security" settings.
Is the database password protected?
Have you set up the proper security context?
If it is not protected, right-click on your Linked Server, and select Properties.
On the Security tab, select the last radio button "Be made with the security context:", and enter Admin as the remote login with no password.