Access Hacks

Hack #55. Test for Duplication

Before you insert multiple entries into master tables in a busy data-entry environment, you'll need a custom validation process to avoid duplicated data.

Just because a database is deployed on the server doesn't mean the entire application must be in that server copy. A common approach is to put the data in the server database and distribute the forms to the local client computers, inside another Access file. This is a typical Access version of a client/server application.

Because the client installations are Access databases, using tables in the client databases opens up possibilities. One useful technique is to have new data entries go into local tables first, and later to bulk-insert them into the master table or tables on the server.

The heart of this technique is that entry operators have local tables that mirror the server tables. Their forms are bound to the local tables and all entry is done locally. At the end of the day, or at scheduled times throughout the day, a process runs that takes the data out of the local tables and moves it to the server tables.

Here is where the advantage lies. The insert process gathers the input from all the entry operators' client tables and tests for duplication before the actual insert into the server tables. Normally, such an interim validation would be overkill because data usually is validated upon entry. However, the point here isn't really to validate data in terms of correct content, but rather, to see ...