Setup Linked Server for Access 2007 in SQL Server 2008


EXEC sp_addlinkedserver

@server = N’my2007Database’,

@provider

= N’Microsoft.ACE.OLEDB.12.0′,

@srvproduct

= N’OLE DB Provider for ACE 2007′,

@datasrc

= N’C:\datatest\Database1.accdb’

GO

— Set up login

EXEC

sp_addlinkedsrvlogin

@rmtsrvname = N’my2007Database’,

@useself

= N’TRUE’,

@locallogin

= NULL,

@rmtuser

= N’Admin’,

@rmtpassword

= NULL

GO

–****Resolve permission issue

USE

[master]

GO

EXEC

master . dbo. sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ , N’AllowInProcess’ , 1

GO

EXEC

master . dbo. sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ , N’DynamicParameters’ , 1

GO

http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/bb2dc720-f8f9-4b93-b5d1-cfb4f8a8b1cb

–******

–List the tables on the linked server

EXEC

sp_tables_ex N’my2007Database’

GO

–Test run for a table

SELECT

* FROM my2007Database…myTableInAccess

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s