Connecting to Access database with database password

[marxbro]

I have been trying to connect to an Access database that has a database password,
according to instructions I have been able to find on the internet and in
"Access 97 Developer's Handbook". I have not been successful.
The DAO code suggested is:

dim work as Workspace
dim db as Database
dim rs as Recordset
dim strSQL as String

Set wrk = DBEngine.Workspaces(0)
Set db = wrk.OpenDatabase("c:\path\database.mdb:,False,False,_
";PWD=password")
strSQL = "select......from table..."
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

When I open the application and point it at my desired database/recordset
I get an error message: Run-time error '13': Type Mismatch for the set rs
= db.....statement.

Any suggestions? What am I doing wrong? Alternatively (preferrably?) is there
ADO coding I could use to open and utilize the password protected Access
database?

I have tried this with a DSN-less connection:

Dim m_cnn As ADODB.Connection
Dim m_rst as ADODB.Recordset

Set m_cnn = New ADODB.Connection
m_cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\path\_ database.mdb",
, "password" [I have also tried listing Jet 3.51]
Set m_rst = New ADODB.Recordset
m_rst.Open "SELECT * FROM AnyTable", m_cnn, _
adOpenKeyset, adLockOptimistic

But I think this is for opening an Access database with Workgroup-based security,
rather than opening past a simple database password, and I get the following
error message with the connection .Open command:

Run-time error '-2147217843(80040e4d)': Cannot start your application. The
workgroup information file is missing or opened exclusively by another user.


I have tried the above code as written, with "admin", and with "" for the
User ID as I don't think I have any User ID set up, and got the same error
message.

Any other suggestions?

Thanks for any help. marxbro

[Carl Prothman]

For the OLE DB Provider for Microsoft Jet add the following tag to your
connection string
"Jet OLEDBatabase Password=MyDbPassword;"

e.g.
http://www.able-consulting.com/ADO_C...orMicrosoftJet

--

Thanks,
Carl Prothman
Microsoft Visual Basic MVP
http://www.able-consulting.com



"marxbro" <marxbro@about.com> wrote in message
news:3995de6f$1@news.devx.com...
>
> I have been trying to connect to an Access database that has a database
password,
> according to instructions I have been able to find on the internet and in
> "Access 97 Developer's Handbook". I have not been successful.
> The DAO code suggested is:
>
> dim work as Workspace
> dim db as Database
> dim rs as Recordset
> dim strSQL as String
>
> Set wrk = DBEngine.Workspaces(0)
> Set db = wrk.OpenDatabase("c:\path\database.mdb:,False,False,_
> ";PWD=password")
> strSQL = "select......from table..."
> Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
>
> When I open the application and point it at my desired database/recordset
> I get an error message: Run-time error '13': Type Mismatch for the set rs
> = db.....statement.
>
> Any suggestions? What am I doing wrong? Alternatively (preferrably?) is
there
> ADO coding I could use to open and utilize the password protected Access
> database?
>
> I have tried this with a DSN-less connection:
>
> Dim m_cnn As ADODB.Connection
> Dim m_rst as ADODB.Recordset
>
> Set m_cnn = New ADODB.Connection
> m_cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\path\_
database.mdb",
> , "password" [I have also tried listing Jet 3.51]
> Set m_rst = New ADODB.Recordset
> m_rst.Open "SELECT * FROM AnyTable", m_cnn, _
> adOpenKeyset, adLockOptimistic
>
> But I think this is for opening an Access database with Workgroup-based
security,
> rather than opening past a simple database password, and I get the
following
> error message with the connection .Open command:
>
> Run-time error '-2147217843(80040e4d)': Cannot start your application. The
> workgroup information file is missing or opened exclusively by another
user.
>
>
> I have tried the above code as written, with "admin", and with "" for the
> User ID as I don't think I have any User ID set up, and got the same error
> message.
>
> Any other suggestions?
>
> Thanks for any help. marxbro
>

[Douglas J. Steele]

Carl's already shown you how to handle the ADO piece.

For DAO, I have to assume that you're working in Access 2000 (or why else
would you worry about ADO?)

ADO and DAO both have Recordset as an object in their respective models.
Obviously, you've added a reference to DAO in your application (or the use
of the Workspace and Database objects would have caused errors), but it must
be lower in the list of references than ADO, and Access is taking the first
reference to Recordset it finds.

To "disambigulate" your use of Recordset, use:

Dim rs As DAO.Recordset
or
Dim rs As ADODB.Recordset

HTH

--

