Linked SQL Server with Microsoft Access

Question

I tried using linked servers in SQL Server 2008 by following these steps to access a Microsoft Access 2003 table.

EXEC sp_addlinkedserver access1t, 'OLE DB Provider for Jet', 'Microsoft.Jet.OLEDB.4.0', 'C:\tester.mdb' EXEC sp_addlinkedsrvlogin access1t, FALSE, Null, Admin, Null GO CREATE VIEW TI001APCE1265 AS SELECT * FROM access1t...Table1 

However, I get the error message:

 OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "access1t" returned message "Unspecified error". Msg 7303, Level 16, State 1, Procedure TI001APCE1265, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "access1t". 

There is no password / username in my access database, but looking at the docs, I realized that I should use sp_addlinkedsrvlogin as described above. I tried it without logging in.

Bit, I still get the same error - what happens ?!

Thank you all

Debug output

Array ([0] => Array ([0] => 42000 [SQLSTATE] => 42000 [1] => 7303 [code] => 7303 [2] => [Microsoft] [SQL Server Native Client 10.0] [SQL Server] Unable to initialize the data source object OLE DB provider "Microsoft.Jet.OLEDB.4.0" for the linked server "access1265293168". [Message] => [Microsoft] [Native SQL Server 10.0 client] [SQL Server] Unable to initialize the object OLE DB provider data source “Microsoft.Jet.OLEDB.4.0” for linked server “access1265293168”.) [1] => Array ([0] => 01000 [SQLSTATE] => 01000 [1] => 7412 [code] => 7412 [2] => [Microsoft] [Native SQL Server 10.0 client] [SQL Server] OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "access1265293168" in return message "Unspecified error". [message] => [Microsoft] [SQL Server Native client 10.0] [SQL Server] OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "access1265293168" returned message "Unspecified error" .))

Answer 1

Have you tried with named parameters ?:

 EXEC sp_addlinkedserver @server = 'access1t', @provider = 'Microsoft.Jet.OLEDB.4.0', @srvproduct = 'OLE DB Provider for Jet', @datasrc = 'C:\tester.mdb' GO 

Perhaps you just need your server name in single quotes 'access1t' .

[Your registered user has permission to access the root of the C: drive? ]