Create linked server to MS Access database

Question

Create linked server to MS Access database

Viewing 12 posts - 16 through 27 (of 27 total)

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

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

No. All our web pages are connected to SQL Server 2000.

This data is in a machine on the same network , as an access file. I need to get that data to SQL server to see it or use it in reports or anything.

All Engineers are checking the data in SQL server. They use this data with other datas in the SQL server machine to make different reports and draw charts. So they need to see it in SQL server .

thanks

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

What is the authentication that you are using on your local machine?

Windows Authentication . My useris has Administrative rights. My pc is also in the same network as SQL server and Access machine.

How is your local PC registration is done (SQL authentication, Windows authentication)?

I just have the Enterprise Manager tool in my PC.

What is authentication on your SQL server where the stored procedure is located?

Both windows and Sql server authentication

When you created the stored procedure how were you logged in on the server?

I did it through the enterprise manager in my machine . Tried with QA ,logged in with windows authentication , logged in with sa account, logged in with another administrator account but still gives the same error. The file is exclisively open or you don' have permission to view it.

Is the user used on your local PC registration registered as a Login on the SQL server server machine?

Yes. I use the same userid to log in to my pc and SQL server. It's a domain account.

thanks for trying to solve this with me.

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

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

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

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

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?

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

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

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

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

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

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 10

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 11

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 12

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.