HERE IS THE SOLUTION FOR ALL OF YOU WITH THE SAME PROBLEM:

-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...