Using SQL Access with Microsoft Access

Summary

This article details how to configure an ODBC Data Source Name (DSN) for Microsoft Access
2000, and how to log data into a Microsoft (MS) Access database using SQL Access for
Wonderware® InTouch™ and the installed ODBC driver. The graphics contained in this article
were captured from a Windows 2000 Server machine. If you are not using this operating system,
the graphics will look different from the ones in this article.

Terminology

Before you continue reading the procedure, please familiarize yourself with some terms, and
their definitions, used throughout this article.

Term Definition
Database A collection of tables containing data.
Contains rows (database records) and columns (database fields) of
Table
information.
Bind List A map between InTouch tags and table columns.
Defines the table columns and their data types. A table template is
Table Template
used when you create a new table.
Data Source An alias which associates a database with an ODBC driver.
Stands for “Open DataBase Connectivity.” This is Microsoft standard to
ODBC
allow interconnectivity between different database formats.

Configuring SQL Access for Microsoft Access

The following step-by-step procedure describes how SQL Access is configured for Microsoft
Access.

1. Start MS Access.

a. Create a new database table or use an existing database table.

b. Make notes of the database file name (for example: SQLAccessData.mdb),

the names of the tables and fields, the data types of the fields, and the
field sizes that you will be using.

2. Install SQL Access Manager, if you have not already done so.

3. If necessary, install the ODBC drivers for MS Access 2000.

a. Navigate to the ODBC Data Source Administrator.

b. Select Start, Programs, Administrative Tools, Data Sources (ODBC).

 o The ODBC Data Source Administrator dialog box will appear (Figure
1).

FIGURE 1: THE ODBC DATA SOURCE ADMINISTRATOR DIALOG BOX

c. On the ODBC Data Source Administrator dialog box, set up a new Data
Source by clicking the Add button.

o The Create New Data Source dialog box (Figure 2) will appear.

o An existing MS Access Data Source can be modified by clicking the

Configure button.
FIGURE 2: THE CREATE NEW DATA SOURCE DIALOG BOX

d. Once you have specified a new or an existing Data Source, the ODBC
Microsoft Access Setup dialog box (Figure 3) will appear.

FIGURE 3: THE ODBC MICROSOFT ACCESS SETUP DIALOG BOX

e. Enter a Data Source Name (for example: SQLAM_Access) and click the
Select button.

o The Select Database dialog box (Figure 4) will appear.

 f. Specify an MS Access database and its directory (for example: C:\Intouch
Applications\SQLAccess Test\Test.MDB).

g. Click OK.

FIGURE 4: THE SELECT DATABASE DIALOG BOX

o After the database is selected, it will be indicated on the ODBC

Microsoft Access Setup dialog box (Figure 5).

FIGURE 5: THE ODBC MICROSOFT ACCESS SETUP DIALOG BOX - THE DATABASE SELECTION

4. Create an InTouch application with tagnames that are similar in name to the
column names in your MS Access database. Use the following tag types for the
corresponding MS Access data types:
Tag Type Used for the MS Access Data Type
Memory Integer Short and Long
Memory Real Float4 and Float8
Memory Message Text

5. Create these tagnames for the SQL Scripts:

Tagname Tag Type

o ConnectionID - Memory Integer

o ResultCode - Memory Integer

o ErrorMsg - Memory Message

o TableName - Memory Message

o TemplateName - Memory Message

o BindList - Memory Message

o WhereExpr - Memory Message

o OrderByExpr - Memory Message

6. Create an InTouch window to display and input the MS Access database column
values into the tags created in step 5. Create these Pushbuttons that are linked
to the PushButton Action scripts listed below:

Connect Pushbutton

ResultCode = SQLConnect( ConnectionId, "DSN=<MyDSN>" );

TableName = "<MyTable>";
TemplateName = "<MyTemplate>";
BindList ="<MyBindList>";

Where:

1. <MyDSN> is the name you assigned for the ODBC Data Source Name.

2. <MyTable> is the name you assigned for the MS Access table.

3. <MyTemplate> and <MyBindList> are the names you assigned (or will
assign) for the SQL Access template file and Bind List.

Disconnect Pushbutton