Doug Steele, Microsoft Access MVP
Beer, Wine and Database Programming. What could be better?
Visit "Doug Steele's Beer and Programming Emporium"
http://I.Am/DougSteele/


marxbro <marxbro@about.com> wrote in message
news:3995de6f$1@news.devx.com...
>
> I have been trying to connect to an Access database that has a database
password,
> according to instructions I have been able to find on the internet and in
> "Access 97 Developer's Handbook". I have not been successful.
> The DAO code suggested is:
>
> dim work as Workspace
> dim db as Database
> dim rs as Recordset
> dim strSQL as String
>
> Set wrk = DBEngine.Workspaces(0)
> Set db = wrk.OpenDatabase("c:\path\database.mdb:,False,False,_
> ";PWD=password")
> strSQL = "select......from table..."
> Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
>
> When I open the application and point it at my desired database/recordset
> I get an error message: Run-time error '13': Type Mismatch for the set rs
> = db.....statement.
>
> Any suggestions? What am I doing wrong? Alternatively (preferrably?) is
there
> ADO coding I could use to open and utilize the password protected Access
> database?
>
> I have tried this with a DSN-less connection:
>
> Dim m_cnn As ADODB.Connection
> Dim m_rst as ADODB.Recordset
>
> Set m_cnn = New ADODB.Connection
> m_cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\path\_
database.mdb",
> , "password" [I have also tried listing Jet 3.51]
> Set m_rst = New ADODB.Recordset
> m_rst.Open "SELECT * FROM AnyTable", m_cnn, _
> adOpenKeyset, adLockOptimistic
>
> But I think this is for opening an Access database with Workgroup-based
security,
> rather than opening past a simple database password, and I get the
following
> error message with the connection .Open command:
>
> Run-time error '-2147217843(80040e4d)': Cannot start your application. The
> workgroup information file is missing or opened exclusively by another
user.
>
>
> I have tried the above code as written, with "admin", and with "" for the
> User ID as I don't think I have any User ID set up, and got the same error
> message.
>
> Any other suggestions?
>
> Thanks for any help. marxbro
>

[marxbro]

Thank you, thank you, thank you.


"Carl Prothman" <carlpr@spamcop.net> wrote:
>For the OLE DB Provider for Microsoft Jet add the following tag to your
>connection string
>"Jet OLEDBatabase Password=MyDbPassword;"
>
>e.g.
>http://www.able-consulting.com/ADO_C...orMicrosoftJet
>
>--
>
>Thanks,
>Carl Prothman
>Microsoft Visual Basic MVP
>http://www.able-consulting.com
>
>
>
>"marxbro" <marxbro@about.com> wrote in message
>news:3995de6f$1@news.devx.com...
>>
>> I have been trying to connect to an Access database that has a database
>password,
>> according to instructions I have been able to find on the internet and
in
>> "Access 97 Developer's Handbook". I have not been successful.
>> The DAO code suggested is:
>>
>> dim work as Workspace
>> dim db as Database
>> dim rs as Recordset
>> dim strSQL as String
>>
>> Set wrk = DBEngine.Workspaces(0)
>> Set db = wrk.OpenDatabase("c:\path\database.mdb:,False,False,_
>> ";PWD=password")
>> strSQL = "select......from table..."
>> Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
>>
>> When I open the application and point it at my desired database/recordset
>> I get an error message: Run-time error '13': Type Mismatch for the set
rs
>> = db.....statement.
>>
>> Any suggestions? What am I doing wrong? Alternatively (preferrably?) is
>there
>> ADO coding I could use to open and utilize the password protected Access
>> database?
>>
>> I have tried this with a DSN-less connection:
>>
>> Dim m_cnn As ADODB.Connection
>> Dim m_rst as ADODB.Recordset
>>
>> Set m_cnn = New ADODB.Connection
>> m_cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\path\_
>database.mdb",
>> , "password" [I have also tried listing Jet 3.51]
>> Set m_rst = New ADODB.Recordset
>> m_rst.Open "SELECT * FROM AnyTable", m_cnn, _
>> adOpenKeyset, adLockOptimistic
>>
>> But I think this is for opening an Access database with Workgroup-based
>security,
>> rather than opening past a simple database password, and I get the
>following
>> error message with the connection .Open command:
>>
>> Run-time error '-2147217843(80040e4d)': Cannot start your application.
The
>> workgroup information file is missing or opened exclusively by another
>user.
>>
>>
>> I have tried the above code as written, with "admin", and with "" for
the
>> User ID as I don't think I have any User ID set up, and got the same error
>> message.
>>
>> Any other suggestions?
>>
>> Thanks for any help. marxbro
>>
>
>

