I use MS Access to a desktop application. Users might not have access or office installed.Is it possible to compact a MS access using a SQL query?
Does after insertions and deletions without compacting the database the size continuously increasing? If yes, when it will exceeds its maximum allowed size, even though it does not have many records, it will deny any further insertions?
You are right that a MSAccess database (like most databases) will increase in size as you add/delete/update its records.
Compacting your Access database on a regular basis is a highly recommended maintenance activity since it will optimise and repair any recent damages without them having a chance to compound and make things worse.
Ideally, you want to do this on a schedule, as part of your datbaase backup strategy for instance (every night is a good idea).
As for your other question: you cannot just issue a SQL command to compact an Access database.
However, you can compact a database from code using the DAO library that is already installed on every Windows machine.
Have a look at the solutions presented in question Access “Compact and Repair” programatically
One word of advice though: do not attempt to compact a shared database on the network unless you are absolutely sure you are the only one accessing it. You could corrupt the database.
At any rate, I would strongly recommend that you first make a copy of the database before doing any compact/repair action on it.
