Solved

Acess mdb file through Sql Stored Procedure

Posted on 2008-06-18
6
429 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 75 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

747 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now