Solved

SQL SERVER 2005 LINKED SERVERS

Posted on 2008-06-17
4
306 Views
Last Modified: 2008-07-10
I want to link a password protected Microsoft Access 97 Database to SQL Server 2005 as a linked server so that all of the mdb tables will be available through the sql server engine as if that database was a database inside of sql server.

Can i do this?

 if so can you give me a step by step

Thanks in advance.
0
Comment
Question by:talker2004
  • 2
4 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21806968
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 21810534
use the sp_linkedserver facility... Here I have an Access database name DB4 and lives in c:\access+mdb\db4.mdb. I create a Linked Server with a name Access_DB4 and can then use it in any query - just have to prefix the table name with the linked server name...


EXEC sp_addlinkedserver
      @server = 'Access_DB4',
      @provider = 'Microsoft.Jet.OLEDB.4.0',
      @srvproduct = 'OLE DB Provider for Jet',
      @datasrc = 'c:\access_mdb\db4.mdb'
GO

EXEC sp_addlinkedsrvlogin
      @rmtsrvname = 'Access_DB4',
      @useself = 'FALSE',
      @locallogin = NULL,
      @rmtuser = 'Admin',
      @rmtpassword = NULL
GO  -- note you will probably set up the real user name and password above. also found much more success leaving out useself and locallogin...

EXEC sp_tables_ex 'Access_DB4'
GO -- gets the list of tables...

SELECT * FROM Access_DB4...Customers

-- then remove the linked server if neede with : sp_dropserver AccessDB4, droplogins
0
 
LVL 7

Author Comment

by:talker2004
ID: 21850217
Got this to work only after i removed the database password of of the Access Database. It is a simple database password on the databsae not a workgroup password. How do i specify the password for the database so that SQL server can connect to my mdb?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21851641
in  the @rmtuser and @rmtpassword = needs to be you access database user and password...
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

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.
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

863 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

27 Experts available now in Live!

Get 1:1 Help Now