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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak ChauhanSQL 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
peter57rCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.