Solved

MS Access Linked Server

Posted on 2001-06-27
4
515 Views
Last Modified: 2012-06-27
SQL 7.0 no SP, Win NT 4.0 SP4
I'm trying to link an MSaccess Database with a linked server with

EXEC sp_addlinkedserver

    @server = 'AccessBOM',

    @provider = 'Microsoft.Jet.OLEDB.4.0',
    @srvproduct = 'OLE DB Provider for Jet',
    @datasrc = 'D:\Data\Access\BILLOFMaterials.mdb'

The creation goes OK... but the select statement (--Select * FROM AccessBOM.BillOfMaterials.dbo.Local_Parts) fails with
"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."

Is there a way to embed a LOGIN Name and or password to an access linked server?

The Statement below works fine....
SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
  'D:\Data\Access\BillOfMaterials.mdb';'admin';'', DataEntryQuery)
  AS a
Where NPG_Part = '29996'

Please help
Thanks
Paul
0
Comment
Question by:PaulCr125
  • 3
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 50 total points
ID: 6231707
This should work:
Select * FROM AccessBOM...Local_Parts

Cheers
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6231726
Or this:
select * from OPENQUERY (AccessBom , 'SELECT * FROM LocalParts') AS LocalParts

Cheers
0
 

Author Comment

by:PaulCr125
ID: 6231992
AngelIIII,
   It was the extra dot "." What is the rule of thumb, 3 dots, 2 dots, 1 dot, how do you keep them straight.

Paul
 
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6232365
If you access a table in the current server.database.owner/user, you don't need any dots:
User=test; table owner=test
select * from TestTable

if you access a table from another user, you need 1 dot:
user=test; table owner=dbo
select * from dbo.TestTable

If you access a table from another database (on the same SQL Server), you need 2 dots:
user/database=test/testdb; table owner/database=dbo/Maindb
select * from Maindb.dbo.testTable

if you access a table in a linked server, you need 3 dots:
select * from LinkedServerName...TestTable
On some server products (ie Oracle), you need the owner:
select * from LinkedServerName..scott.TestTable

BTW: it is a good practice in the databases to let dbo own all the tables

Cheers

0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
The AZure backup problem 11 51
sql query display the latest row 10 51
IF SQL Query 12 26
Sql case statement to calculate totals 5 32
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question