Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Acess mdb file through Sql Stored Procedure

Posted on 2008-06-18
6
Medium Priority
?
443 Views
Last Modified: 2008-12-31
Hi Everybody,

I have one stored procedure which gets the records from access table through openrowset via Microsoft.OLEDB.JET.4.0. It is working fine in 32 bit system. But when I deploy this thing on 64bit, its just keep saying that Microsoft OlEDB.Jet.4.0 is not registered which is fine because i came to know Jet Oledb 4.0 does not work on 64bit.
Is there anyway to open the records from access table in the stored procedure through any other provider? Please let me know asap.
Thx
0
Comment
Question by:RkVerma
  • 3
  • 2
6 Comments
 
LVL 17

Expert Comment

by:dbaSQL
ID: 21812512
i may be wrong, but I thought they had actually released a 64-bit oledb for odbc.  
regardless, you could take a look at something like this:  http://www.sswug.org/see/30828
it uses openrowset to retrieve from the mdb via sql procedure
0
 

Author Comment

by:RkVerma
ID: 21818029
do you have anything other link to follow because this link ask for credit card number which i dont want to put? plz help me on this issue asap.

Thx
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 21818422
this is the content of that link:

General Description:

The following stored procedure gets an access mdb file and a table (or view ) name and queries
that table in the access file using OPENROWSET set function FROM sql server environment

The Algorithm:

The procedure sp_QueryMSAccess  gets the access MDB file name (including path)
the path must be accessible to the SQL Server machine (must be in one of the Server's local hard
drives or in a NETwork share accessible to SQL Server logon account

The procedure construnct an OPENROWSET SET function that queries the given table (or view) specifying that mdb file as the data source.
By default the access login is Admin and password is the empty string unless otherwise given


after constructing that T-SQL command the command is executed using DYNAMIC t-sql and printed for
debugging purposes

Here is the T-SQL procedure source code:


use master
go

-- -----------------------------------------------------------------
-- Proc: sp_QueryMSAccess
-- By  : Eli Leiba
-- Date: 10/03/06
-- Desc: Wrap-around procedure that simplifies
-- query from MSAccess
-- -----------------------------------------------------------------
Create Procedure sp_QueryMSAccess
      ( @mdbFile varchar(200),
        @tableOrView varchar(100),
        @usr varchar(20)='Admin',
        @pwd varchar(20)=''    
 )
as
begin
 
   declare @tsqlStmt varchar(600)
   set nocount on

   set @tsqlStmt = 'SELECT t.* FROM OPENROWSET('+ '''' +
  'Microsoft.Jet.OLEDB.4.0' + '''' + ','  +
  '''' + @mdbFile + '''' + ';' + '''' + @usr  + '''' +
                ';' + '''' + @pwd + '''' + ',' + rtrim(ltrim(@tableOrView))
                + ') AS t'

   print (@tsqlStmt)
   exec (@tsqlStmt)

   set nocount off
end
go

Here is an example for usage:

Suppose we have an access file in C:\DataShare called accDB.mdb and we want to query
vCODETAB table
login is Admin and password not needed

do the following:

exec sp_QueryMSAccess @mdbFile = 'c:\DataShare\accDB.mdb', @tableOrView = 'vCODETAB'

THE information will be retrieved and
the statement printed should be :

SELECT t.* FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','c:\DataShare\accDB.mdb';'Admin';'',vCODETAB) AS t
0
 

Author Comment

by:RkVerma
ID: 21827328
Thx for the stored procedure. But i am already using like this. The problem is, you can not use 'Microsoft.Jet.OLEDB.4.0' in 64bit. If you have any other way to access mdb file then please let me know.
0
 
LVL 17

Accepted Solution

by:
dbaSQL earned 300 total points
ID: 21829932
here is another EE post, similar problem, w/suggested workaround:
http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Server/2003_Server/Q_23234170.html
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

972 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