Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Acess mdb file through Sql Stored Procedure

Posted on 2008-06-18
6
Medium Priority
?
441 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
[X]
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
  • 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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 Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

704 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