Accessing Citadel 4 Data with Microsoft Access

Note: The SQL/92 standard states that a delimited identifier is any string of no more than 128 characters enclosed in quotation marks. It further states that characters within a delimited identifier are exempt from SQL syntax checking. Unfortunately, Microsoft Access performs its own syntax checking for ODBC queries using a non-standard SQL syntax—even within delimited identifiers. For this reason, National Instruments provides a Convert Special Characters selection in the Citadel ODBC Setup dialog box. When this option is selected, the ODBC driver converts the special characters to accepted Access characters. Refer to the related link "Setting up Citadel as an ODBC Data Source" for more details.

Some Tips for Querying Citadel using Microsoft Access
When you query Citadel using Microsoft Access, you must use the Microsoft Access non-standard SQL syntax in your select statement. Remember to consider the following elements:

• Convert special characters for Access compatibility
• Place double quotes around Lookout trace names to identify them as delimited identifiers
• Enclose identifiers in square brackets ([ ])
• Place pound signs (#) around time stamps

For example, to retrieve LocalTime, Pot1, and Tiway1.V101, where LocalTime is greater than 11/20/95 18:00:00, and where Interval is one second, enter the following query:

SELECT LocalTime, ["Pot1"], ["Tiway1@V101"]
FROM Traces
WHERE LocalTime > #11/20/95 6:00:00 PM#
AND Interval = '0:01'

Querying Citadel from Microsoft Access
Use the following procedure to query Citadel from within Microsoft Access.

1. Open a database and select File»Get External Data»Link Table. The Link dialog box appears, as shown in the following illustration.
2. Set the Files of type field to ODBC databases(). The Select Data Source dialog box appears.
3. Choose the Machine Data Source tab.
4. Highlight Citadel 4.0 Database.dsn, as shown in following illustration. (This example assumes you have created a Citadel data source as described in the related link "Setting up Citadel as an ODBC Data Source." If you have not created a Citadel data source, you may not be able to complete this example exercise.)
5. Click on OK. The Link Tables dialog box appears.
6. Choose Traces. The new table links to your database.
7. The Select Unique Record Identifier dialog box appears. Click on Cancel, because Lookout does not support unique record identifiers.
8. At this point, You should get a Traces entry in the database window. This is the link to the actual database table. You can double click on the Traces table to retrieve data from the Citadel database. You will probably notice that in the result table you have only one row per day! In other words the time interval between retrieved rows is 1 day.



9. If we want to retrieve data with a particular time interval we need to create a query. Select Queries in the Objects bar and then double click on the Create Query in the design view. Two new windows (Query1 : Select Query and Show Table) should pop up. Double click on Traces in the Show Table to add it to the query panel and then click the Close button.





10. Now we are ready to build our query. Field cells in the grid let you select any table column. Select Interval in the first field, uncheck the Show checkbox and type =’0:1:0’ in the Criteria. This will set the time interval to one minute (0:1:0 represents H:M:S). Select LocalTime for the second column and some data columns for a few other columns (here “YS23_RB_MB1”, “PT02”).




11. Run the query. Select Query >> Run. You should get a new window with your data. Notice that you are getting all columns that we selected in the previous step, except the Interval for which we unchecked the checkbox. Also note that the time interval between rows is one minute as expected.

To go back to the design view select View >> Design View.

Reader Comments | Submit a comment »