• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 549
  • Last Modified:

Access Database As Linked Server?

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
Clif
Asked:
Clif
1 Solution
 
Ioannis ParaskevopoulosCommented:
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
 
Dale FyeCommented:
You should be able to do this with the SQL Server Migration Assistant
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
ClifAuthor Commented:
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
 
Dale FyeCommented:
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
 
ClifAuthor Commented:
That is perfect.  It's exactly what I needed.

Thanks.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now