The connection to a single table from the Access database has been shown in the Configuring an ODBC Replicator for connecting to an Access Database application example. Additional options for advanced data queries will now be shown.
The Demo-Database.mdb example database must already have been setup as a data source. Edit the settings for this data source now and open the page, where the Contacts were selected. Select the Enter SQL Select Statement option.
An SQL Select statement serves to request data. Records can be filtered using this statement and data united from multiple tables. You will find additional documentation about SQL on the Internet pages of the respective database vendor or in the Microsoft Access® help files. You can also use Microsoft Access® for creating these requests.
Creating a Filter for Records
The example database contains a Title field in the Contacts. Only the contacts that have the value, "Frau", in this field should be used. The SQL statement for this is:
SELECT * FROM Contacts WHERE Title = 'Frau'
Click the Next button. You will now see several records in the field assignment, which have the value, "Frau" in the Title field.
Joining Data from Multiple Tables
The example database does not contain any company names in the Contacts table. However, the CompanyID field does exist, which will create a reference to the company data in the Company table. The query will now be expanded so that the CompanyName field will be assigned to the contact. The SQL statement for this is:
SELECT Contacts.*, Company.CompanyName FROM Contacts LEFT OUTER JOIN Company ON Contacts.CompanyID = Company.CompanyID
Click the Next button. The CompanyName field will now also be available in the field assignment in the source fields.
Duplicate Field Names when joining Multiple Tables
The Company table has the Street field, which contains the company address. A field with the same name also exists in the Contacts table. The AS SQL instruction is used to rename a field, in order to be able to distinguish the fields with the same name. The SQL statement for this is:
SELECT Contacts.*, Company.CompanyName, Company.Street AS CompanyStreet FROM Contacts LEFT OUTER JOIN Company ON Contacts.CompanyID = Company.CompanyID
Click the Next button. The CompanyStreet field will now be available in the field assignment in the source fields.
Version MetaDirectory_4.0