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

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.

Who is Participating?
rboyd56Connect With a Mentor Commented:
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]
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',

Add your own values

The query this way:

select * from [SEATTLE Mktg]...<table name>
ZuZuPetalsAuthor Commented:
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
   'OLE DB Provider for Jet',

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?
Are you using the 64 bit version of SQL Server?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.