Cleaning a Microsoft Access DB
I have been fortunate enough in my career to mainly use MS SQL Server as my applications' data store.
With all the small spin off products that we are pushing at the moment, we need to make these applications available without the luxury of using SQL Server (or MSDE).
Thus I've been working with Access in the last few days. It hasn't been going too bad, but a few stuff are new to me. I'm not used to the T-SQL capabilities, the field types are different, etc. I'm also working of a factory model for the data access, meaning I can switch to SQL Server if it's available (Postgres too) at runtime. To keep my queries clean, I have a stored procedure framework similar to StuartG's, which I wrote earlier this year for our main project (which needs to be db independent).
The one thing that really bugged me while working with the newly created Access db was that I couldn't find something similar to the truncate table statement in SQL server. This cleans a table without logging the changes as it does with a delete statement. It also starts the identity field (Autonumber in Access) from the start again.
After creating my DB in access I started running my application on it and populating the db with data. This in turn caused the files size to increase. I want to start packaging the product now, so that means that I want to clean up the db, and restart all the tables' Autonumber values.
I tried several actions like deleting the autonumber field on the tables and recreating it, copying th table (and specifying the structure only option).
In the end, the only option I found that restarted all the tables' Autonumber fields and shrinked the size of the mdb file, was the following:
- Create a new access file
- In the newly created file, click on File -> Get external data -> Import
- Browse to the existing mdb file and click on import
- Select all the tables that you wish to transfer
- Under the options tab - ensure that the definition only option is selected
- Click on OK