SQL Server Linked Server to Microsoft Access
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 videos on Youtube
![Abs](https://i.stack.imgur.com/ZPArK.jpg?s=256&g=1)
Abs
Updated on April 15, 2022Comments
-
Abs about 2 years
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". ) )
-
Abs over 14 yearsI am logged in as Admin. I thought because I passed in false for @useself attribute of sp_addlinkedsrvlogin - it would only use the username and password I have supplied to access the Microsoft access database. I have also just tried your code and removed my first line and I still get the same error. I have added some debug output with sql server error codes, but I have no idea what they mean!
-
Fionnuala over 14 yearsI played around with this a bit, and for me the error seems to have been due to permissions. When I logged in to SQL Server as the Admin user for this PC, I could connect to c:\docs\test.mdb as illustrated above, with other messing about, I could not.
-
Abs over 14 years@Remou I think its a permissions/credentials problem. I am using this
EXEC sp_addlinkedsrvlogin acc465tghv, TRUE
and this seems to be working. I am just testing and playing around with now. -
David-W-Fenton over 13 yearsA Jet/ACE data store has to be writable to be used, so you need to store it in a location that is read/write for the Windows logon you're running with. By default, the root of C: is read-only for non-admin users (starting with Win2000, as a matter of fact), so it's very likely that the problem is NTFS permissions on the root of C: preventing the write access that is needed here.