Re-building an Access Database

[Joe Boatman]

In addition to using Compact to reduce the size of an Access database, all the 'objects' can be imported into a new database with a dramatic reduction in size. Consider doing this having installed a new version of MS Access or after several modifications or when the database gets corrupted. It's not uncommon for a 90 MB accdb file to reduce to 25 MB using this method.

I append the version to my database file names and this means that I keep the previous versions. The attached routine prefixes the file name to each module name so all versions of each module are archived.

I developed the attached code (Modules_Export) to export all the basic and class modules but I've been unable to code for the Form2 object and code. The code requires a reference to Microsoft Scripting Runtime and does the following:

• Exports each module as a text file with the correct extension, eg .bas, .cls
• Prefixes the exported file names with the current database base name
• Creates the backup folder if not exists - routine included
• Calls an included routine to close all forms
• Counts the number of lines of code for each module
• Sorts the module names using included Sort routine

To do this:

1. Unprotect (remove code password) from the source database using Project Properties in the VBE
2. For safety, backup all VBE modules using attached Modules_Export
3. Manually export any Forms 2.0 in the VBE to the backup folder
4. Make a note of the References (VBE Tools menu > References)
5. Close the current database
6. Create a new (Access 2016) database
7. In the EXTERNAL DATA ribbon, click Access in Import & Link section (in New Data Source)
8. Browse to the database to import and select all objects in each tab of Import Objects excluding Menus & Toolbars in Options *
9. In the Access window, set the navigation pane to Object Type

* You can also drag the exported modules into the VBE from the backup folder.

In the VBE, remove OLE Automation and Microsoft ActiveX Data Objects Library (Tools > References) and add References as per the above list

1. Import any Forms 2.0 and save module
2. Check code will compile
3. Up any version number you have in your code
4. Protect the database code with a password in Project Properties
5. Compact the new database