Solved

MS Access Linked Server

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

912 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

21 Experts available now in Live!

Get 1:1 Help Now