Personalize

Text Size

Layout

Navigation Position

Willie Tilton

Check out my l337 LCD Tan!

<< Visual Studio 2003 CVS Control | Home | Links of the day >>

Creating a Linked Server (in SQL Server) to a MS Access Database

I thought this was pretty intuitive, but I had to look around for awhile before I got it. Here's how I did it (pretty easy!):

First open up your SQL Analyser and copy this in:

sp_addlinkedserver '', 'Access 97', 'Microsoft.Jet.OLEDB.4.0', 'C:\Inetpub\wwwroot\fullPathToDatabase\DatabaseName.mdb'

Change the to what you want it to be called. eg. MyData

Modify the path to your MS Access database.

This is the step where I got confused. Now paste in (comment or remove the other one):

sp_addlinkedsrvlogin '', false, '', 'Admin', NULL

I wanted to grant the access to the admin so I put in 'sa' for the user. Bing bam boom, done!

This only works for a MS Access database with no username and password. If you have that you're going to have to modify your registry, and then fill in the Username for 'Admin' and password for NULL above...read the quoted material from MS's website:

To access a secured Access database, configure the registry (using the Registry Editor) to use the correct Workgroup Information file used by Access. Use the Registry Editor to add the full path name of the Workgroup Information file used by Access to this registry entry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\SystemDB

Friday, April 22, 2005 12:31 PM

Your Comments.

# SQL Server Linked Server: Access
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_12_6a44.asp
  ...

Left by Andrew Renner at 6/14/2005 11:45 AM
# re: Creating a Linked Server (in SQL Server) to a MS Access Database
The syntax is a little strange (not a typo) to access a table:

SELECT * FROM LinkServerName...TableName

eg.
SELECT * FROM MNPForums...tblCategory

Wierd eh?

Left by Willie Tilton at 4/22/2005 12:42 PM
# re: Creating a Linked Server (in SQL Server) to a MS Access Database
Notation in SQL is: Server.Database.Schema.Table

When Access is a linked server there is no database or schema hence:
Server...Table

SELECT * FROM ServerName...TableName

Left by Parax at 11/26/2006 10:42 PM
# re: Creating a Linked Server (in SQL Server) to a MS Access Database
The part of your post that referenced adding a user was helpful. I have a web application that references stored procs that hit the Access DB. Since I'm connecting with a web user, I modified your string as: sp_addlinkedsrvlogin 'MY_SERVER', false, 'MY_WEB_USER', 'Admin', NULL

Worked GREAT! Thanks!!!

Left by Grateful! at 12/26/2007 2:24 PM
# re: Creating a Linked Server (in SQL Server) to a MS Access Database
Thanks very much! I was really struggling with this and stumbled on your solution.

Cheers!

Left by Marc at 1/10/2008 5:12 PM
# re: Creating a Linked Server (in SQL Server) to a MS Access Database
Extremely helpful. Almost makes the 2 hours I already spent trying to fix this seem worthwhile!

Left by Dan at 6/24/2008 3:15 PM
# Creating a Linked Server (in SQL Server) to a AS/400 Database
I want to get the data from as400 into SQL server. So I need to create a linked server (SQL server) to a AS/400 Database through ODBC. But don't know how? Please help. Thanks.

Left by SQLAS400 at 7/31/2008 1:18 PM

Your Reply.

Comment Form.

Fields denoted with a "*" are required.

*Your name:

*Subject:

You may also like to leave your email or website.

*Your message:

Please add 7 and 4 and type the answer here:

Enter the code shown above: