How to create a SQL Server Linked Server to MS Access

I need to create a linked server in SQL Server 2008 R2 to a MS Access 2007 database.

Does anyone have any tips?

Thanks,
Steve
fcsITAsked:
Who is Participating?
 
peter57rConnect With a Mentor Commented:
I previously thought this was not possible but seeing your Q I had another look round and came on this..

http://social.msdn.microsoft.com/Forums/en-NZ/sqlgetstarted/thread/11a8b5e5-3f10-41db-bc1a-266cdc0aa072

Look nearly at the end of the thread.
0
 
Deepak ChauhanConnect With a Mentor SQL Server DBACommented:
There is two  blck of script both are working fine u can opt whatever is usable for you

1.
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'ACCESS', @srvproduct=N'access', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'f:\test.accdb'
GO
 
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'ACCESS', @locallogin = NULL , @useself = N'False'
GO

or you can use below template  only put you actual variables it is tested and working fine..

2.
EXEC sp_addlinkedserver
    @server = N'Your Linked Server Name',
    @provider = N'Microsoft.ACE.OLEDB.12.0',
    @srvproduct = N'Access2007',
    @datasrc = N'C:\path\to\your\db.accdb'
GO

-- Set up login mapping using current user's security context
EXEC sp_addlinkedsrvlogin
    @rmtsrvname = N'Your Linked Server Name',
    @useself = N'TRUE',
    @locallogin = NULL,
    @rmtuser = N'Your Linked Server Name',
    @rmtpassword = ''
GO

-- List the tables on the linked server
EXEC sp_tables_ex N'Your Linked Server Name'
GO

-- Select all the rows from table1
SELECT * FROM [Your Linked Server Name]...table1
0
 
fcsITAuthor Commented:
Quick question that I forgot to include in my original post, the Access database is supplied by a third party vendor.  The credentials for accessing it do not include a username, but does have a password.

Everything I've tried says a username is required, but the database itself doesn't have one, it just has the password portion.  Will that work with these options?
0
 
fcsITAuthor Commented:
I just tried to create the linked server using your instructions, but ran into the same creditials problem I've been hitting using every other method.

How can I create a linked server to an Access database that is password protected, but has not username associated with that password?
0
 
fcsITAuthor Commented:
Still no luck on this.  I found an article (wish I had copied the URL to it to post here) on Microsoft's site talking about this issue.  They said you have to jump through a lot of hoops to get a user account between the SQL Server and the server the Access db resides on, such as creating a temp directory, assigning it permissions, and several other things.

I'm abadoning this whole process and will either figure out a better way, or just have the users do it manually.

Thanks everyone for your help!
0
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.

All Courses

From novice to tech pro — start learning today.