Using Access MDB as a Data Source

Database Exchange control supports two drivers:

1. ODBC driver for Microsoft Access (*.MDB)
2. Jet OLE DB Provider. This driver requires installation of MS Access.

Be aware that the schema of an Access database retrieved by Database Exchange is grouped into a table list and stored procedure list.

The table list includes the following objects:

• Tables
• Select queries without parameters

The stored procedure list includes the following objects:

• Select queries with parameters
• Make table queries
• Append queries
• Update queries
• Crosstab queries

Notes

1. Unlike MSSQL stored procedures, no parameters are prompted for Access parameterized queries in the parameter field. So you need to know what the parameters and their order are.
2. Each parameter must be enclosed by ‘ ‘ and separated by a comma.
3. Don’t use parentheses () to enclose the parameter list.
4. As a parameter is entered as a string, the ActiveX control tries to convert it to its original data type defined in the parameterized query. So for datetime type parameters, parameter entry should use format ‘YYYY/MM/DD HH:mm:ss’.

For instance, assume that there is a parameterized query in your Access database and its SQL statement looks like

SELECT AlarmSummary.*
FROM AlarmSummary
WHERE (((AlarmSummary.OnDate) Between [Date1:] And [Date2:]));

This query has two parameters, Date1 and Date2. Be aware that the field OnDate is a Date/Time type. If you want to retrieve data between 10/10/2003 and 11/10/2003, you should enter

‘2003/10/10’, ‘2003/10/11’

into the parameter field on the Database Exchange Properties dialog box.

Note: You need Version 3.1.1 or later of Database Exchange control to support Access parameterized queries with Jet OLE DB Provider only.