[marxbro]

Thanks for your response. I went with the post Carl sent, and it did what
I needed. I will also try your suggestions later to see where that gets me.


"Douglas J. Steele" <djsteele@idirect.com> wrote:
>Carl's already shown you how to handle the ADO piece.
>
>For DAO, I have to assume that you're working in Access 2000 (or why else
>would you worry about ADO?)
>
>ADO and DAO both have Recordset as an object in their respective models.
>Obviously, you've added a reference to DAO in your application (or the use
>of the Workspace and Database objects would have caused errors), but it
must
>be lower in the list of references than ADO, and Access is taking the first
>reference to Recordset it finds.
>
>To "disambigulate" your use of Recordset, use:
>
> Dim rs As DAO.Recordset
>or
> Dim rs As ADODB.Recordset
>
>HTH
>
>--
>
>Doug Steele, Microsoft Access MVP
>Beer, Wine and Database Programming. What could be better?
>Visit "Doug Steele's Beer and Programming Emporium"
>http://I.Am/DougSteele/
>
>
>marxbro <marxbro@about.com> wrote in message
>news:3995de6f$1@news.devx.com...
>>
>> I have been trying to connect to an Access database that has a database
>password,
>> according to instructions I have been able to find on the internet and
in
>> "Access 97 Developer's Handbook". I have not been successful.
>> The DAO code suggested is:
>>
>> dim work as Workspace
>> dim db as Database
>> dim rs as Recordset
>> dim strSQL as String
>>
>> Set wrk = DBEngine.Workspaces(0)
>> Set db = wrk.OpenDatabase("c:\path\database.mdb:,False,False,_
>> ";PWD=password")
>> strSQL = "select......from table..."
>> Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
>>
>> When I open the application and point it at my desired database/recordset
>> I get an error message: Run-time error '13': Type Mismatch for the set
rs
>> = db.....statement.
>>
>> Any suggestions? What am I doing wrong? Alternatively (preferrably?) is
>there
>> ADO coding I could use to open and utilize the password protected Access
>> database?
>>
>> I have tried this with a DSN-less connection:
>>
>> Dim m_cnn As ADODB.Connection
>> Dim m_rst as ADODB.Recordset
>>
>> Set m_cnn = New ADODB.Connection
>> m_cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\path\_
>database.mdb",
>> , "password" [I have also tried listing Jet 3.51]
>> Set m_rst = New ADODB.Recordset
>> m_rst.Open "SELECT * FROM AnyTable", m_cnn, _
>> adOpenKeyset, adLockOptimistic
>>
>> But I think this is for opening an Access database with Workgroup-based
>security,
>> rather than opening past a simple database password, and I get the
>following
>> error message with the connection .Open command:
>>
>> Run-time error '-2147217843(80040e4d)': Cannot start your application.
The
>> workgroup information file is missing or opened exclusively by another
>user.
>>
>>
>> I have tried the above code as written, with "admin", and with "" for
the
>> User ID as I don't think I have any User ID set up, and got the same error
>> message.
>>
>> Any other suggestions?
>>
>> Thanks for any help. marxbro
>>
>
>

[ChrisH]

"marxbro" <marxbro@about.com> wrote:
>
>I have been trying to connect to an Access database that has a database
password,
>according to instructions I have been able to find on the internet and in
>"Access 97 Developer's Handbook". I have not been successful.
>The DAO code suggested is:
>
>dim work as Workspace
>dim db as Database
>dim rs as Recordset
>dim strSQL as String
>
>Set wrk = DBEngine.Workspaces(0)
>Set db = wrk.OpenDatabase("c:\path\database.mdb:,False,False,_
>";PWD=password")
>strSQL = "select......from table..."
>Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
>
>When I open the application and point it at my desired database/recordset
>I get an error message: Run-time error '13': Type Mismatch for the set rs
>= db.....statement.
>
>Any suggestions? What am I doing wrong? Alternatively (preferrably?) is
there
>ADO coding I could use to open and utilize the password protected Access
>database?
>
>I have tried this with a DSN-less connection:
>
>Dim m_cnn As ADODB.Connection
>Dim m_rst as ADODB.Recordset
>
>Set m_cnn = New ADODB.Connection
>m_cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\path\_ database.mdb",
>, "password" [I have also tried listing Jet 3.51]
>Set m_rst = New ADODB.Recordset
>m_rst.Open "SELECT * FROM AnyTable", m_cnn, _
>adOpenKeyset, adLockOptimistic
>
>But I think this is for opening an Access database with Workgroup-based
security,
>rather than opening past a simple database password, and I get the following
>error message with the connection .Open command:
>
>Run-time error '-2147217843(80040e4d)': Cannot start your application. The
>workgroup information file is missing or opened exclusively by another user.
>
>
>I have tried the above code as written, with "admin", and with "" for the
>User ID as I don't think I have any User ID set up, and got the same error
>message.
>
>Any other suggestions?
>
>Thanks for any help. marxbro
>
I had a hard time with this one too when I first started to try and use ADO.
The DSN way was the easiest, but I did not want to have to worry about configuring
a dsn on a user machine for an access database that would really just be
acting as a data file. There is more than one way to do this. One way using
the ADO Jet provider and one using the default provider. There is an article
on MSDN about this; I have tried it and it does work.

