Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2001
    Posts
    36

    Unanswered: Linked Server pointing to Access DB

    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 ?

    Any help is appreciated.

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Have you tried the query without OPENQUERY? OPENQUERY passes the query to the remote server for processing, which, I believe with Access will not work, as it is not actually a server.

    Try:

    SELECT id, verifier FROM [newverification]...[accounts]
    -bpd

  3. #3
    Join Date
    Nov 2001
    Posts
    36
    I get the following thing :

    Server: Msg 7313, Level 16, State 1, Line 1
    Invalid schema or catalog specified for provider 'MSDASQL'.
    OLE DB error trace [Non-interface error: Invalid schema or catalog specified for the provider.].

  4. #4
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Couple of questions...

    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.

    Retry the query.

    Here's a link for more info:
    http://msdn.microsoft.com/library/de...qd_12_6a44.asp
    -bpd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •