mdbConverter
mdbConverter is very useful if you are maintaining mdb files (MS Access database files) . You can maintain data definitions and data in one mdb file, and apply (or let your "customers" apply) the changes to other mdb files.
mdbConverter can compare tables in mdb files or in other databases (like Oracle, MS SQL server) if their tables are linked by ODBC to a mdb file. mdbConverter will find out if there are differences in the data definitions as tables, fields and indexes. If so, You can tell mdConverter to change the data definitions or the data in a table based on a source table. In other words it can be used to synchronize mdb files.
Compare DB Structure
Shows tables in the Source, but not in the Target, and opposite. Shows
Differences in Fields and Indexes for Tables that Exists both in the Source and target.
Copy DB structure
By clicking this button, you will first get a form where you can select which
tables to Copy From Source to Target.
Options:
1. Overwrite:
Overwrites the entire Target table and the Data in the Target table is lost.
If you then want to replace data with data from the Source, you need to run the Copy
Data Command.
2. Add Tables:
Add the table If it doesn't exist in the target database.
If the Table doesn't exist in the Target it will be added. You also need to Run Copy Data
to Copy the Data from the Source tables to the target tables (that are Added).
3. Import Linked table:
If one of the tables You select to Add is a Linked Table (e.g. by ODBC), it will be
imported to an Access Table.
4. Replace Field Defs:
Update the Field Definitions in the Target table equal to the Source Table. In other
words, it makes the Table definitions equal.
Remark: Eventually Fields In the target, but not in the source are
removed. If this option is used together with Update Indexes, Indexes in the target but
not in the source are removed.
5. Update Indexes:
Update the Indexes in the Target table equal to the Source Table. In other words, it
makes the indexes equal.
Remark: Update Indexes should be used together with Replace
Field Defs or the Overwrite Tables option, to ensure to not getting invalid
indexes in the Target referring to fields that does not exist. If you use the Overwrite
Tables option, the data in the target table is lost. With the command Copy Data
you can Copy the Data from the Source to the Target tables.
WARNING!
If the there are differences in the Primary or Unique Indexes between the Target
and the Source table, data in the Target can get lost. Always take a backup before Copying
DB structure. When changes in fields name and primary keys are done, You can use the
overwrite table option. Then the Target table definitions (Fields and Indexes) will be
exactly as the Source. But the Target table have lost it's data, so you still have to copy
the data from the Source to the Target.
Problem Example 1:
Source table S_table consists of two fields:
ID (which is unique)
Name (which is NOT Unique)
Target table T_table consists of two fields:
ID (which is unique)
Name (which is Unique)
When Copying data, mdbConverter will try to Copy for example from the Source:
ID Name
1 Scott
2 Tiger
3 Scott
4 Annie
5 Tiger
The Target table will not accept this values, because Name is Unique, it only accepts one
Scott and one Tiger.
Copy Data
Use this Command to Copy Data from the Source to the Target tables. The data in the
Target table is overwritten and replaced by the source data. Successful use of this
Command, requires identical table structure between the source and target table, which is
obtained by the Copy DB Structure command.
Problem examples when the Table definitions are Not identical:
- If changing of Primary Key in the target or source (See Problem Example 1 above)
- If Allow zero length of a Field is False in the target but True in the Source. It is not possible to insert Zero length strings (empty strings) in Fields which is defined to not accept such strings, of course.
- If the Field names are different mdbConverter does not know which field to place the values in. Example: If the name of the Source field is Customer, and the "similar" name of the Target field is CustomerName.
- If the same Field name in Source and Target has different datatype. The Customer Field in the Source is Text, but Numeric in the Target. It will fail to insert a not numeric string like "Hydro" to a numeric Field.
If you need to convert data between not identical tables, you can use a tool like Database Integration Manager.