Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Chris033115 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    22

    limiting access to Navigation Panel ane/or securing accdb by converting to accde

    My Access project includes header and line item tables, a data entry form, and a report. Everything is in a single *.accbd rather than having tables split from forms.



    I've set it up to launch from a desktop shortcut in runtime mode so users don't see the Navigation Pane. It's on a standalone PC in a secured area, but anyone in this work zone will use the Access program. Also, Office Professional is installed on the PC and anyone who can log on to the computer could open the database file with the full version of Access from the program menu instead of using the shortcut.

    I need to "secure" the objects in the Navigation Pane but there doesn't appear to be an easy way to go about this. I was hoping there was a way to put a password on it or create "administrator" rights. I'm now considering converting the file to an *.accde which would hide the code but I don't see where this secures objects in the Navigation Pane. Also, I'm unsure how one might edit code if needed in the future.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    I would start by leaving your accdb file alone, for now. Determine which PC will be your development PC. Use this computer to make design changes to your application. On this "Development PC", create a directory to store copies of your work. I usually have folders named garage, archive, master, etc. The idea is to have a place you can work on files, trash old work, know which is the latest "Work In Progress", know which is the previous version, and know which copy is for publication (consumption).


    With all of that in place, create a Front End file. Move all of your forms, queries, modules, macros, and Macros created by the Wizard to a blank DB. Once you have this, you should be able to link your FE file to the original accdb file. Now, your FE file should look just like your original accdb file. The only difference is that the tables within the FE are linked tables.


    You can leave your original accdb file (now your Backend) alone now. Design changes to the tables are the only reason you would touch the original accdb file. All of the other design changes will be made to your FE file. I use a copy of the file from my Master folder. I copy the Master file and place it in the Garage. I work in the Garage. When I am finished in the Garage, I replace the file in the Master with my new version, archiving the other file. This is a simplistic explanation. The additional steps required to secure and publish will require additional folders (space to work in). I use subfolders within the Garage.

    Once you have your DB split and you are confident with which files are Archive, Master, Work In Progress, etc. you can start to address locking the app down. The proper way to use a split DB is to place a copy of the Master on the local workstation of each User's machine. If you use UNC (Universal Naming Convention) to link your tables, it will be super easy to place the copies on the local workstations. I create a directory on the local C drive and a shortcut on their desktop.

    After you have that up and running and you are comfortable with deploying your app, address the security issues. Here is a brief description of how I do it. You will need a little guidance once you get to that point in time.
    https://www.accessforums.net/access/...tml#post273044

  3. #3
    Chris033115 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    22
    I'll study your instructions. The idea of a split database is new to me. It makes sense if multiple users on multiple PCs all need to access and update the same tables... like any multi-user system. My program will never be on more than 1 PC. It will be hooked up to equipment in a lab and whatever technician is on duty will be using it. In this scenario is there a reason to split tables from the FE? Obviously (I think) it would secure the data--but the tables or back end will also have to be on this PC because it isn't connected to the office network. Part of its security is that it's isolated. But it needs protection from the possibility of being tampered with. It's equally important that users can't modify the data entry form or report output or edit macros so the Navigation Pane is still an issue. I'm beginning to think that all the macros need to be rewritten as code. Just as I thought I was done... it seems like I need to revamp everything.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    It does change things that this is a standalone desktop DB. However, the process that I am describing does not, simply, secure the DB with a password. As you mentioned, there is a need to allow users access to forms without allowing access to the tables and design changes.

    I am sure there is a way you could limit access to some users while offering Admin privileges to others with a standalone DB file. Splitting the DB will offer a layer of protection against locking yourself out of the tables. Imagine making a mistake to your code or implementing the lockdown in the incorrect order to the file that contains the data. Splitting the DB simplifies the lockdown process. It also lets you make design changes while others are accessing/working with the DB. If you lock yourself out of the FE file, you have backup copies and there is not any risk to your data.

    Edit: You should be able to import your Macros into the new, blank, DB. Some Macros do not function during Runtime and cause an exception if they do not have access to Design Changes. My preference is to use code to avoid issues. However, it may be possible to get the level of security you desire without publishing your FE file as an executable, leaving the option for Design Changes via code and Macros.

    So, I would not worry too much about revamping the entire DB at this point and simply split the DB to start with.

  5. #5
    Ajax is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,010
    In this scenario is there a reason to split tables from the FE?
    Yes

    1. maintenance: You can edit your master front end whilst users continue to work and simply replace the 'old front end' when ready
    2. users: although potentially having sight of the tables will not be able to change the table structure in any way
    3. corruption: Although more applicable to multiple users accessing the back end at the same time, it is easier to recover from a split db than a non split one if either forms or data become corrupted

  6. #6
    Chris033115 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    22
    Makes complete sense. This was my first time using Access. I started with Macros but in the process was introduced to writing code... and much prefer code at this point. I'll work on splitting the database... Thank you.

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    Quote Originally Posted by Chris033115 View Post
    ... I'll work on splitting the database... Thank you.
    OK, let us know how it is going. BTW, there is not any need to delete forms, queries, etc. from the Backend. Simply, Import what you need to a new DB. You can come back later and "Clean Up" the Backend file. Just be conscious that the BE's forms and queries should bot be used after the split process. They will be "Dated" as soon as you make design changes to the new FE file.

  8. #8
    Chris033115 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    22
    NOTE - I found the Save AS accde rather the the menu selection in Database Tools as described elsewhere. HOWEVER when I try to Save as an accde, it returns an error saying it cannot be done. The expanded message refers to Click image for larger version. 

