Solved

MS Access Linked Server

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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 video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

821 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