Lesson 1

When novice developers create an Access database, they generally use few of the software’s powerful functions. When first creating a database for yourself, you probably got by just fine by simply modifying, adding, and deleting information directly in tables by using datasheets. Later you found out how to create queries, forms, and reports, and life got more exciting (and more complicated).
Once you graduate to developing database applications to which several users will share access, you must pay more attention to control and design. You must control who is using the database, what data users can access, and how they can use it. Also, you must design the database well so that it is easy for others to understand and use. Now that you know how to create databases for yourself or a small group, it is time to learn how to share them, perhaps with many users.

Question: How does a Microsoft Access Administrator: 1) control who is using the database, 2) determine which data users can access and how they can use it, 3) Design the database so that it is easy for others to understand and use.
As an Access Administrator, you can control who uses the database by implementing a user management system. Microsoft Access does not provide built-in user management; hence, you'll have to create a workaround to establish the control mechanism.

1. User Login Table: Set up a user table containing usernames, hashed passwords, and other user information. This table will validate users when they log into the database.
2. Login Form: Create a login form that prompts users for their username and password. On submission, the form will verify the credentials against the user table.
3. Authentication Code: Use VBA (Visual Basic for Applications) to check the login credentials. If a user fails authentication, they should not be granted access to the database.
4. Session Management: For added security, you can track user sessions by logging timestamps and user activities. This gives an overview of who is accessing the database and when.

To control the access and manipulation of data by users, you would have to use a combination of Access features and custom VBA scripts.

1. . User Roles: Define user roles in your user table (administrator, standard user, guest, etc.). Each role can have specific access levels to various database components.
2. Form and Report Access: Implement user role checks in your VBA scripts to control the visibility and editability of forms and reports.
3. Table and Query Access: Access doesn't provide a native way to restrict table or query access. However, you can control this indirectly by restricting the forms and reports that interface with these objects.
4. Record-Level Security: Microsoft Access does not support record-level security directly. To achieve this, you'll have to use workarounds like VBA scripts combined with query criteria.

Good database design helps ensure usability and comprehension by end users.

1. Consistent Naming Conventions: Implement clear and consistent naming conventions for tables, queries, forms, and reports. This helps users understand what each object does.
2. Normalization: Normalize your data to eliminate redundancy and maintain data integrity. This also simplifies the data model, making it easier for users to understand the relationships between tables.
3. User-Friendly Forms and Reports: Design forms and reports to be intuitive and easy to use. For instance, group related items together, use clear labels, and provide user guidance where necessary.
4. Documentation: Document your database structure, objects, relationships, and VBA scripts. Good documentation can help users understand the design and purpose of your database.

While Microsoft Access does not offer robust user management or data control mechanisms out-of-the-box, you can create these systems using a combination of tables, forms, VBA scripting, and thoughtful design.

By the end of this module, you will know how to:

1. Set and modify an Access database password
2. Use the Startup dialog options to customize the layout of your database and the functionality available to users
3. Identify seven add-in utilities that increase the functionality of Access
4. Encrypt and decrypt a database and identify the reasons to use encryption
5. Define Replication, identify its purpose, and list the ways it can be done
6. Create a copy of a database by using Replication, then synchronize the copies

These features are not for beginning users to jump into, but for users that want to take advantage of the more powerful features. In the next lesson we will set and modify a database password.