Solved

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

Posted on 2007-03-20
4
246 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
[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
  • 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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 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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

740 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