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

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
MehramAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Anthony PerkinsConnect With a Mentor Commented:
>>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
 
mayank_joshiConnect With a Mentor Commented:
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
 
mayank_joshiCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
mayank_joshiCommented:
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
 
MehramAuthor Commented:
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
 
lalitgadaCommented:
use this site

connectionstring.com

in this site you get all the connetion type.
0
 
Anthony PerkinsCommented:
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
 
MehramAuthor Commented:
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
 
MehramAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.