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
Solved

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

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

856 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