[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

MS Access Linked Server

Posted on 2001-06-27
4
Medium Priority
?
520 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

656 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