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

x
?
Solved

From within sql server, to read access mdb which has a password.

Posted on 2011-03-07
9
Medium Priority
?
1,651 Views
Last Modified: 2012-06-27
SQL Server 2005
Access 2002

select *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'\\mateen\pbapps10\Distr\Acc\test_with_password.mdb';
'admin';'',grpmf) a

this works
but now I put a password 'expert' to 'test_with_password.mdb'

select *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'\\mateen\pbapps10\Distr\Acc\test_with_password.mdb';
'admin';'expert',grpmf) a
and this does not work. the error message is

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

I dont want to first unset the access mdb password.
My object/target is to read the outsource access mdb with password.
Is it possible
0
Comment
Question by:Mehram
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 9

Assisted Solution

by:mayank_joshi
mayank_joshi earned 1000 total points
ID: 35066434
If you are trying to open an external file such as a TXT, XLS, CSV,MDB using SQL's OPENROWSET or OPENDATASOURCE functionality and keep getting errors that look like the ones below then here is the fix/workaround.

The Errors:

OLE Database provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE Database provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


The fix:

What you need to do is visit the link below and download the

2007 Office System Driver: Data Connectivity Components download

Once downloaded and installed on the SQL Server you will notice a new driver in the list of drivers on the server at this path:

SERVER\Server Objects\Linked Servers\Providers

The new driver is called:

Microsoft.ACE.OLEDB.12.0

All you then need to do is change your SQL Query to use this new driver rather than the old Microsoft.Jet.OLEDB.4.0 one.

Please follow the steps as given below

1. Execute the following statement in your SQL Server 2008 :

SP_CONFIGURE 'Ad Hoc Distributed Queries'


If It results any Err (or) run_value = 0 then , Do the following steps... Otherwise skip the steps

SP_CONFIGURE 'show advanced option',1

RECONFIGURE

GO

SP_CONFIGURE 'Ad Hoc Distributed Queries',1

RECONFIGURE



2. Try the scripts now

SELECT Column1, Column2

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'C:\Documents and Settings\ smu\Desktop\Website\FileUpload\FIALED_PAPER_LIST_JAN2010_01042010.mdb';

      'admin';'',TABLE_NAME)


Note:
TABLE_NAME should be the name of the Table which is available in the mdb (FIALED_PAPER_LIST_JAN2010_01042010.mdb)

 That should sort you out.
0
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35066553
As an alternative you can try  via linked server:-

EXEC master.dbo.sp_addlinkedserver @server = N'yourmsaccessdb', @srvproduct=N'yourmsaccessdb'', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'C\yourmsaccessdb.mdb'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'yourmsaccessdb',@useself=N'False',@locallogin=NULL,@rmtuser=N'Admin',@rmtpassword='########'

Open in new window

0
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35066656
then:-

SELECT * FROM yourmsaccessdb...yourTableName

Open in new window



Further Refrence:-

http://www.aspfree.com/c/a/Microsoft-Access/Configuring-a-Linked-Microsoft-Access-Server-on-SQL-2005-Server/1/

0
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!

 

Author Comment

by:Mehram
ID: 35066778
Hi mayank_joshi

The second method shows the exact error which i said in the question. Therefore, I would go after your first suggestion.

Thanks for your help
0
 
LVL 1

Expert Comment

by:lalitgada
ID: 35067252
use this site

connectionstring.com

in this site you get all the connetion type.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35069014
And you should know that there is no JET 64-bit driver, so if you are using SQL Server 2005 64-bit you cannot use JET.
0
 

Author Comment

by:Mehram
ID: 35078869
Hi Aceperkins

<<And you should know that there is no JET 64-bit driver, so if you are using SQL Server 2005 64-bit you cannot use JET.>>

Then why in  the world  I am able to read unprotected access database (The following code works)
select *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'\\mateen\pbapps10\Distr\Acc\test_with_password.mdb';
'admin';'',grpmf) a
but unable to read protected access database
the following code same as above with password given does not work
select *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'\\mateen\pbapps10\Distr\Acc\test_with_password.mdb';
'admin';'expert',grpmf) a

The alternate way linked server method also does not work with protected access db and the error is exactly the same as with openrowset method. Is it a bug of SQL 2005


0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1000 total points
ID: 35082990
>>Then why in  the world  <<
That would be because you are not using a 64-bit version of SQL Server 2005.

But to answer your question, I have no idea why you cannot read an "unprotected" MS Access database.
0
 

Author Closing Comment

by:Mehram
ID: 35093486
Thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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

916 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