Acess mdb file through Sql Stored Procedure

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
RkVermaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
dbaSQLConnect With a Mentor Commented:
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
 
dbaSQLCommented:
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
 
RkVermaAuthor Commented:
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
 
dbaSQLCommented:
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
 
RkVermaAuthor Commented:
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
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.