linked server for MS Access 2010 database in read only mode

I am trying to create a linked server on SQL server for MS Access database which is shared to me on network i.e. I do not have copy on my machine. While creating a linked server I am facing two issues: 1. How to create a linked server without using mapped drive or using UNC path to that database? 2. How to create that linked server with read only mode as I only need to import the data to SQL server ?

I know this is common question but I have been struggling this for past many days?

Thanks in advance!!
more ▼

asked May 09, 2012 at 09:18 PM in Default

ankurjain512 gravatar image

0 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

If this is a one time import you can simply use the import/export wizard in SQL Server Management Studio.

If you are wanting to do it more than once and create the linked server you are going to have to use a UNC path for SQL Server service to be able to get to the file.

With regards to read only mode I don't believe that is an option with linked server objects. What can be done over that link is determined by the user permissions on either end. With Access you probably could setup an account that can only read the data.
more ▼

answered May 10, 2012 at 01:36 PM

Shawn_Melton gravatar image

5.4k 20 21 29

(comments are locked)
10|1200 characters needed characters left

You can read, view, open mdb file due to special software below http://www.access.fixtoolbox.com

more ▼

answered Jul 21, 2012 at 02:24 PM

nikstone27 gravatar image


(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 09, 2012 at 09:18 PM

Seen: 1416 times

Last Updated: Jul 21, 2012 at 02:24 PM