Compact an MS Access Data File

Contents of this Topic: ShowHide

Overview

This topic only applies to Microsoft Access databases. Use the appropriate SQL Management Suite tools to perform maintenance on an SQL database.  

OMS provides a mechanism for the user to easily compact their store file the database used by Monsoon OMS that contains all order, customer, inventory and supplier records related to a given "store"., which is an MS Access database.

Access databases gradually grow in size to accommodate new information that is added. However, MS Access does not automatically shrink the file as information is deleted. This causes the database to function inefficiently.

The Compact Data File function recovers unused bytes left over from deleted information and rebuilds all the indexes in the database (store data file the MS Access or SQL database that contains the users order information and program preferences. It does not contain Reports, which are housed in the Monsoon OMS program file (MS Access database).).

This mechanism only acts on OMS store file and OMTemp databases, not the OMS program file The MS Access database that contains the Monsoon OMS application code. This database does not contain the user's order information, which is housed in the Store Data File. The program file name begins with SEOrdMan* and has the extension of .mdb. (SEOrdMan2007.mdb, etc.). However, the same process can be run manually against a program file. Refer to the Knowledge Base topic, Compact and Repair Database, for more information.

The Compact Data File function is accessible from Main Menu>Settings>Data Functions, or via the Quick Clicks on the Main Menu, if the user has altered the default values of the Quick Clicks.

Compact the Store Data File

1. Before proceeding, make sure all instances of OMS are closed (all workstations).

2. Close any external shipping software that may be accessing the data file, such as UPS WorldShip or FedEx ShipManager.

3. Re-open the OMS on one workstation.

4. As a precaution, use the Backup Data File link in the Quick Clicks menu or Data Functions Menu to make a copy of your data file before beginning the compaction process.

5. When the backup is complete, click the Compact Data File link.

6. Respond [OK] to the confirmation message box to proceed with the compaction process, or click [Cancel] to abort the request before compaction occurs.

7. Make a new backup copy of the newly compacted data file.

Note: It is strongly recommended that an Access database (store file) is compacted on a weekly basis. The process can take anywhere from a few seconds to several minutes or more, depending on the size of the file.

Troubleshooting

• Should you encounter errors while compacting your data file ("MS Access needs to close screen synonymous with the Microsoft Access Form object or graphical user interface (GUI)"), clear the box that offers to repair the database for you and close MS Access. This particular problem could be caused by problems with the store data file (database) or OMS 's program file, which is also an Access database.

• If you suspect problems with the store data file, restore the backup copy of the data file created in step 4 and try again. Otherwise, before restarting OMS , compact and repair the program file and then restart the application and attempt to compact the store data file again. When the process has been successfully completed, make a new backup copy of the data file.

Created: 2/29/12

Revised:

Published: 04/14/16