Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2006
    Posts
    5

    Unanswered: toubrle connecting to a Password protected MDB

    Hi,

    The FAQ said to be specific so here goes I have two database files, one that holds my forms, queries ect and one that holds my tables. The tables mdb file is password protected.

    I am trying to write SQL insert/delete/update queries using VBA to update my tables. I have select queries working using the following:

    Dim data As Database
    Dim source As String
    Dim record, other As Recordset

    source = "C:\junk\link.MDB"
    Set data = OpenDatabase(source, False, True, "MS Access;PWD=padlock")
    Set record = data.OpenRecordset("SELECT * FROM Table1 where num = 1")
    Text1.Value = record!Name

    record.Close
    data.Close

    This works wonderfully for selects but does nothing for update type queries.

    There is probably a simple solution to this. I guess in a nutshell I would like to know 1) how to correctly connect to a password protected Access 2003 database and 2) the code that might be used to run insert / delete / update queries within that database once it is open. Thank You.

    John.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    You want to do it with recordsets or action queries?

    Recordsets have additional parameters (such as CursorType) - say dbOpenDynaset Which allows you to edit the result set returned ...

    You can Execute queries on your database connection (for action queries) ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Sep 2006
    Posts
    5
    Action queries please

    I have tried a couple of different ways example:

    Dim data as Database
    data.execute "INSERT...."

    but I get "Invalid Password" errors.

    The closest I've come to succeeding ( I think ) was with this bit of code:

    Dim dbs As DAO.Database, sql As String, iCount As Integer
    Set dbs = CurrentDb
    sql = "INSERT INTO Table1(Name, Num) VALUES('bingo', 99)"
    dbs.Execute sql, dbFailOnError
    iCount = dbs.RecordsAffected

    This also throws an "Invalid password" error. If I try adding the OpenDatabase command into the mix, like so:

    Dim dbs As DAO.Database, sql As String, iCount As Integer
    Set dbs = OpenDatabase(source, False, True, "MS Access;PWD=padlock")
    sql = "INSERT INTO Table1(Name, Num) VALUES('bingo', 99)"
    dbs.Execute sql, dbFailOnError
    iCount = dbs.RecordsAffected

    then I get "Runtime Error 3073, Operation must use an updateable query"

    Everything I've read about that error indicates that its a permissions issue, but the database isn't protected in any way other than the password. I hope that clarifies what I'm trying to do. Thanks for the quick response and thanks in advance for any help you can give me on the subject

    John.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    did you check the meaning of that little TRUE you have in there?

    start typing:
    Set dbs = OpenDatabase(source, False
    ...what do you see in the prompt when you add the next character = comma?

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Sep 2006
    Posts
    5
    haha ahhh.. right. You ever stare at a chunk of code so long that you're really not seeing it anymore? Yeah... Thanks so much for the help, problem solved and you both should be elevated to sainthood! readonly = true... my goodness I'm dense...

    John.

  6. #6
    Join Date
    Sep 2006
    Posts
    5
    Alright that solvde that one problem but now I find I have another problem realted to my linked database. So again, briefly this time:

    I have a split access project, tables in one MBD, forms/access queries etc in the other. The tables are password protected and linked to the other file.

    Now I assumed the queries I designed in Access would still work, but they are being stopped by the access password also. How do you include a password so Access queries can connect to that linked table? Thanks in advance for any help.

    John.

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    this shouldn't be a problem - when you first create the link at the FE you are prompted for the password. from then on the FE should see the linked tables without the need for a password.

    did you make the link before adding the password?
    if yes, perhaps try deleting the linked tables from the FE and re-link.

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Sep 2006
    Posts
    5
    Ok I have that figured out now... and am starting to think that I implemented my security incorrectly. When the links are in place, a user using the forms can access the linked database ( since the password is stored in the link ) Is there a way to set security so that a user can access the forms without having access to the database tables without implementing user-level security? I already built my own login / security into the forms themselves ( this is my first time using access and was more comfortable with the code part than Access's naitive security ) I really hope I don't have to redesign the way users log in and i don't want to have two login's. I just want to lock those tables! Thanks again for all your patientence and help, its been great so far

    John.

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    link = no pwd
    your original opendatabase() has pwd but no linked tables
    ho hum.

    can't you just throw people out if they fail to jump your home-made security hurdles at startup? a humane docmd.quit and they're gone! what are they going to do anyway in a database without data?

    be aware that A's pwd is blown away in a second by any one of a dozen freebie tools on the net.

    A's workgroup security is stronger but it annoys me. i suspect that is where you will need to be in the end. i don't use it myself so i can't offer any consolation.

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •