Solved

Acess mdb file through Sql Stored Procedure

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

734 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