Name:	Cant make ACCDE.JPG 
Views:	41 
Size:	45.6 KB 
ID:	20466too many open Table IDs. ??? It's linked to a be with 8 tables??

    My database is split and all macros have been rewritten as code. Everything appears to be working fine. I'm at the point where I want to secure it but I'm not seeing any menu options to convert the FE to an accde OR password encrypt the backend. I'm using Access 2013. Also, I've come across other people commenting that an accde created in Access 2013 (however they've done it!) won't work with 2010 Runtime. Not a big issue, I have both 2010 and 2013 runtimes on hand but the 2010 runtime is installed at client site.

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    As a general rule, you want to develop using an older version. If you are using Macros, you will have conflicts if you use a different version to run the app. So, developing with 2007 and running with 2010 can be a problem with some Macros. So it is not always, newer installs will run older, legacy apps. If you are developing for longevity, use VBA.

    As for converting to accde, don't wory about that until the last step. What this will do is remove the VBA from the file. So if there is text within your VBA, converting to accde will make it more difficult for others to see it. You can test your accdb files compatibility with the Runtime environment by change the extension name from accdb to accdr. Changing it back to accdb will not have any adverse affects.

    You will want to encrypt the BE file before you link your FE to the BE. This way, you can store the pass in the FE (using the wizard). If you are extremely concerned about keeping the password secret. You can disguise it and hide it within VBA, using VBA to create connection strings. Then, the accde will provide a little more security. However, if someone figures out how to crack your FE, they can use that as a key to the tables/BE.

    To encrypt the BE file, you will need to open it and click on the File tab from within the Ribbon. I believe this to be the same in 2013. It is called the Backstage. This is where you see things like Options, Info, Save, and so on. For 2010, the Encrypt with Password is under "Info". Make sure you have created a copy and can get in using your pass after you encrypt.

    If you do not see the Encrypt With Password Option, you may need to open the BE in Exclusive mode. To do this, launch the application Access. No DB should be open. Now, use the Open link on the sidebar of the Backstage. Then, navigate to the BE file. After you select the BE file, use the little pulldown in the Open button of the dialog window. There is an option to open in Exclusive mode or Exclusively od something like that.

    If you still have troubles, post up and I will boot up A2013 to see what it looks like.

  10. #10
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    As for post #8 and the accde thing, you need to compile your VBA to the Intermediate language before converting to accde. You do this from the VBA editor/IDE. I would worry about that after encrypting the BE and linking your FE. You will also need to do other last minute touches via the Options and VBA to remove Design Change abilities, prior to conversion to accde.

  11. #11
    Chris033115 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    22
    I'll have to backtrack a little bit and encrypt the BE. I assume I can delete the linked tables in the FE, encrypt the BE, and then re-import and link to the FE.

    I got through creating the accde. I had to compile the vba and fix a couple erroneous entries and then it worked fine. Have tested the main data entry form and report and it worked great, everything looks secure. I have an error accessing other parts of the Main form. It's a 2-tier navigation form. But it's a bug in Access and I found the fix: Create an OnError event for the main navigation form:

    Private
    Sub Form_Error(DataErr AsInteger, Response AsInteger)
    If DataErr =-25357Then
    Response
    = acDataErrContinue
    EndIf
    EndSub

    Thanks so much for all your help!!

  12. #12
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    OK, no problem to click on the table links (icons) and delete them from the Navigation Pane. Then you can link them again using the wizard. Let us know how it goes.

  13. #13
    Chris033115 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    22
    Everything is looking good with one exception. After converting FE to accde the 2nd tab of my navigation form still won't open. I dropped the code in (above) and no longer get an error message, but selecting the 2nd tab does nothing. I've also read where using macros instead of vba code on the form corrects the problem. NOT SO. The only code I had on the from was to hide the toolbar and the error trapping. Removed both, reconverted with no resolution.

    I'm using a 2-tier navigation form with 3 tabs on the top tier and 3 lower tabs on the last tab of the top tier. They are all very similar and include sub-forms with datasheets. All tabs except the 2nd one on the top tier works fine. ALL work fine in the accdb but when converted to accde tab 2 quits.

    The error message is "Requested type library or wizard is not a VBA project."

    Any thoughts??

  14. #14
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    Unfortunately, no. I do not use the Navigation Control because of its idiosyncrasies. Even when you think you have it working in your favor, it does things like you are describing. So I do not have the experience to help you with the Navigation Control.

    The only thing I can suggest is to get the name correct in your VBA. If it is a two tier, you need to address its name (the second tier and or sublevel). Keep in mind, it is a control. You have a form object, form name, navigation control name. There may be another form name for the second tier Navigation Control.

    Because it is a Control, there may be an illusion that the second navigation is tiered. It is more likely the forms are tiered. It may be Form with subform syntax to get at the second Navigation Control.

  15. #15
    Chris033115 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    22
    With all the keystroke shortcuts, views, menus, and toolbar OFF the accdb appears to be locked down pretty good. I'll go with this for now. Thank you SO MUCH for your time and input! I'm going to marked this solved.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 05-12-2014, 04:45 PM
  2. ACCDE to ACCDB
    By breakingme10 in forum Security
    Replies: 2
    Last Post: 01-14-2014, 10:04 AM
  3. ACCDB vs ACCDE
    By data808 in forum Access
    Replies: 8
    Last Post: 01-09-2014, 04:39 PM
  4. Assign converted macro to navigation panel button
    By nhylan in forum Programming
    Replies: 4
    Last Post: 04-17-2013, 01:33 PM
  5. Accdb To Accde Transfer, Access 2007
    By Luu in forum Access
    Replies: 3
    Last Post: 01-19-2013, 12:30 AM

Tags for this Thread

Posting Permissions

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