Openrowset question

[jadams0173]

I'm trying to use Openrowset to access and A2K table. I've read the BOL on saw the examples that it showed. The question I have is that the BOL example using JET with access showed how to do it using the workgroup file with Admin login and '' password.

My Access DB has a database password when you open it. Is there a way to use openrowset with mdb file that is secured this way? I tried some methods of a connection string like would be used from VB6 but that didn't work.

BOL example:

USE pubs
GO
SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders) 
   AS a
GO

What I've tried:

SELECT top 10 *
FROM 
OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    'R:\Maintenance\DB_BE\stencils_master_be.mdb';
    'Jet OLEDB:Database Password=mypw'

and the error is

Server: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near 'Jet OLEDBatabase Password=mypw'.

 

[bborissov]

You didn't close the last bracket and didn't put an alias for OPENROWSET:

SELECT top 10 *
FROM
OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    'R:\Maintenance\DB_BE\stencils_master_be.mdb';
    'Jet OLEDB:Database Password=mypw'[COLOR=red][b]) AS Test[/b][/color]

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[jadams0173]

Thanks for the reply bborissov.

I did miss the closing paren. and I added the alias as you show above but still get a syntax error.

Line 6: Incorrect syntax near ')'

 

[bborissov]

SELECT top 10 *
FROM
OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    'R:\Maintenance\DB_BE\stencils_master_be.mdb';
    'admin', 'mypw', TableNameHere) AS Test

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[jadams0173]

I tried that to and the syntax was correct but now I get this error:


Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

I don't have a workgroup file because I don't use Access security.

 

[jadams0173]

If I unset the database password the BOL method works. I really don't want to have to do this though. I'm still searching for a solution if it is possible.

 

[cmmrfrds]

You probably need to reference the work group file name in the string.

See connection strings here.

http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForAccess