Solved

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

Posted on 2011-03-07
9
1,594 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
[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
  • 3
  • 2
  • +1
9 Comments
 
LVL 9

Assisted Solution

by:mayank_joshi
mayank_joshi earned 250 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
Technology Partners: 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 250 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

MongoDB Through a MySQL Lens

This article looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

623 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