Access Cookbook - Ken Getz [105]
Figure 4-18. Sample zstblDeleteOrder
If you'd like a form to control the deletion process, create a new, blank form. Place one command button on the form and modify the command button's Click event handler to call acbClearData:
Private Sub cmdClear_Click( )
Call acbClearData
End Sub
Discussion
The acbClearData function automates the task of selecting the order of your tables and then deleting the data table by table. You select the order when you build the zstblDeleteOrder table. The function works by opening a snapshot of this table and looping through the snapshot one line at a time. The line in the function that does the actual work is:
db.Execute "DELETE * FROM " & rst("TableName")
This line concatenates the table name found in rstTables, using SQL keywords to form a complete SQL statement. For example, if you specify tblFood as one of the tables to delete, Access builds the following SQL statement:
DELETE * FROM tblFood;
This is the SQL equivalent of a delete query that selects all rows from the table and deletes them. The db.Execute statement turns this query over to the Jet engine for execution.
The sample database has a second button, Restock, on the demo form. This button runs a procedure that in turn runs four append queries to take backup copies of the data and return them to the main data tables. This lets you test the function in the sample database more than once.
When you use this technique in your own database, be sure to compact the database before you distribute it to your users. To do this, select Tools → Database Utilities → Compact and Repair Database. There are two reasons to compact your database at this point:
Until you compact, the Access file won't shrink at all. When you delete data from tables, Access marks the data pages as empty, but it doesn't give them back to your hard drive as free space. This occurs only when you compact the database.
When you compact a database, Access resets the next counter values for all incrementing autonumber fields. If you remove all the data from a table with an autonumber in it and compact the database, the next record added will have an autonumber value of 1.
See Also
For more information on using DAO in Access databases, see How Do I Use Data Access Objects (DAO) in New Databases? in the Preface.
4.10. Secure Your Access Database
Problem
You've created an Access database that you'd like to secure. The database contains some sensitive data to which you wish to limit access. You'd like to be able to create different classes of users, so that some users have no access to this data, others can read the data but can't change it, and still others can modify the data. How can you accomplish this?
Solution
The Microsoft Jet database engine, which Access uses to store and retrieve its objects and data, employs a workgroup-based security model that allows you to secure your Access databases, assigning permissions to users and groups. Access supports two mechanisms for securing your database: the database password feature and user-level security. The database password feature is an all-or-nothing proposition—users who know the password aren't restricted in any way once they're in the database. If you want to assign varying permissions to different users, you'll need user-level security. User-level security is fairly complex—it doesn't work if you leave out a step. It consists of creating a new workgroup file (which holds user, group, and password information) and then using this new workgroup file to secure the database. There is a Security Wizard built into Access that will help you secure your database, but you can also manually perform the process, which will help you understand what's happening.
User-level security relies