Solved

How does a SQL Server stored proc query an Access database table?

Posted on 2007-03-20
4
218 Views
Last Modified: 2012-08-13
I need a Microsoft SQL Server 2005 stored procedure to be able to query a Microsoft Access 2003 table stored in an .mdb file on a Windows Network file share.

Is there anyway to do this?   I Googled it but can't seem to find any answers.

ZuZu
0
Comment
Question by:ZuZuPetals
  • 3
4 Comments
 
LVL 16

Expert Comment

by:rboyd56
ID: 18757115
You can create a linked server to the Access Database and then you a query against the linked server in a stord procedure

EXEC sp_addlinkedserver
   'SEATTLE Mktg',
   'OLE DB Provider for Jet',
   'Microsoft.Jet.OLEDB.4.0',
   'C:\MSOffice\Access\Samples\Northwind.mdb'
GO

Add your own values

The query this way:

select * from [SEATTLE Mktg]...<table name>
0
 
LVL 2

Author Comment

by:ZuZuPetals
ID: 18757328
Wow... that's great--real close!  It almost worked.  When tried it with my stuff I get:

Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.

with these commands:

EXEC sp_addlinkedserver
   'MyDB',
   'OLE DB Provider for Jet',
   'Microsoft.Jet.OLEDB.4.0',
   'E:\Databases\MyDB.mdb'
GO

select * from MyDB...tblOrder where idsOrderID=1227612;

As it turns out, the .mdb file is actually on the same server as the SQL Server--convenient.

I checked the sp_addlinkedserver help and it looks like the provider_name you gave is correct.  

According to our sysadmin, MS Access 2003 is indeed installed on that server.  

Any ideas?
0
 
LVL 16

Expert Comment

by:rboyd56
ID: 18757490
Are you using the 64 bit version of SQL Server?
0
 
LVL 16

Accepted Solution

by:
rboyd56 earned 500 total points
ID: 18757519
If you are then you are probably out of luck. The provider is for the 32 bit version. There is not a Jet driver for the 64 bit version of SQL Server

This is from a Microsoft employee who would know:

Matt Neerincx
 

Posts 450  Answer Re: "Microsoft.Jet.OLEDB.4.0" has not been registered
  Was this post helpful ?      
 
 
 The is no way to use Jet driver from a 64-bit SQL Server unfortunately.  They did not port the driver to 64-bit.  So this would only work from a 32-bit SQL Server.


--------------------------------------------------------------------------------
Matt Neerincx [MSFT]  
 
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=494063&SiteID=1
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now