http://msdn.microsoft.com/library/te...ate_topic4.htm

for a list of links to the dao/jet to ado migration the link is

http://msdn.microsoft.com/library/te...oadoupdate.htm

hope this helps

[Jeremy Dunck]

If you want to use DAO, then the only thing you need to change is your Dim
statement.
You see, whenever you Dim an object you're really supposed to supply the
library to use. VB is (too?) forgiving, though, and allows you to Dim objects
without a specified library.
At compile time, it (tries) to resolve these omitted libraries by searching
through all referenced (and/or registered) libraries, and picks the 1st library
it finds with a class named what you Dimed.
So, back in the olden days, when DAO was the 1st library that VB ran across
"Dim x As Recordset" was interpreted as "Dim x as DAO.Recordset".
However, now that ADO is on the scene, and implements a Recordset class,
VB interprets "Dim x As Recordset" as "Dim x As ADO.Recordset".
So you're mistakenly Dim'ing an ADO recordset. And that's why you're getting
the type mismatch error. This is a very common problem, which bit me once.
Now I spread the word on it .

-Jeremy

"ChrisH" <bartelby_ix@yahoo.com> wrote:
>
>"marxbro" <marxbro@about.com> wrote:
>>
>>I have been trying to connect to an Access database that has a database
>password,
>>according to instructions I have been able to find on the internet and
in
>>"Access 97 Developer's Handbook". I have not been successful.
>>The DAO code suggested is:
>>
>>dim work as Workspace
>>dim db as Database
>>dim rs as Recordset
>>dim strSQL as String
>>
>>Set wrk = DBEngine.Workspaces(0)
>>Set db = wrk.OpenDatabase("c:\path\database.mdb:,False,False,_
>>";PWD=password")
>>strSQL = "select......from table..."
>>Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
>>
>>When I open the application and point it at my desired database/recordset
>>I get an error message: Run-time error '13': Type Mismatch for the set
rs
>>= db.....statement.
>>
>>Any suggestions? What am I doing wrong? Alternatively (preferrably?) is
>there
>>ADO coding I could use to open and utilize the password protected Access
>>database?
>>
>>I have tried this with a DSN-less connection:
>>
>>Dim m_cnn As ADODB.Connection
>>Dim m_rst as ADODB.Recordset
>>
>>Set m_cnn = New ADODB.Connection
>>m_cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\path\_ database.mdb",
>>, "password" [I have also tried listing Jet 3.51]
>>Set m_rst = New ADODB.Recordset
>>m_rst.Open "SELECT * FROM AnyTable", m_cnn, _
>>adOpenKeyset, adLockOptimistic
>>
>>But I think this is for opening an Access database with Workgroup-based
>security,
>>rather than opening past a simple database password, and I get the following
>>error message with the connection .Open command:
>>
>>Run-time error '-2147217843(80040e4d)': Cannot start your application.
The
>>workgroup information file is missing or opened exclusively by another
user.
>>
>>
>>I have tried the above code as written, with "admin", and with "" for the
>>User ID as I don't think I have any User ID set up, and got the same error
>>message.
>>
>>Any other suggestions?
>>
>>Thanks for any help. marxbro
>>
>I had a hard time with this one too when I first started to try and use
ADO.
> The DSN way was the easiest, but I did not want to have to worry about
configuring
>a dsn on a user machine for an access database that would really just be
>acting as a data file. There is more than one way to do this. One way
using
>the ADO Jet provider and one using the default provider. There is an article
>on MSDN about this; I have tried it and it does work.
>
>http://msdn.microsoft.com/library/te...ate_topic4.htm
>
>for a list of links to the dao/jet to ado migration the link is
>
>http://msdn.microsoft.com/library/te...oadoupdate.htm
>
>hope this helps