Is there a way to empty access database and erase all data on it completely as if the database never used before?

I have Microsoft Access database (.mdb) that contains too much data. When i delete the data in it by code or by deleting records, the data is completely deleted. However the database size remains the same开发者_JAVA技巧 (i.e. not decreased) and AutoNumber Fields start from last number saved before deleting records. I ask if there is a way like (empty database in MySql) by which i could have Just the database scheme as if it is created new and never contained data before. Thanks

You want to compact and repair the database.

It'll vary depending on the version of MS-Access you are using, but in 2010 you can find it on the Database Tools ribbon.

Basically, when you delete record from MS-Access (much like a file on a hard drive), it doesn't truly delete it right away. It just marks the record as deleted. When the DB needs to write a new record, it will likely (if the space is sized correctly) try to write over top of one of these areas, rather than increase the size of the database.

Compacting and Repairing the database, amongst other things, removes all of these deleted records from the database.

Which leads to the question. Should you do it regularly? Like defragging your hard drive, not necessarily. Sometimes removing all the blank spaces in the DB and reduce performance (as some time you have records that change size regularly and my no longer fit in the space allotted for them). Also, a compact and repair is not without risk (I have had it take a functioning database and corrupt it beyond repair) - so it's a VERY good idea to take a backup first.

@CodeSlave's answer covers the database size part of your question.

You also asked about the AutoNumber field, how to get it to go back to 1. Allen Browne shows how to loop over each table and reset the AutoNumber field here:

http://allenbrowne.com/ser-40.html