Information about designing a database

Access Third-Party Developer Tools

Here you will find some Third-Party Developer Tools and Code libraries to help speed up your Access programming.



Allows you to work with multiple versions of MS Access. Detects the version of Access used to create your MDB, MDA or MDE files and either reports the version number or starts the appropriate version of MS Access.


Auto FE Updater

The Auto FE Updater is a drag and drop deployment on the server.   No installation required.  Just download, unzip the files, and place it on the server.  The utility does not require any extra admin privileges to test or for the users to run the utility.  The IT folks in your organization do not need to be involved in your decision to use the Auto FE Updater.


Read more ...

Access User-Level Security

The built-in User-level Security (ULS) is available for databases using the Jet (.mdb) format. It has been dropped in the newer ACE (.accdb) format in Access 2007.

With a JET (.mdb) database, you are always using the User-Level Security. By default, all users use the user admin from the system.mdw workgroup file.

While learning to use the User-Level Security (ULS) be sure to practice on a copy of your database. It is also very important to create a new workgroup file (.mdw). I would avoid modifying the default system.mdw workgroup file that installs with Access. Make sure to keep a backup of your custom workgroup file (.mdw)

Once a database is secured using a custom workgroup file, you must always use this workgroup file whenever you open the database. This means that you must copy the workgroup file (.mdw) with the .mdb/.mde front end.

I found it useful to store the workgroup file with the back end database. I create a shortcut to open the front end using the command line switch for Access to specify the workgroup file in the location with the back end. This way the workgroup file is shared.

I had stopped using the built-in User-Level Security and have developed a custom solution using Access objects/VBA code that works with all versions of Access.

Essentially Microsoft has decided to discourage the use of ULS as explained here:

"Since JET is a file-based database system where users need physical access to the file to operate on their data, the concept of user-level security in Jet to assign different levels of user access to the data within the same file was not recommended. To have multiple people use the database but with different data access privileges, the recommended practice was to move this data to a centralized service like an SQL server or SharePoint lists. However, Jet has had this feature for some time and it has worked OK for usability and custom navigation scenarios but isn’t recommended for actual security."


If you do decide to use the built-in User-Level Security, I would recommend reading:


Whitepaper by Jack MacDonald: How I use Microsoft Access User-Level Security



Microsoft Access Security Apparatus

Additional Links

Access Basics

By Crystal


Access Basics is designed for those of you with a thirst to understand the fundamentals of Access with programming in mind ... whether you realize it or not.
If you are not a programmer, you will become one — because that is where the deepest power of Access gets unleashed.
If you have a logical mind and a desire to learn, all you need is a good foundation
... and here it is.


Summary of Contents for Access Basics
    (click on the Chapter you want to read)

  1. Cover Letter and Types of Applications
    What are the principal applications for computers? and how does Access fit in?
  2. Database Objects
    Learn about the basic Access objects: Tables, Queries, Forms, Reports, Macros, and Modules
    Also explains Properties and Methods, VBA, and Reference Libraries
  3. Normalizing Data
    Now that you understand what a table is ... how do you design them? ... the simple way is not often easy to see! Tips to get you looking at your data from a different perspective.
  4. Relationships, Referential Integrity, Indexes
    How does all the various information relate? What can you do to while you are designing your database to get an overall view?
  5. Form and Report Design
    Now that the basic structure is in place, how do you make it easy for the users to enter data? How do you create reports?
  6. SQL Syntax
    What is SQL? Is it hard to learn? ... no!
  7. Delimiters
    How is text information distinguished from numbers or dates? How do you indicate a field in a table or a control on a form?
  8. Where to Get More Help
    Are there places on the Internet where you can ask questions and folks will help? ...Yes
    Are there good books for learning Access? ...Yes
    Are there web pages you can go to for valuable information? ...Yes
    Are there sites devoted to Access 2007? ...Yes