?
Solved

Access Database As Linked Server?

Posted on 2013-02-01
6
Medium Priority
?
524 Views
Last Modified: 2013-02-01
I'm running SQL Server 2008 SP2
(No idea what version of Access wrote the mdb file, but should be either 2008 or the version just prior)

I have an mdb file (historic data) that I need to read.  Rather than go out and purchase MS Access, I'd like it if I can connect it to SQL Server as a Linked Server.

I used the following code:
EXEC sp_addlinkedserver 
   @server = 'access_test', 
   @provider = 'Microsoft.Jet.OLEDB.4.0', 
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc =  'C:\Users\Public\Documents\xxxxxx.mdb'
GO

Open in new window

Which seemed to connect to the file with no issue.  However when I click on the folder that should display the files, I get the following error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. (Microsoft SQL Server, Error: 7308)

Any ideas?

How do I connect an MS Access database to SQL Server as a Linked Server?
0
Comment
Question by:Clif
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 38844193
Hi,

Try this:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

Open in new window


and have a look at http://blog.sqlauthority.com/2010/11/03/sql-server-fix-error-ms-jet-oledb-4-0-cannot-be-used-for-distributed-queries-because-the-provider-is-used-to-run-in-apartment-mode/

Giannis
0
 
LVL 48

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 38844204
You should be able to do this with the SQL Server Migration Assistant
0
 
LVL 85
ID: 38844222
If you just want to review the data you can use the free Access Runtime:

http://www.microsoft.com/en-us/download/details.aspx?id=10910

Install that on a workstation, then locate the .mdb file and doubleclick it. The runtime should open and allow you to review the data in the tables.

of course, if you need to move data into your server database, then you can do as suggested above, or use SSIS to move the data in.
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 10

Author Comment

by:Clif
ID: 38844231
jyparask,
I ran across that page myself and tried the recommendation.  It did not work.

Unless, of course, I'm supposed to remove the (failed) link, then run the configuration, then re-attach the Access database.

-----
fyed,
SSMA sounds promising, however I cannot seem to find a download link.  Searching around from the link you provided, I found what looked like a download link, but was lead to a Microsoft page called "Learning Center" and a suggestion that I "Get SQL Server 2012".
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 38844253
Try this link.  Most of the links I had were redirected, but finally found this:

http://www.microsoft.com/en-us/download/details.aspx?id=28763
0
 
LVL 10

Author Closing Comment

by:Clif
ID: 38844791
That is perfect.  It's exactly what I needed.

Thanks.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question