Posted on 2008-06-17
Medium Priority
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.
Question by:talker2004
  • 2
LVL 143

Expert Comment

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

Accepted Solution

Mark Wills earned 2000 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'

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

Author Comment

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?
LVL 52

Expert Comment

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

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

624 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