none
ACCDE or ACCDR file type to deploy a simple Access Application?

    Question

  • Hi,

    I'm developing a simple application based on an integration of Access and Word (Office 2007 and 2010).

    I do not have strong security concerns, and most of the users will be using it on a single computer.

    From what I gathered I could use:

    1. an ACCDE file, so I will have a smaller file size and I could prevent the user to see and modifying my Forms, Reports, Macros, VBA Code. BUT not the tables! That could be even deleted! (Yes I could, remove the Navigation Pane, and not showing them so easily. But is it enough?)
    2. an ACCDR file, so also a user without Access on his computer can use my app. BUT it is enough to change the extension back to ACCDB to see everything!

    How can I have the benefits of both ACCDE and ACCDR?

    How can I prevent the user to enter in Design view in my tables?

    It will be very difficult to split the database and have the data in a folder and the logic in another one on the same computer? I know that doesn't make sense in term of security, but has I said in the application there will not be any "valuable" information, I'm more concerned in accidentally corrumption; and in that way maybe I could address also the few ones that will use the app over a network.

    Thanks for any advise, Lauro

    Sunday, May 13, 2012 8:09 PM

Answers

  • Last Doubts...

    I currently use A2007 for my projects.

    The Disabled Shift Key is the only one that really locks you out. If you were not locked out you could re-enable Special Keys anyway. You can have a hidden control that you set to doubleclick to re-enable Shift Key, but really you should only be using the Shift Key on the copy you will make the .accde distribution from. Step 1 make copy, Step 2 Disable Shift Key, Step 3 Make .accde etc.

    Not sure what you mean with the last one a unique file versus a split db. I always go with the split db. I can not think of an instance where I would want my tables in the same file as the logic.


    Chris Ward

    • Marked as answer by Lauro2 Tuesday, May 15, 2012 9:23 PM
    Monday, May 14, 2012 9:51 PM

