MS Access Split utility

Overview

Access Split is a utility that breaks a single .mdb file into multiple .xml files and then re-construct the database back from those files. It was originally created as a substitute for Microsoft Visual Source Safe control for Access "VSS", and then began its own life.

The major VSS limitation is combining most of the database information into one large binary object. Therefore any time changes are made, such as modifying a Caption property on a table field, creating or modifying an index, changing your import/export specification, etc. - "VSS" only provides notification that the binary files differ, and not what the actual change is.

Access Split attempts to overcome this limitation by listing the majority of the database objects and their properties in easy-to-read .xml files. Those files in turn can be put into source control software of your choice. This allows the database developers greater traceability of the source changes.

The main purpose of making it available at this stage is to gauge public interest in the utility and any functions that need to be added, improved or unnecessary.

Getting Started

Installing Access Split is easy. You can dowload installation files zipped in AS_Setup.zip, unzip them to a temporary folder.

Then run Setup.exe file and after a few clicks the installation is complete (it may ask to install .Net framework, if not installed already). You will see a new icon added to your desktop and start menu.

(You can download executables only here.)

Running the program will bring up the following screen:

As any .Net application the utility will take its time to load for the first time.

Click on "Create Source" button to get source files from the database and on "Create Database" button to assemble the database from source.

The two screens - "Create Source" and "Create Database" are very similar in look and feel. All you need to do is select the source and destination for your process, choose the objects you want (or select "All objects" checkbox) and click on "Process" button.

Processing errors are logged into Errors.log file located in the application directory. If the error is critical, then execution will stop immediately and a message indicating the error will be shown. Otherwise you will see "Finished with errors" message at the end of processing.

You can also cancel processing if you want to change conditions.

Creating Source Files

First select the database file you want to process. You can either type in the full path or use Open Database dialog (click on   button). Note: selecting a valid .mdb file enables "Retrieve objects" button.

Then select a source database (output) folder. You can check the contents of the folder by clicking on "Open source folder" button.

Now select the objects you want to create source files for and click "Process".
Please exercise with caution the use of "Include data for local tables" checkbox: large tables (more than 1000 records) may take a while to process. In most cases you will not want to store the data from your temporary tables either. But relatively small reference tables seem like perfect candidates for this function.

Depending on the size and complexity of your database, it can take from a few seconds to a few minutes to process a database file. See Technical Details for more information.

Access Split organizes .xml files into multiple directories (similar to tabs in the database window) that sit beneath the database directory.

The .xml files will have the same name as the corresponding database objects. The exceptions to this rule are as follows:

• ObjectList.xml will list database objects
• *_tableData.xml files will list local table's data
• *_module.xml files in Forms and Reports directories will appear for those objects that have their HasModule property set to True.

Creating Database

First select the database source folder

Then select the folder where the database will be created (or updated). Note that the database name is stored in Database.xml in your source folder.

If you already have the database file with the same name in the target directory, you will be asked whether you want to update the existing database or create a new one. You will normally use the former to update individual objects and the latter to process "All objects".

Please note that re-creating linked tables requires the source data to be accessible at the time of linking, otherwise the table(s) will not be created.

Technical Details

Access Split uses DAO 3.6 to access most database objects/properties. The utility can create source files from .mdb files created with Access 97 - 2003 and create/update .mdb files no older than Access 2000 .

In most cases only values that are different from defaults are recorded.

Known issues:

·  Creating source files for linked tables and queries based on linked tables takes a long time if they refer to a network location.
·  Replication is not supported.
·  Access security is generally not supported (only Database password is recorded)
·  Layout of Query Designers and Relationships window is not supported.
·  Custom command bars are not supported.

The utility comes as a stand-alone executable built using .Net technology, and therefore requires .Net runtime to be installed in order to run.

MS Access Add-In is being developed so that the functionality can be accessed without leaving the development environment.

Support

Please forward all your questions, comments or suggestions to support@compros.com.au.

Version updates:

·  06 Sep 2010:   Enhanced functionality to process improper object names (those that contain ?,/, etc).
·  03 Feb 2006:   Initial version made available to public.