Solved

MS Access Linked Server

Posted on 2001-06-27
4
511 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 142

Accepted Solution

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

Cheers
0
 
LVL 142

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 142

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now