HERE IS THE SOLUTION FOR ALL OF YOU WITH THE SAME PROBLEM:
Тема на sql.ru
-On your Server Objects ->
Linked Servers ->
Providers, Righ Click on Microsoft.ACE.OLEDB.12.0 and select Options. Check the option ->
Allow inprocess
HERE IS THE SOLUTION FOR ALL OF YOU WITH THE SAME PROBLEM:
- Install the 2007 Office System Driver: Data Connectivity Components on your server.
here is the link:
http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en
- Make shure you have SQL SERVER 2005 Standard installed. IT DOESN'T WORK'S ON SQL EXPRESS. No mather what you do. It only works on the standard versions...
- Install SQL SERVER SP2
- On your Server Objects > Linked Servers > Providers, Righ Click on Microsoft.ACE.OLEDB.12.0 and select Options. Check the option Allow inprocess
- Use this T-SQL template (replacing by your own values) to create a connecto to the Linked Server:
Code Snippet
-- ==============================================
-- Add Linked Server Access 2007 ACCDB template
-- BY JOSÉ ALVES @ CCG - UMINHO - PORTUGAL
-- ==============================================
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
I hope this helps you. To me it was a big headache but i figured out after many tries...
Greetings from Portugal