ResultCode = SQLDisconnect( ConnectionId );

CreateTable Pushbutton
 ResultCode = SQLCreateTable( ConnectionId, TableName, TemplateName );

DropTable Pushbutton

ResultCode = SQLDropTable( ConnectionId, TableName );

Insert Pushbutton

ResultCode = SQLInsert( ConnectionId, TableName, BindList );

Select Pushbutton

WhereExpr = "";
OrderByExpr = "";
ResultCode = SQLSelect( ConnectionId, TableName, BindList, WhereExpr,
OrderByExpr );

First Pushbutton

ResultCode = SQLFirst( ConnectionId );

Prev Pushbutton

ResultCode = SQLPrev( ConnectionId );

Next Pushbutton

ResultCode = SQLNext( ConnectionId );

Last Pushbutton

ResultCode = SQLLast( ConnectionId );

End Pushbutton

ResultCode = SQLEnd( ConnectionId );

7. Create this Data Change script on the tag ResultCode to respond to errors in the
various SQL calls:

IF ResultCode == 0 THEN
ErrorMsg = "No errors occurred";
ELSE
ErrorMsg = SQLErrorMsg(ResultCode);
ENDIF;

8. Start the SQL Manager to create a Bind List (Figure 6).

o The Bind List associates the tagnames in your InTouch application to the
fields in the MS Access table that you want to reference.
 o It is very important that the column names configured are not keywords
that are excluded from use for the SQL Access Bind List and the Table
Template, and the Open Database Connectivity (ODBC) interface.

o A list of those keywords are included in the Appendix of "The Intouch SQL
Access Manager User's Guide."

Note: If the column names in the table have spaces, then enclose the names
with square brackets ([ ]). For example, the column name "Employee ID" should
appear in the Bind List as [Employee ID]. In future InTouch releases the brackets
may not be necessary.

FIGURE 6: THE BIND LIST CONFIGURATION DIALOG BOX

9. Configure a Table Template (Figure 7) in the SQL Manager.

o This is where you will duplicate the column names, data type and size of
the MS Access database that you want to access.

Note: Again, enclose the column names with square brackets ([ ]) if the names have
spaces. In future InTouch releases the brackets may not be necessary.
FIGURE 7: THE TABLE TEMPLATE CONFIGURATION DIALOG BOX

Also, if your MS Access table name has spaces, then enclose the table name in single
quotes (for example: 'my table').

Operating SQL Access

For most InTouch applications, the following steps on operating SQL Access are typical.

1. Start the WindowViewer and click the Connect button to perform an SQLConnect.

a. If SQLConnect is successful, then create the SQL Access table (if it does
not exist) by clicking the Create Table button.

b. This executes the SQLCreateTable command with the parameters that

were specified in step 2 in the "Configuring SQL Access for Microsoft
Access" section.

2. Insert the records into the table by entering data in the Data Entry fields and
clicking the Insert Record button which executes the SQLInsert command.

3. Use the SQL Select to select records for viewing and editing.

a. This will create a temporary Results table which contains records that can
be browsed by using SQLFirst, SQLLast, SQLNext, and SQLPrev.

b. See Chapter 4 of "The SQL Access for InTouch User's Guide" for details.

4. After you browse the Results table, you can then free up the system resources
that were used by the Results table by executing the SQLEnd command. You
 may also remove the SQL Access table by executing the SQLDropTable
command.

5. Disconnect from the MS Access database by clicking the Disconnect button.

SQL Access Reserved Keywords

If a reserved keyword is used as the Column Name in a Bind List or Table Template, an error
message is generated in the Wonderware Logger. The type of error generated depends upon the
ODBC driver being used and the location in which the keyword is found. For example, one of the
most common errors made is using the DATE and TIME for Column Names in a Bind List or Table
Template. To avoid this error, use a slightly different name, e.g., "aDATE" and "aTIME."

The reserved keywords define the Structured Query Language (SQL) used by InTouch SQL
Access. The keywords are also recognized by the specific ODBC driver being used. SQL Access
passes the SQL command containing one or more reserved keywords to the ODBC.dll file. If the
SQL command cannot be interpreted correctly, SQL Access generates an error message in the
Wonderware Logger.