Linked Server to Access DB Fails Test

Question

Linked Server to Access DB Fails Test

amy26

SSCrazy Eights

Points: 8926
More actions
October 31, 2007 at 1:48 pm

#119157

Was not able to successfully connect to a linked access database I created. The database does not have a password or anything on it. It was on a shared drive and I put the path in \\servername\directory ... I thought maybe it was because the server could not find the path but I moved it around to different locations... I even put it on the c drive of the actual server. I used the following code to create it:
exec sp_addlinkedserver @server='CommDb',
@srvproduct='Access',
@provider='Microsoft.Jet.OLEDB.4.0',
@datasrc='\\everest\markets\ESG_DataMart.mdb'
exec sp_addlinkedsrvlogin @rmtsrvname='CommDb',
@useself='false',
@rmtuser='Admin',
@rmtpassword=''
The error message I get is this:
Cannot initalize the data source object of OLE DB Provider "Microsoft.Jet.OLEDB.4.0" for linked server CommDB.
OLE DB Provider "Microsoft.Jet.OLEDB.4.0" for linked server "CommDb" returned message "Unspecified Error" (Microsoft SQL Server, Error: 7303)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 1

I believe the issue is related to permissions. Please follow the instructions in the below post and see if you can find any success.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=836809&SiteID=1

Thanks

Prasad Bhogadi
www.inforaise.com

amy26 SSCrazy Eights Points: 8926 More actions · Answer 2

I don't think that's what it is as it does the same thing if I put the database on the c drive of the server.

Also it has nothing to do with a webserver.

Key DBA SSCertifiable Points: 6029 More actions · Answer 3

Roxanne,

Based on this MSDN article "Troubleshooting (SQL Server 2000) Error 7303"

http://msdn2.microsoft.com/en-us/library/aa226395(SQL.80).aspx

... it looks like you will have to tweak your sp_addlinkedserver or sp_addlinkedsrvlogin settings.

Try changing your @srvproduct value to the one below:

exec sp_addlinkedserver @server='CommDb',

@srvproduct='OLE DB Provider for Jet',

@provider='Microsoft.Jet.OLEDB.4.0',

@datasrc='\\everest\markets\ESG_DataMart.mdb'

... all of the other @values look good.

Here are my reference notes on Linked Servers to MS Access databases:

-- create linked server syntax

sp_addlinkedserver

-- example

EXECUTE sp_addlinkedserver 'North', 'OLE DB Provider for Jet', 'Microsoft.Jet.OLEDB.4.0','C:\Practice Files\Northwind.mdb'

-- test access

-- issue the following query against the Customers table.

SELECT * FROM North...Customers

If that does not work, another couple of thoughts:

1) What version of MS Access is the ESG_DataMart.mdb?

2) Have you checked the NTFS permissions on the file? You may have to get drastic and grant Full Control to Everyone to ESG_DataMart.mdb.

Hope this helps.

"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

Dan-343268 Grasshopper Points: 15 More actions · Answer 4

Roxanne,

Have you been able to find a resolution to this? I am having the same issue, the odd thing is the timing. I had a perfectly functioning linked server to an Access database that worked for months. Then as of 10/31 it stopped working. The Access DB is fine, I can log in perfectly using MS Access, I just cant hit it in SQL Server 2005 using the linked server.

Please let me know if you have found anything.

Thanks,

Dan

mdoll-543435 SSC Rookie Points: 27 More actions · Answer 5

I am having the same issue as well. I am quite sure I am doing the Link Server script correctly. Something is afoot!!

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 6

Here is my version of adding a linked server, named Test2000 and the Access file is C:\BILL\Test2000.mdb. This code works. I wonder if you're just missing the GO statement after adding the linked server...

sp_addlinkedserver

@server = 'Test2000',

@provider = 'Microsoft.Jet.OLEDB.4.0',

@srvproduct = '',

@datasrc = 'C:\BILL\Test2000.mdb'

GO

sp_addlinkedsrvlogin

@rmtsrvname='Test2000',

@useself='False',

@rmtuser='Admin',

@rmtpassword=''

GO

mdoll-543435 SSC Rookie Points: 27 More actions · Answer 7

I've tried a few things....and now this is what I have when I try to run a Select Statment:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "CUSTSERV" returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "CUSTSERV".

Key DBA SSCertifiable Points: 6029 More actions · Answer 8

mdoll,

can you please run a couple of queries and post the results for us?

-- SQL Server 2000 and 2005

-- Help on the linked server:

1)

EXEC sp_linkedservers

2)

EXEC sp_helpserver

3)

-- SQL Server 2000

select * from sysservers

-- SQL Server 2005

select * from sys.sysservers

Those results should give us an idea of how you have things configured.

Regards,

"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

amy26 SSCrazy Eights Points: 8926 More actions · Answer 9

shouldn't he make his own post instead of hijacking mine? lol

I haven't been able to fix this error either... I had to make a work around by creating a DTS (SSIS) package and connecting to the access database that way.

How is it that the package can connect to it but the linked server cannot?

mdoll-543435 SSC Rookie Points: 27 More actions · Answer 10

I agree with Roxanne

and...sorry...I didn't mean to hijack your post......just trying to help...you know...one world...one spirit.....kumbaya......

amy26 SSCrazy Eights Points: 8926 More actions · Answer 11

amy26

SSCrazy Eights

Points: 8926

November 9, 2007 at 12:52 pm

#749973

Its ok. 🙂