Connecting to Access database with database password


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7

Thread: Connecting to Access database with database password

  1. #1
    marxbro Guest

    Connecting to Access database with database password


    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


  2. #2
    Carl Prothman Guest

    Re: Connecting to Access database with database password

    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
    >




  3. #3
    Douglas J. Steele Guest

    Re: Connecting to Access database with database password

    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
    >




  4. #4
    marxbro Guest

    Re: Connecting to Access database with database password


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

    >
    >



  5. #5
    marxbro Guest

    Re: Connecting to Access database with database password


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

    >
    >



  6. #6
    ChrisH Guest

    Re: Connecting to Access database with database password


    "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

  7. #7
    Jeremy Dunck Guest

    Re: Connecting to Access database with database password


    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



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center