Create linked server to MS Access database

Question

Create linked server to MS Access database

Viewing 15 posts - 31 through 44 (of 44 total)

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

SQLdba-617800 SSCommitted Points: 1586 More actions · Answer 1

Yes you are right. I created the linked server in the server. If connect to that server using remote console I am able to execute the stored procedure .

My login has server role as System Administrators and I have access to all the databses.

thanks

Glen SSCertifiable Points: 6737 More actions · Answer 2

Try this:

remote connect to the SQL server machine.

Go to the Query analyzer and execute following code:

use databasename

go

grant execute on sprocnamethatyoucreated to public

Exit from remote console. Return back to your local machine. Try excuting your sproc from the local machine again.

SQLdba-617800 SSCommitted Points: 1586 More actions · Answer 3

Actually its not a stored procedure . Its a view to see the data from the linked access file

select * from OPENQUERY([VIMachine], 'Select * from DataFile')

In query analyser If I give select * from viewname , I am able to see the data

thanks

Glen SSCertifiable Points: 6737 More actions · Answer 4

So, in the second statement then use

GRANT ALL ON VIEWNAME TO PUBLIC.

SQLdba-617800 SSCommitted Points: 1586 More actions · Answer 5

Still giving me the same error.

I tested another method . Mapped a drive in the SQL server to the share. And gave E:/SamInfo.mdb as the location .

Again it is working from the server .

But If I run a select command outside, it is giving me E:/SamInfo.mdb is not a valid path.

going :crazy:

thanks

SQLdba-617800 SSCommitted Points: 1586 More actions · Answer 6

I copied the database to a local drive and created the linked server with C:/SamInfo.mdb

It is working fine everywhere .

Any idea?

Glen SSCertifiable Points: 6737 More actions · Answer 7

Glen

SSCertifiable

Points: 6737

September 10, 2008 at 12:45 pm

#869905

Repeat it for E:\Saminfo.mdb

SQLdba-617800 SSCommitted Points: 1586 More actions · Answer 8

I mapped E drive to the share and tried to create the linked server with E:/samInfo.mdb.

It is showing all data if I connect to the SQL server through remote console and execute the select statement. But If run it from my machine , It gives me E:/SamInfo.mdb is not a valid path

thanks

Glen SSCertifiable Points: 6737 More actions · Answer 9

Don't do mapping drives!

Do it in the syntacs I gave you before:

\etworkcomputername\sharenamewitheveryonegrantedrights\filename.mdb

SQLdba-617800 SSCommitted Points: 1586 More actions · Answer 10

Yes I tried with //AMPCS04/VI/SamInfo.mdb

VI is the share name .

Then it is givving me the same error. It is already opened exclusively by another user.

or you need permeission to view its data

Glen SSCertifiable Points: 6737 More actions · Answer 11

Do you have Windows firewall on your local PC?

Can you try simply opening Windows explorer and type there a location of file with \\ networkcomputername\sharename? (and it is a back slash - \, not a forward slash = /)

What do you see?

SQLdba-617800 SSCommitted Points: 1586 More actions · Answer 12

SQLdba-617800

SSCommitted

Points: 1586

September 10, 2008 at 1:12 pm

#869928

I can see all the files under the shared directory.

Glen Potter SSC Veteran Points: 232 More actions · Answer 13

unless there is a different way to cast the path, you need to use a simpler path.

You said it is in "ProgramFiles", but this folder generally has a space "Program Files" which for some reason, SQL Server has issue with locating Linked Server files.

Try a simple root folder like "C:\Acc_Link" and see if it does not connect better for you.

I still have issues with passworded Access dbs, but if I remove the password, a simple path lets me connect okay.

Rajesh Menon-461740 SSC Enthusiast Points: 165 More actions · Answer 14

Hi,

Please let me know if you have got the correct FIX for the issue. I also face the same problem while tryring to create a link table on a remote access database..

thanks

rajesh

yopiet SSC Journeyman Points: 81 More actions · Answer 15

I have experienced the same issue. And I found out that the problems lies on your mdb password. If you are using logon password on your mdb you can use linkedserver and openrowset to do querry.

EXEC sp_addlinkedserver

@server = N'MyLinkServer',

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

@srvproduct = N'OLE DB Provider for Jet',

@datasrc = N'C:\TEMP\TEST.MDB';

select * From OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\TEMP\TEST.MDB';;, tablename)

But if your mdb using database password, you need to change it first to logon password.