Solved

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

Posted on 2007-03-20
4
230 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

803 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