Access Cookbook - Ken Getz [209]
You could extend this technique to capture additional detail about the records being added, updated, or deleted. You might even add extra fields to the logging table to capture the actual data instead of just the primary key that identifies the changed record. This allows you to completely reconstruct the table at any point in time by inspecting the log file and making or removing changes. The drawback to enabling this capability is that it requires substantially more storage space, since you'll be storing a full copy of the data every time any part of it changes.
If you wish to log a table with a compound primary key, just replace the last parameter when calling the acbLog functions with a concatenation of each field that makes up the primary key. For example, to log an addition to the tblOrderDetail table with a primary key made up of OrderId and OrderItem, you would use the following function call in the AfterInsert event property:
=acbLogAdd("tblOrderDetail", [OrderId] & "; " & [OrderItem])
acbLog opens a recordset on the logging table with the dbAppendOnly argument. This returns an initially blank recordset ready to receive new records instead of a full dynaset whose existing records can be edited. This gives you a performance boost when you are only adding new records and do not need to pull in existing records.
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.
10.4. Send Messages to Other Users Without Using Email
Problem
When you have multiple users logged into your application, you want them to be able to communicate quickly and easily with one another. You need a simple interface for sending notes back and forth so users can check whether anyone else is editing a particular entry, compare notes on workflow, and so on. How can you implement this in Access?
Solution
You can keep your notes in a table in a shared database to which all users have access. Whenever someone writes a note to another user, that note is added as another record in this table. By using a form that makes use of the Timer event, you can monitor the status of this table from any Access application and notify users when new messages have arrived.
This solution employs two files, 10-04fe.MDB and 10-04be.MDB. Before you can try it, you'll need to link the data tables from 10-04be.MDB (the "backend" or data database) to 10-04fe.MDB (the "frontend" or application database). Linking a data table allows you to use a table from one Access database within another Access database. Start Access and load 10-04fe.MDB. Choose File → Get External Data Link Tables, and select 10-04be.MDB as the Access link database. At the Link Tables dialog, select tblMessage and click OK, as shown in Figure 10-16.
Figure 10-16. Linking a data table
Now you can test-drive this solution by sending a message to yourself. Open both frmSendMail and frmReceiveMail. Minimize the Receive Mail form. Select your username from the To combo box. If you haven't altered the default Access security settings, your username will be Admin, which should be confirmed in the From text box. Enter any message and click the Send Message button. In Figure 10-17, Peter has used frmSendMail to compose a message to Jean.
TIP
In order to send messages between multiple users, you'll need to set up a workgroup that contains the users, and have each user log in as him or herself. See Section 10.1 for more information on setting up a workgroup.
Figure 10-17. Using frmSendMail to send a message
The Send Mail form will clear as soon as the message is sent. Within 10 seconds, the Receive Mail form will pop up with the message. Figure 10-18 shows how Jean would see the message from Peter. Click on the Mark as Read button to clear the Receive Mail form. If more than one message is waiting, you can navigate through them.
Figure 10-18. Using frmReceiveMail to receive a message