All replies

  • I would always deploy as an accde.  Compiled code, smaller file size, lcoked down so people can't mess around...

    I would also split the db regardless of anything else.  It simply makes sense.  You can have both the FE & BE in the same folder, that make no difference, but this way should you need to make any update you can and simply replace the FE without any impact on the data.  On the other hand, if you keep it all together, and have to make any updates, you will have to transfer the data into your new version.  This becomes very difficult to manage and very time consuming (especially when things go wrong, and they will go wrong, Murphy's Law).  By splitting you also reduce chances of database corruption.

    What benefit from a accdr do you need in an accde?  They serve 2 very different purposes!

    "How can I prevent the user to enter in Design view in my tables?" - deploy an accde


    Daniel Pineault, 2010 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Sunday, May 13, 2012 11:07 PM
  • 1) Always build your App in the lowest version of Access your clients will use. If some will use A2007 and others A2010 then build only in A2007 as A2010 is NOT backwards compatible with A2007.

    2) I always distribute with .accde I don't see the benefit of .accdr other than those without access can use the app. My experience is in today's business world everyone has Access.

    3) .accde will prevent modifications to Forms and you can password protect your code and modules in VBA editor so they cannot be modified however you will not see a significant decrease in size. 7mb db after split will in some cases have about 2mb in the backend (BE) but suddenly the frontend (FE) will grow to about 10mb. That's just the nature of the design but overall after a build up of data the FE will always (for the most part) be the same size, while the BE grows.

    4) You should be relatively safe in your BE since your concern is not security. Just disable your Shift Key, and Disable Access Special Keys in the Access Options.

    5) Changing the extension of .accdr to .accdb is not so simple as changing the letters in the file name. Unless you have security issues I would not worry with this.

    6) If you are really worried about your tables you should consider password protecting the BE (Tables) and include the password in the FE but not give it to anyone. There are options about how to use the password as a string to open the BE as well as using code to pass the password.

    7) There isn't a way to stop someone from going into design view in the Tables if they have access to them so permissions to the Table locations should be limited by server permissions and then don't really discuss the location with the users. Only someone you are really worried about should you watch out for and you probably aren't working with that type anyway, right?

    8) When you split the db the logic is the User Interfaces distributed to the users in whichever manor you choose. I place shortcuts to the UI's on the user's computers that link to the UI's on the server and each user has their own interface. I keep the UI's in 1 folder and the BE in another folder as shallow on the server location as possible to prevent lag. Too many folders deep will slow the connection. Never store the BE on a particular computer if your can help it, it should be on a network.


    Chris Ward


    • Edited by KCDW Sunday, May 13, 2012 11:22 PM Correct Spelling
    Sunday, May 13, 2012 11:08 PM
  • In addition to what the others have told you, an ACCDR does not allow users without Access to use your application. If you have users without Access, use the free Access Run-Time.

    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    Monday, May 14, 2012 12:46 AM
  • Thanks to all for your valuable suggestions.

    Well, I think that the best solution for me is the following:

    1. Develop my solution as a normal unique ACCDB file; providing a custom TAB in the Ribbon for open my forms and reports.
    2. Try my solution in Access 2007.
    3. Keep a backup file.
    4. Disable Special Keys and Shift Key.
    5. Split the database in two files: one for logic (Front End) and one for data (Back End).
    6. Compile the FE file as a ACCDE file. Leave the BE as a normal ACCDB file.
    7. Dowload the latest Access Run-Time
    8. Repeat twice the packaging with the wizard, one for BE, one for FE (ACCDE) in the latter case provide the Access Run-time or let the user download it.
    9. Let the user decide to put the BE in the same computer where he will use the FE or on a networked computer.

    Am I correct? Do I miss something important?

    Will I, in this way, accomplish the following goals:

    1. All the users (also the ones who don't have a full Access version installed) will be able to use my application. [I DO NOT need a ACCDR to use the free Access Run-time library]
    2. All my Forms, Reports, Macro, VBA Modules will not be visible or modifiable.
    3. Not having do interact with my tables, the user will not make unintentional changes in them (But if he deliberatly decide to do so, he could still do it).
    4. The user will see the basic Tab/Groups/Controls in the Ribbon [some of them I could even repurpose or disable] plus the one I will put in it.
    5. If I decide to implement a new version with new tools or interface, as long I do not change the structure of the tables,  its enough to develop, compile nd distribute a new FE ACCDE file.
    6. Also the few user that could use my app in a network could benefit from the division between logic and data.

    Last doubts:

    • Should I make the all packaging process in Access 2007?
    • If I decide make some change in my app after I disabled the Special Keys and Shift Key; I cannot open it, so I have to do it in a new backed up file?
    • The logic of developing for one unique file or a splitted one is the same?

    Thank you again also for this long list of questions.

    Lauro
    Monday, May 14, 2012 8:39 PM
  • Last Doubts...

    I currently use A2007 for my projects.

    The Disabled Shift Key is the only one that really locks you out. If you were not locked out you could re-enable Special Keys anyway. You can have a hidden control that you set to doubleclick to re-enable Shift Key, but really you should only be using the Shift Key on the copy you will make the .accde distribution from. Step 1 make copy, Step 2 Disable Shift Key, Step 3 Make .accde etc.

    Not sure what you mean with the last one a unique file versus a split db. I always go with the split db. I can not think of an instance where I would want my tables in the same file as the logic.


    Chris Ward

    • Marked as answer by Lauro2 Tuesday, May 15, 2012 9:23 PM
    Monday, May 14, 2012 9:51 PM
  • Lauro,

    1) Make the ACCDE file

    2) Change the extension from ACCDE to ACCDR.

    3) Distribute the file either as a single file and have the users download the free Access 2010 runtime version or use Inno Installer to create a package that includes the Access runtime. (If you'd like I can share the Inno script I use for this.

    As to your question of splitting the database. If you have multiple users, using the system on different machines and they all need access to the same data, you need to split the database. You can then password protect the database backend (the part with the tables and data) and make the frontend (the part with the VBA code) and ACCDE/R as per above.

    Last, I see that people are telling you to develop in 2007 and not 2010 because not everyone has 2010. I think that if people will be running your application in a runtime environment anyway (no design changes by the enduser), there is no need not to use 2010 and if someone doesn't have it s/he just downloads it for free.  You gain alot of GUI design flexibility.

    /Joe


    • Edited by Model_m Tuesday, May 15, 2012 8:10 AM Added info
    Tuesday, May 15, 2012 8:04 AM
  • Thank you again Chris!

    The last question just reveiled my fear in entering the - for me uncharted - field of "networking programming" or the awesame world of "back End" / "Front End" applications...

    Lauro

    Tuesday, May 15, 2012 9:23 PM
  • Thanks Joe,

    From the others suggestions I gathered that there isn't a benefit for to go from an ACCDE file to an ACCDR file, that I can use the free Access run-time with both the ACCDE or the ACCDR files. Is this right?

    I'm not really sure if someone will use my application on a network, but it seems there are no drowbacks and no difficulties in splitting in two the project, so I think I will do it.

    I didn't really understood what you are saying in point 3). You are telling me that I could give the run-time to the user or telling him to dawnload it. Right?

    I would appreciate, anyway if you want to share with me your Inno script.

    Lauro

    Tuesday, May 15, 2012 9:37 PM