by Ross Mack - GUI Computing

I recently had to work on a huge Access database that performed a massive import of around forty-five megabytes of unindexed dBase files. Because of the sheer volume of data - and the time the import took to run - it had only been run once when I took over maintenance. This meant, that after several days of updates to the Access Basic macros and queries, we discovered a design fault. Much of the data manipulation performed during the import relied on tables with counter fields. Spot the obvious problem. When the tables were cleared so that the import could be run again, their counter fields were no longer starting at zero. This caused a synchronisation problem and all the data immediately looked like it had come from another planet.

Now, I told you all that so I could tell you this. The best available solution seemed to be to find some way to reset the counters. Resetting counter fields is fiddly at best. You could create another database and import the structure of the existing database into it. In this case that would have meant importing not only 60-70 tables, but about 100 queries and 50 or so macros. Also, this is not really possible within the scope of an Access macro. What I decided to do was write some Access Basic code to destroy and recreate each table.

This is done in five stages:

1. Interrogate the TableDefs and its fields collection and translate these into a SQL expression to create the table. (`CREATE TABLE. . .')
2. Interrogate the indexes collection of the table and translate each into a SQL expression to create the index. (`CREATE INDEX. . .')
3. Remove each index from the Indexes collection.
4. Remove the table from the TableDefs collection.
5. Execute all the SQL we have generated to create a fresh TableDef and all its Indexes.

Act One Act Two Act Three

[HOME] [TABLE OF CONTENTS] [SEARCH]