In the application example Configuration of an ODBC replicator to connect an Access database the connection of a single table from an Access database was shown. Now other possibilities of extended data retrieval are shown.
The sample database Demo-Database.mdb must already be set up as a data source. Now edit the settings of this data source and open the page where the "Contacts" table was selected. Select "Enter SQL Select statement".
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 sample database contains a field "Title"' in the contacts. Only the contacts that have the value "Woman" in the field are to be used. The SQL statement for this is:
SELECT * FROM Contacts WHERE Title = 'Frau'
Click on Next. In the field mapping you will now only see records that have the value "Woman" in the "Title" field.
Joining Data from Multiple Tables
The sample database does not contain a company name in the "Contacts" table. However, there is a field "CompanyID" which creates a reference to the data of the company in the "Company" table. The query is now extended so that the "CompanyName" field is 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 Next. In the field mapping, the "CompanyName" field is now also available in the source fields.
Duplicate Field Names when joining Multiple Tables
In the "Company" table there is a "Street" field that contains the address of the company. In the "Contacts" table there is a field with the same name. In order to distinguish the fields with the same name, the SQL statement "AS" is used to rename a field. 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 on Next. In the field mapping, the "CompanyStreet" field is now also available in the source fields.
Version 6