mDesktop can read and export data using standard ODBC connections. Supported ODBC drivers include the standard ones for SQL Server, mySQL, DB2, Oracle and Access.
When importing data, the user must create a single table or view containing the data which is to be read into mDesktop – for Access, this can be a query. The user can select which fields from the table or view they would like to import. mDesktop uses an ODBC Connection file to import the required information from the chosen data source, as well as export data back to the original source database. On output, the user can choose to create a new table, overwrite an existing table, or delete records from within a table and reassign orphaned records in a child table.
The following sections explain mDesktop's ODBC functionality in detail, covering all of the major options and processes.
You can create an ODBC Connection file by selecting the ODBC Connection Manager option from the File menu or by choosing the ODBC option in the mDesktop Import Manager window, as shown below (to access the ODBC Connection Manager window - click "…" after choosing the ODBC radio button).
The ODBC Connection Manager window is then displayed as follows.
Choosing an ODBC Data Source
To choose an ODBC Data Source, click the button. If prompted, enter the Login ID and Password for the data source.
Selecting a Table
After choosing a data source, select a table or view from the drop-down list of available tables. This defines the data that will be imported.
Choosing Which Fields to Import
When importing a table or view, you can choose what fields you would like to import. This feature can help improve processing time by limiting the size of large files with numerous fields that are unrelated to the matching process, as well as reducing the setup time required in mDesktop. You can select fields individually by double-clicking, select a block of fields by highlighting them and clicking "Add", or use the Add All button.
Once you have created an ODBC Connection file, you can import the data selected via mDesktop's Setup Wizard or by importing it into an existing mDesktop Main File. Both methods are described below.
Using mDesktop's Setup Wizard to Import ODBC Data
The mDesktop Setup Wizard can be launched by selecting one of the file wizards or by selecting the Setup Wizard option located under the Import menu. Choosing the latter method will prompt you for the file that you wish to import, so you can only use this if you have previously created an ODBC Connection (*.ODB) file. However, starting the Setup Wizard via the Single File or Two File button will allow you to specify an ODBC Connection file or create a new one, as discussed in "Creating an ODBC Connection file".
Importing ODBC Data into an Existing Main File
To import ODBC data into an existing mDesktop Main File, the DBF structure of the Main File must be consistent with the fields selected in the ODBC connection. If the field layout is not consistent between the DBF and the ODBC connection file, data will be misplaced causing problems during import or matching.
You can import records into an existing mDesktop Main File by selecting the Import Records option from the Import menu. If the Main File is not already open, mDesktop will prompt you for the file. Next, you will see the Input Parameters window, as described in Importing without using the Setup Wizard.
Once you have reviewed and changed the parameters if necessary, select the Continue button to specify the ODBC Connection file that you wish to use. mDesktop will then import the data and continue with processing as requested.
mDesktop can use the same ODBC Connection file to export data back to the data source. To do this, choose an output format of ODBC when specifying the output options. However, take careful note of the created ODB file name and location assigned by the Setup Wizard, so that you can select it for output as you will be prompted for the ODB file. You will then see the following window:
Creating a New Table
To create a new table, choose the "New Table" radio button and enter a table name. The new table will be created in the Data Source specified by the ODBC Connection file.
Overwriting an Existing Table
To overwrite an existing table in the source database, choose the "Existing Table" radio button. Choosing this output option allows you to specify other options which are only available for this type of output. The resultant output window is displayed below.
Overwrite Table with mDesktop information
This option will overwrite an existing table with information from the mDesktop Main File. The type of output will depend on what output option was chosen (unique records, matched records, or flagged records) and the field layout will be determined by the layout option(s) chosen by the user.
Just remove duplicate records
This will remove the duplicate records that you flagged within mDesktop from the existing table that you selected.
Both the "Overwrite Table with mDesktop information" and the "Just remove duplicate records" options can cause relational data to become corrupt if careful steps are not taken. mDesktop has integrated "Child Table Relationships" to help cope with this task.
Reassign Orphaned Records
This functionality is used when working with parent and child tables that are related via a specific reference field. Generally the parent table will be (or should be) a unique list of names (or companies) and addresses. The child table will often be transactional data, linked to the parent table via the reference field. Each parent record will relate to any number of child records in a one-to-many relationship. mDesktop is commonly used to dedupe a parent table. This could result in orphaned child records, where parent records are removed and the child records that relate to the removed records are left orphaned (where the references in these child records no-longer relate to references that exist in the parent table).
The "Reassign Orphaned Records" functionality enables users to specify any related child tables during the output step (when writing the deduped parent data back). It will then re-populate the references of the records in the child table(s) with new references. The new references will be those of the records that the original parent records were deleted against.
To use this functionality in mDesktop, there must be a parent table and a child table. The child records must contain references linking them to parent records. Parent Data should be imported via an ODBC connection, and upon doing so, it is essential that the reference field linking the parent and child tables together is labeled as 'unique_ref' during the Setup Wizard stage.
The following example illustrates this…
FRED WATERS, 1 High Street, Leatherhead ID110
MR F WATERS, 1 High St, LEATHERHEAD ID103
F WATERS Transaction 31763 ID103 - changed to ID110
F WATERS Transaction 332140 ID103 - changed to ID110
In this example, all F WATERS records in the child table became orphaned, as their parent record had been removed in the parent table. The references for the child records have now been re-assigned to the new parent record. So 'MR F WATERS' has been removed and all transactions in the child data that related to this record, now relate to 'FRED WATERS' instead.
After importing the parent data and flagging the duplicates, you can write the data back to the source database using the Output To File screen, Output Flagged Records or Output Matched Records. You can select the required fields as normal for the type of output that you are using.
Make sure that the same ODBC connection used to import the data is selected as the output destination, and select ODBC as the output format.
If you select ODBC as the output format, mDesktop will display the 'ODBC File Output Options' screen:
The 'Reassign' functionality is only relevant when overwriting the source table, so from the 'Existing Table' drop-down, select the original parent data table. Next, select 'Reassign Orphan Records' on the right-hand side of the dialog. The child table and the corresponding reference field can then be specified, linking the child records to the parent records. Click on 'Continue' to export the cleaned parent data and re-populate the references in the child table where necessary.