Sql-server – SQL Server Linked Server to Microsoft Access

linked-serverms-accessoledbsql servertsql

I have tried to make use of linked servers in SQL Server 2008 by doing the following 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:

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 on my access database but looking at the documents I understood I had to use the sp_addlinkedsrvlogin like the above. I have tried it without a login either.

Bit I still get the same error – what is going?!

Thanks all

Debug Output

Array ( [0] => Array ( [0] => 42000
[SQLSTATE] => 42000 [1] => 7303 [code]
=> 7303 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot
initialize the data source object of
OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked
server "access1265293168". [message]
=> [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot
initialize the data source object of
OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked
server "access1265293168". ) [1] =>
Array ( [0] => 01000 [SQLSTATE] =>
01000 [1] => 7412 [code] => 7412 [2]
=> [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".
[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".
) )

Best Answer

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

It may be you just need your server name in single quotes 'access1t'.

[ Does your logged in user have permission to access the root of C: drive? ]

Related Topic