Results 1 to 13 of 13

Importing a password-protected database

  1. #1
    rohde020 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    5

    Importing a password-protected database

    My organization uses databases to gather schedule information from Program Directors. Schedules are created multiple times a year (let's say quarterly). So new Access Databases are created each quarter. Each quarter, Program Directors have the option of entering new schedule information, or importing their information from the previous quarter.



    We have used a Macro with an Import Database command for years. I didn't set it up myself, and my skills are rudimentary at best . The VBA code is DoCmd.RunSavedImportExport ("previousquarter").

    The problem I'm encountering now is that we recently had to start password-protecting the databases. Those sneaky Program Directors were going back into their Databases and making changes after they'd submitted them. We are using the Encrypt with Password feature on the File tab. So now when the Program Director runs their Quarter 2 Import, they're being asked to enter the password on their Quarter 1 database.

    We use the same password for all files across our organization, but we don't want our Program staff to know it, only our Data Processing staff. So I'd like to add code to our Import macro that would automatically enter that password.

    Does anyone have any idea on code I could add to accomplish this?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,246
    Bing: access vba import password protected database

    Review http://www.utteraccess.com/forum/lof.../t1973330.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    rohde020 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    5
    Thanks for the reply! I somehow messed up my notification settings so I didn't see it until now.

    I reviewed the link you shared, as well as the Bing results, but I'm still stuck. The VBA code that is already in the database uses DoCmd.RunSavedImportExport. If I'm reading the link correctly, it seems like that information includes VBA to pull the tables over, rather than using the Saved Import. If possible, I'd rather not have to figure that out and re-write all of the code. I'm wondering if there's any way to have VBA enter the password when the prompt comes up for it.

    OR- it seemed like there was a suggestion to run a code that would open the original database, enter the password, run the Import, and then close the original database. I attempted to this, but was still prompted for the password. Here is what I tried-
    Dim db
    Dim strDbName
    Dim strPassword
    strDbName = "location\filename.mdb"
    strPassword = "mypassword"
    Set acc = CreateObject("Access.Application")
    Set db = acc.DBEngine.OpenDatabase(strDbName, False, False, ";PWD=" & strPassword)
    acc.OpenCurrentDatabase strDbName
    DoCmd.RunSavedImportExport ("MySavedImport")
    db.Close

    As I mentioned initially, I only barely know what I'm doing here, so any further help is appreciated. Thank you!

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,246
    RunSavedImportExpor is not aware of connection set to db variable.

    No, VBA cannot respond to popup prompt.

    Really don't think can avoid code rewrite.

    But I've never had to work with password protected db so my knowledge is limited.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,554
    We use the same password for all files across our organization, but we don't want our Program staff to know it, only our Data Processing staff. So I'd like to add code to our Import macro that would automatically enter that password.
    I suspect it won't take long for your program staff to get to know it.

    Another solution is to use a query which would be something like this

    Code:
    SELECT *
    FROM myTable IN 'C:\...\lastmonth.accdb'[MS Access; PWD=myPassword];
    then change the query recordset type to snapshot. When users run it, they can see the data, they can copy it, but they can't change it.

    Or you can modify this select query to an update or insert query to append the data to existing tables

    But users will still see the password if they open the query in design view

    To protect against this, build the query in vba and run it there instead.

    But again, if your users are using a .accdb front end they can still go to design view and see it in the code.

    To protect against this, provide your users with a .accde instead.

    That still won't stop someone who is determined enough and has a little bit of access knowledge but is probably sufficient for your purposes

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,319
    Another method that I have used is to set a password when linking the tables. If you try to open the be, you get a password prompt. If you allow users to open/see the nav pane in the front end they could stumble on how to expose the password, so it's a weak protection in that situation. A determined person can do all sorts of things to circumvent Access security. I know of one case where an admin used an AutoExec macro to receive an email if anyone opened the be, but he didn't disable the shift bypass property, so guess what?
    The point is, the tougher you want to make it, the more you have to do and you'll probably never achieve 100% security.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.

  7. #7
    rohde020 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    5
    Thanks all!

    I tried to write VBA code to import the tables I need (to replace the Saved Import) but unfortunately couldn't get that code to work either. Was trying to use-

    DoCmd.TransferDatabase acImport,,"Previous location\name",acTable,"Previous table","New table",False

    At this point, we go back into the original databases and remove the encryption when it's time to start working on the next session. It's annoying and repetitive, but it seems like that will continue to be the best solution for now.

    If anyone has any other thoughts or can tell where I went awry, I'd love to hear them. Otherwise, thanks for your help!

  8. #8
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,554
    If anyone has any other thoughts or can tell where I went awry, I'd love to hear them
    without knowing your table design or what you actually tried - your actual code, not air code - not really possible to say where you went awry

  9. #9
    rohde020 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    5
    I don't know what air code means...apologies...

    Here is the whole nightmare code as it is now. The text in RED is what I'm testing with.
    There's a lot to unpack in there, but essentially what it does is when the user clicks the Import button, the existing daily tables are wiped clean, new tables are imported from the selected previous session table, and a query populates the imported tables' data into the existing tables.
    Users are prompted to enter their location with a 2-digit number, hence all of the ELSE IFs. I've been testing with location 14.
    This code is still wiping clean the existing tables, but isn't importing the new table (I've only tried to get one in to start). It is not asking me for a password.





    Private Sub ImportButton_Click()


    On Error GoTo Err_Exit_Command33_Click

    Dim strbranch 'local variable to hold branch number that the user supplied




    Dim tableNumber As Integer 'Int used to reprsent the talbes as integers
    Dim tableDay As Integer 'Int used to loop through possible import numbers
    Dim tableName As String 'String used as the table name
    Dim tableNameTemp As String 'String used as temproary name for dropping tables

    DoCmd.SetWarnings False 'This block of code clears/delete all records in the original table
    DoCmd.RunSQL ("Delete * FROM tbl01Monday")
    DoCmd.RunSQL ("Delete * FROM tbl02Tuesday")
    DoCmd.RunSQL ("Delete * FROM tbl03Wednesday")
    DoCmd.RunSQL ("Delete * FROM tbl04Thursday")
    DoCmd.RunSQL ("Delete * FROM tbl05Friday")
    DoCmd.RunSQL ("Delete * FROM tbl06Saturday")
    DoCmd.RunSQL ("Delete * FROM tbl07Sunday")
    DoCmd.RunSQL ("Delete * FROM tbl08Flex")



    DoCmd.SetWarnings True
    'MsgBox "Tables have been cleared" 'Output - For testing only

    On Error GoTo ErrHandler:
    For tableNumber = 1 To 8 'This nested for loop of code drops all copied tables
    If tableNumber = 1 Then tableName = "tbl01Monday" 'If more "original" tables are added, just incase the table number
    If tableNumber = 2 Then tableName = "tbl02Tuesday"
    If tableNumber = 3 Then tableName = "tbl03Wednesday"
    If tableNumber = 4 Then tableName = "tbl04Thursday"
    If tableNumber = 5 Then tableName = "tbl05Friday"
    If tableNumber = 6 Then tableName = "tbl06Saturday"
    If tableNumber = 7 Then tableName = "tbl07Sunday"
    If tableNumber = 8 Then tableName = "tbl08flex"




    'MsgBox (tableName) 'Output - For testing only

    For tableDay = 1 To 2 'Only up to 2 because there should not be more than
    tableNameTemp = tableName '1 copy beacuse we drop the previous table before creating another one
    If tableDay = 1 Then tableNameTemp = tableNameTemp & tableDay
    If tableDay = 2 Then tableNameTemp = tableNameTemp & tableDay
    'MsgBox (tableNameTemp) 'Output - For testing only
    DoCmd.RunSQL ("Drop Table " & tableNameTemp)
    Next tableDay
    Next tableNumber

    ErrHandler:
    'MsgBox ("Error") 'Output - For testing only
    Resume Next


    'If the user enters an invalid branch number they will have a chance to try again
    Try_Again:
    strbranch = InputBox("Please Enter your two digit branch number: ")
    Application.AutomationSecurity = msoAutomationSecurityLow 'Turn Security notice off
    If strbranch = "32" Then
    DoCmd.RunSavedImportExport ("Import-32_AN Group Training Template")
    GoTo Importing_Copies
    ElseIf strbranch = "14" Then
    DoCmd.TransferDatabase acImport, , "C:\\Users\sclbar1\Desktop\14_BL.mdb", acTable, "tbl01Monday", "tbl01Monday1", False
    GoTo Importing_Copies

    ElseIf strbranch = "17" Then
    DoCmd.RunSavedImportExport ("Import-17_DT Group Training Template")
    GoTo Importing_Copies
    ElseIf strbranch = "76" Then
    DoCmd.RunSavedImportExport ("Import-76_EA Group Training Template")
    GoTo Importing_Copies
    ElseIf strbranch = "27" Then
    DoCmd.RunSavedImportExport ("Import-27_EBH Group Training Template")
    GoTo Importing_Copies
    ElseIf strbranch = "82" Then
    DoCmd.RunSavedImportExport ("Import-82_EG Group Training Template")
    GoTo Importing_Copies
    ElseIf strbranch = "34" Then
    DoCmd.RunSavedImportExport ("Import-34_ER Group Training Template")
    GoTo Importing_Copies
    ElseIf strbranch = "85" Then
    DoCmd.RunSavedImportExport ("Import-85_HA Group Training Template")
    GoTo Importing_Copies
    ElseIf strbranch = "84" Then
    DoCmd.RunSavedImportExport ("Import-84_HU Group Training Template")
    GoTo Importing_Copies
    ElseIf strbranch = "81" Then
    DoCmd.RunSavedImportExport ("Import-81_LL Group Training Template")
    GoTo Importing_Copies
    ElseIf strbranch = "30" Then
    DoCmd.RunSavedImportExport ("Import-30_BU Group Training Template")
    GoTo Importing_Copies
    ElseIf strbranch = "77" Then
    DoCmd.RunSavedImportExport ("Import-77_MW Group Training Template")
    GoTo Importing_Copies
    ElseIf strbranch = "24" Then
    DoCmd.RunSavedImportExport ("Import-24_NH Group Training Template")
    GoTo Importing_Copies
    ElseIf strbranch = "22" Then
    DoCmd.RunSavedImportExport ("Import-22_RD Group Training Template")
    GoTo Importing_Copies
    ElseIf strbranch = "36" Then
    DoCmd.RunSavedImportExport ("Import-36_RV Group Training Template")
    GoTo Importing_Copies
    ElseIf strbranch = "20" Then
    DoCmd.RunSavedImportExport ("Import-20_SD Group Training Template")
    GoTo Importing_Copies
    ElseIf strbranch = "89" Then
    DoCmd.RunSavedImportExport ("Import-89_SH Group Training Template")
    GoTo Importing_Copies
    ElseIf strbranch = "75" Then
    DoCmd.RunSavedImportExport ("Import-75_SK Group Training Template")
    GoTo Importing_Copies
    ElseIf strbranch = "88" Then
    DoCmd.RunSavedImportExport ("Import-88_WBA Group Training Template")
    GoTo Importing_Copies
    ElseIf strbranch = "83" Then
    DoCmd.RunSavedImportExport ("Import-83_WD Group Training Template")
    GoTo Importing_Copies
    ElseIf strbranch = "70" Then
    DoCmd.RunSavedImportExport ("Import-70_WSP Group Training Template")
    GoTo Importing_Copies
    ElseIf strbranch = "16" Then
    DoCmd.RunSavedImportExport ("Import-16_NC Group Training Template")
    GoTo Importing_Copies
    ElseIf strbranch = "38" Then
    DoCmd.RunSavedImportExport ("Import-38_FL Group Training Template")
    GoTo Importing_Copies
    ElseIf strbranch = "79" Then
    DoCmd.RunSavedImportExport ("Import-79_MV Group Training Template")
    GoTo Importing_Copies
    ElseIf strbranch = "87" Then
    DoCmd.RunSavedImportExport ("Import-87_MCC Group Training Template")
    GoTo Importing_Copies
    ElseIf strbranch = "50" Then
    DoCmd.RunSavedImportExport ("Import-50_ROC Group Training Template")
    GoTo Importing_Copies
    Else
    MsgBox "Invalid branch number, please try again."
    GoTo Try_Again


    End If

    Importing_Copies:
    DoCmd.SetWarnings False 'This setting removes all the warning popups
    DoCmd.OpenQuery "qry01MondayImport", acViewNormal, acEdit
    DoCmd.OpenQuery "qry02TuesdayImport", acViewNormal, acEdit
    DoCmd.OpenQuery "qry03WednesdayImport", acViewNormal, acEdit
    DoCmd.OpenQuery "qry04ThursdayImport", acViewNormal, acEdit
    DoCmd.OpenQuery "qry05FridayImport", acViewNormal, acEdit
    DoCmd.OpenQuery "qry06SaturdayImport", acViewNormal, acEdit
    DoCmd.OpenQuery "qry07SundayImport", acViewNormal, acEdit
    DoCmd.OpenQuery "qry08FlexImport", acViewNormal, acEdit
    DoCmd.SetWarnings True 'This setting re-enables all the warning popups
    Application.AutomationSecurity = msoAutomationSecurityByUI 'Turn Security notice back on
    MsgBox "Import Completed. Please verify or update your offerings."




    Exit_Command33_Click:
    Exit Sub


    Err_Exit_Command33_Click:
    MsgBox Err.Description
    Resume Exit_Command33_Click


    End Sub

  10. #10
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,554
    air code means approximate code, typically what is provided as a guidance as to what to do. The recipient is expected to be able to substitute and correct table/field/control names etc. e.g. these are examples of aircode

    Code:
    SELECT *
    FROM sometable
    
    docmd.runsql "myqueryname"
    with regards your else if's, they could be reduced to 3 or 4 lines of code

    the other thing you need to do is to use the code tags when you have more than a few lines of code, this will preserve indenting and make the code easier to read. Going forward if you don't, don't expect much in the way of responses. To use the code tags, after pasting the code, highlight it and click the code button (the # button)

    Code:
    Try_Again:
    strbranch = InputBox("Please Enter your two digit branch number: ")
    Application.AutomationSecurity = msoAutomationSecurityLow 'Turn Security notice off
    If strbranch = "32" Then
        DoCmd.RunSavedImportExport ("Import-32_AN Group Training Template")
        GoTo Importing_Copies
    ElseIf strbranch = "14" Then
        DoCmd.TransferDatabase acImport, , "C:\\Users\sclbar1\Desktop\14_BL.mdb", acTable, "tbl01Monday", "tbl01Monday1", False
        GoTo Importing_Copies
    ElseIf strbranch = "17" Then
    ...
    ...
    Else
        MsgBox "Invalid branch number, please try again."
        GoTo Try_Again
    end if
    suggestion for reducing the code - this is aircode because I haven't completed the whole of the case 32,14... line

    Code:
    Try_Again:
    strbranch = InputBox("Please Enter your two digit branch number: ")
    Select case strBranch
        case 32,14,16,17.....
            Application.AutomationSecurity = msoAutomationSecurityLow 'Turn Security notice off
            DoCmd.RunSavedImportExport ("Import-" & strBranch & "_AN Group Training Template")
            'you don't need the goto importing_copies line
        case else
            MsgBox "Invalid branch number, please try again."
            GoTo Try_Again
    
    end select
    moving now to a solution you can try this - you'll need to supply the password
    Code:
    at the top of your function put
    Dim DB As DAO.Database
    ...
    ...
    ...
    
    Try_Again:
    strbranch = InputBox("Please Enter your two digit branch number: ")
    
    Select case strBranch
        case 32,14,16,17.....
            Application.AutomationSecurity = msoAutomationSecurityLow 'Turn Security notice off
           Set DB = DBEngine.OpenDatabase(Name:="C:\\Users\sclbar1\Desktop\" & strbranch & "_BL.mdb", Options:=False, ReadOnly:=False, Connect:=";PWD=" & myPassword)
            DoCmd.TransferDatabase acImport, "Microsoft Access" , "C:\\Users\sclbar1\Desktop\" & strbranch & "_BL.mdb", acTable, "tbl01Monday", "tbl01Monday1", False
    
        case else
            MsgBox "Invalid branch number, please try again."
           GoTo Try_Again
    
    end select
    or alternatively

    Code:
    at the top of your function put
    Dim sqlStr as string
    ...
    ...
    ...
    
    Try_Again:
    strbranch = InputBox("Please Enter your two digit branch number: ")
    
    Select case strBranch
       case 32,14,16,17.....
           Application.AutomationSecurity = msoAutomationSecurityLow 'Turn Security notice off
           sqlStr="SELECT * INTO tbl01Monday1 FROM tbl01Monday IN  'C:\\Users\sclbar1\Desktop\" & strbranch & "_BL.mdb'[PWD=mypassword]"       
           currentdb.execute sqlstr
       case else
           MsgBox "Invalid branch number, please try again."
          GoTo Try_Again
    
    end select
    if it doesn't work, post back what you have actually used - only needs the relevant section together with the reason why and any error messages

  11. #11
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,554
    to follow on from my previous post - the last example is a make table query, perhaps it should be an insert query in which case use this line instead

    Code:
    sqlStr="INSERT INTO tbl01Monday1 SELECT * FROM tbl01Monday IN  'C:\\Users\sclbar1\Desktop\" & strbranch & "_BL.mdb'[PWD=mypassword]"

  12. #12
    rohde020 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    5
    Thank you so much Ajax! I had been wondering how to make the code look "code-y". I appreciate your directions with the #.

    I think I'm going to need to leave the ELSE IFs in, as the database names include identifying letters in addition to the numbers typed in by the user. But that was a great suggestion to clean this up a little!

    It appears I have now officially hit a brick wall, because I can't get this (pretty basic part?) to work:

    Code:
    Dim DB As DAO.Database
    As I'm typing it, I notice that the DAO doesn't come up as an auto-fill option. I did some research and I looked in the Tools/References section, and I think whatever I need to have checked, I don't. When I tried to check the box for Microsoft DAO 3.6 Object Library, I got an error that read "Error in loading DLL". I suspect this is something that isn't actually loaded on to my work computer.

    I tried using your alternate code suggestion. I was able to put Dim sqlStr As String, and it auto-filled as I expected, then this is what I have in the main section,
    Code:
        ElseIf strbranch = "14" Then
            sqlStr = "SELECT * INTO tbl01Monday1 FROM tbl01Monday IN 'C:\\Users\sclbar1\Desktop\14_BL.mdb'[PWD=mypassword]"
            CurrentDb.Execute sqlStr
    (subbing in our password of course), and it just didn't do anything. I expected a new table, tblMonday1 to appear, and it didn't. If I set up an empty tblMonday1 table, still nothing. Possibly this is also related to the DAO.Database problem?

    I think it is time for me to cut my losses and move on. But thank you so much for helping me through this far- I appreciate it so much!

  13. #13
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,554
    I expected a new table, tblMonday1 to appear
    I would have expected tbl01Monday1 based on the code you provided

    suggest copy the sql

    SELECT * INTO tbl01Monday1 FROM tbl01Monday IN 'C:\\Users\sclbar1\Desktop\14_BL.mdb'[PWD=mypassword]

    into the sql window of a new query (correcting for password) and then run it.

    Any errors generated? does the table appear? if not try to run the query again, it may be the navigation window has not been refreshed. Running it a second time should give a prompt to say something like 'OK to delete the existing table?'

    If that doesn't work change the query to a simple select query by removing 'INTO tbl01Monday1' and run it - do you see the data?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 06-03-2016, 03:58 AM
  2. Replies: 3
    Last Post: 02-10-2016, 07:14 AM
  3. Password Protected Query Database
    By avicknair in forum Queries
    Replies: 9
    Last Post: 10-29-2015, 02:08 PM
  4. Replies: 2
    Last Post: 09-21-2012, 05:12 PM
  5. linking table in a password protected database
    By bdaniel in forum Programming
    Replies: 1
    Last Post: 01-04-2012, 01:25 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums