Reading Data from an Access Table

A SELECT query can be used to read data from and Access Table or to call an Access Query. A Query is preferred over a Table if there are many more Columns in the table than are required at the time, if the data needs to be sorted or if there is a requirement to relate or join a number of Tables. The Cicode required is as follows.

Function SQLTest
   INT hSQL, iResult;
   hSQL = SQLConnect("DSN=ODBCTest;UID=YourUID_C;PWD=YourPWD");
   IF hSQL <> -1 Then
      iResult = SQLExec(hSQL, "SELECT * FROM qryRecipes WHERE Recipe Between '3000' And '6000'");
      IF iResult = 0 Then
         WHILE SQLNext(hSQL) = 0 DO
            TraceMsg(">" + SQLGetField(hSQL, "Recipe") + "<>" +
            SQLGetField(hSQL, "Flour") + "<>" +
            SQLGetField(hSQL, "Water") + "<>" +
            SQLGetField(hSQL, "Cocoa") + "<");
         END
         SQLDisconnect(hSQL);
      ELSE
         Message("SQL Error", SQLErrMsg, 48);
      END
   ELSE
      Message("SQL Error", SQLErrMsg, 48);
   END
END

Writing Data to an Access Table

To append data to an Access Table using ODBC, an SQL INSERT statement can be used.

Function SQLInsert
   INT hSQL, iResult;
   hSQL = SQLConnect("DSN=ODBCTest;UID=YourUID;PWD=YourPWD");
   IF hSQL <> -1 Then
      iResult = SQLExec(hSQL, "INSERT INTO tblRecipes (Recipe, Flour, Water, Cocoa) VALUES ('X1234', 2, 3, 4)" );
      SQLDisconnect(hSQL);
   END
END

To avoid having to deal with SQL statements, the standard Cicode Device functions can be used to append records to an Access table. Firstly configure an SQL Device. If the table has a lot of fields that do not need to be written to, define only those fields that are required in the device definition (this keeps the device definition as simple as possible and reduces the number of DevWrite instructions). DevOpen, DevWrite and DevClose can then be used to add records to the table. Cicode will accept successive DevWrites until they equal the number of fields in the device definition at which time it will construct an SQL INSERT statement. The DevWrites must contain data for fields in the same order as the device definition. It is best to do a DevOpen followed immediately by successive DevWrites for as many records as are required then a DevClose to avoid the risk of the data being out of context.

To edit or update data in and Access table there must be a unique (usually primary) key to identify the Row (record) to be changed. This is to be able to provide a WHERE clause that will apply only to that Row in an SQL UPDATE.

To edit data, read the data in the normal way, keeping track of the unique key. Any changed values can later be written to the same Row using an UPDATE query with a WHERE clause.

Function SQLUpdate
   INT hSQL, iResult;
   hSQL = SQLConnect("DSN=ODBCTest;UID=YourUID;PWD=YourPWD");
   IF hSQL <> -1 Then
      iResult = SQLExec(hSQL, "UPDATE tblRecipes SET Flour = 20, Water = 30,Cocoa = 40 WHERE Recipe ='X1234'");
      SQLDisconnect(hSQL);
   END
END

The ODBC/SQL environment does not provide the facility to edit the "Current Record". There is in fact NO Current Record. For this reason DevAppend and DevSetField cannot be used to add or modify records.

Deleting Rows from an Access Table

The DELETE keyword is used in conjunction with a WHERE clause to delete the required Row or Rows.

Function SQLDelete
   INT hSQL, iResult;
   hSQL = SQLConnect("DSN=ODBCTest;UID=Dick_C;PWD=pulp");
   IF hSQL <> -1 Then
      iResult = SQLExec(hSQL, "DELETE FROM tblRecipes WHERE Recipe = 'X1234'");
      SQLDisconnect(hSQL);
   END
END

Calling ACTION QUERIES

Access ACTION Queries CANNOT be called in a SELECT Query such as:-

"SELECT * FROM qdeDeleteRecipe".

To call an Access ACTION via ODBC, use the Call statement in SQLExec. Note that the statement must be enclosed in "curly" brackets.

"{Call qdeDeleteRecipe}".

The Call statement can be used to Call SELECT Queries, the resulting Recordset being accessible in the normal way.

PARAMETER QUERIES

Many ODBC Servers will accept PARAMETERS in a Call statement so that Parameters can be defined in a Query Definition on the Server and their values supplied by ODBC Clients at run time. Unfortunately, the Access Jet Engine uses Parameter Markers which are not supported in the standard Call statement.

The following can be used as a work around:-

For each query that requires parameters, design a arguments table with the same name as the query but with a different prefix. For example, if the QUERY is qryParamTest, the TABLE could be called argParamTest..

The TABLE should have, say, five fields called Param1, Param2, Param3, Param4, Param5.

This table should be added to the Access Query Definition for qryParamTest. When this has been done, the field names can be used a parameters anywhere in the Query Definition.

+------------------------------------------------------------------+
| qryParamTest:Select Query                                        |
+------------------------------------------------------------------+
|  +-----------------+    +-------------------+                    |
|  | argParamTest    |    | tblRecipes        |                    |
|  +-----------------+    +-------------------+                    |
|  | *               |    | *                 |                    |
|  | Param1          |    | RECIPE            |                    |
|  | Param2          |    | Flour             |                    |
|  | Param3          |    | Water             |                    |
|  | Param4          |    | Cocoa             |                    |
|  | Param5          |    +-------------------+                    |
|  +-----------------+                                             |
+           +-----------------+------------+-----------+-----------+
|    Field: | Recipe          | Flour      | Water     | Cocoa     |
+           +-----------------+------------+-----------+-----------+
|    Table: | tblRecipes      | tblRecipes | tblRecipes| tblRecipes|
+           +-----------------+------------+-----------+-----------+
|     Show: | X               | X          | X         | X         |
+           +-----------------+------------+-----------+-----------+
| Criteria: |Between [Param1] |            |           |           |
|           |And [Param2]     |            |           |           |
+           +-----------------+------------+-----------+-----------+
|           |                 |            |           |           |
+           +-----------------+------------+-----------+-----------+
|           |                 |            |           |           |
+           +-----------------+------------+-----------+-----------+
|           |                 |            |           |           |
+           +-----------------+------------+-----------+-----------+
|                                                                  |
+------------------------------------------------------------------+

A simple Cicode function can be written to which the QUERY name (without the prefix) and Parameters are passed. The function inserts "arg" in front on the Query name and executes a DELETE from the TABLE, to be sure that it is empty, and then performs and INSERT to leave the table containing ONE RECORD with the desired Parameters in the appropriate fields. The function then prefixes the Query Name with "qry" and Calls the Query.

Function SQLCall(INT hSQL, STRING sQueryName, STRING sArg1 = " ", STRING sArg2 = " ", STRING sArg3 =" ", STRING sArg4 = " ", STRING sArg5 = " ")

   STRING sTable, sQuery;
   sTable = "arg" + sQueryName;
   sQuery = "qry" + sQueryName;
   SQLExec(hSQL, "DELETE FROM " + sTable);
   SQLExec(hSQL, "INSERT INTO " + sTable + " (Param1, Param2, Param3, Param4, Param5) VALUES ('" + sArg1 + "', '" + sArg2 + "', '" + sArg3 + "', '" + sArg4 + "', '" + sArg5 + "')");
   SQLExec(hSQL, "{Call " + sQuery + "}");
END

Calling the Function form Cicode is then as simple as:-

SQLCall(hSQL, "ParamTest", "2000", "4000");

Note that the default parameters for SQLCall must be SPACES if "Allow Zero Length" is "No" in the Access Table Definitions for fields Param1, Param2 etc.  The function can be used to call many different parameter queries.

An advantage of this work around is that, even after Citect has been shut down, the Query can be called from Access and, because the parameters are still stored in the arguments table, the resulting Recordset can be viewed.

In general, it is probably simpler to design Queries that perform any required joins, sorting and field selection and call them using a WHERE clause to select the desired Rows (records).

Getting the Correct Syntax

The ODBC syntax for SQLs varies from the Access syntax in some ways. A good way to get the syntax correct and view the resulting recordset is to use the query designer in Microsoft Query then copy the SQL text from it into Cicode. Because MS Query uses ODBC, any syntax that works in it will work when called via ODBC from Cicode. MS Query can also be used to confirm that the DSN is correct.

MS Query tends to create SQL text that is more verbose than absolutely necessary. In particular it always includes the path with the file name which is not necessary because the path is already defined in the DSN entry. It is also bad practice to hard code file paths. MS Query tends to prefix all Column (field) names with the Table names to avoid any chance of ambiguity. Again this is not always necessary and it is desirable to keep the SQL text as brief as possible in your code.

The SQL statement text generated by the query designer can be pasted into Execute SQL window (under the File menu of MS Query), any surplus text removed and the SQL statement tested until the simplest syntax that works can be found. There is provision to save the SQL text if required. The final version of the SQL statement can be used with confidence in Cicode.

Access Date/Time and Cicode Date/Time
Access and Cicode have different Date/Time variables. There are three ways to convert between the two:-

Convert to real numbers
In both Cicode and Access it is possible to equate Real Numbers to Data/Time variables. The conversion between the two systems is as follows:-

For 16 bit Windows CitectTime = 86400 * (ExcelTime - 25568.66667)
For 32 bit Windows CitectTime = 86400 * (ExcelTime - (25569 + (lTimeZone / 24)))

For 32 bit Windows ExcelTime = 25569 + ((CitectTime + GMTBias()) / 86400.0);

(See the GMTBias() Cicode function below).

CitectSCADA v6.00 has a new Cicode function to do this conversion for you. Use the following command:

ExcelTime = TimeToOleDate(CitectTime, 1);
 

There is a detailed Excel VBA example at the end of this article showing how to deal with the more complex 32 bit situation.

Covert to strings
The Data and/or Time are converted to and from text strings using the standard conversion functions available in each environment.

Use the #Date/Time# SQL syntax
The Jet Engine will convert and Dates and Time strings enclosed in # markers. This date be very useful in a WHERE clause:-

SELECT * FROM qryMyQuery WHERE Date BETWEEN #3/20/96# AND #3/27/96#

Note that the American Date format is always used in this case, the Jet Engine DLL ignores the Date and Time settings as set in the Control Panel.

Programming Style

Most of the sample code does not include error checking and reporting. This has been done to keep the examples as simple as possible. Error checking is however essential for ODBC code. The first example (SQLTest) suggests how this can be done.

As mentioned above, considerations should be given to implementing most of the complexity of Queries in Access Query Definitions where they are easier to design and the results are easily viewed. A WHERE clause can be used when calling the Query to select only the desired Rows at run time. Where tables have many Columns (fields), the Access Query Definitions can be used to restrict any particular call to view only the fields of interest.

It is helpful to build the SQL test up into strings. Firstly the ODBC function calls become simpler to read. Secondly the strings can be passed through TraceMsg() to make debugging easier.

Remember that the Jet Engine runs on the same PC as Citect and that complex Queries returning large Recordsets can have an adverse impact on CPU and memory resources. Potential problems can be avoided by careful table, query and relationship design.

Setting up ODBC

To call ODBC on another Citect PC use MsgRPC or setup a report or event code triggered by a PLC bit. To use ODBC, the Access ODBC Driver must be installed. This can be obtained from Microsoft and is included with Microsoft Office. It is important to use the 16 bit drivers for Windows 3.1x/Citect 3.x and the 32 bit drivers for Windows 95/Citect 4.x. The installation procedure (eg for Microsoft Office) will copy the necessary drivers and the Jet Engine DLL into the appropriate Windows directories.

With the Driver installed on the PC the ODBC Icon can be selected from the Control Panel and a Data Service Name set up for the desired MDB. This is used in the DSN= part of the connect string.

The Jet Engine DLL is quite large (1 MB) and a problem can arise if the Windows Virtual Memory Manager (VMM) swaps the it out of memory because the next time an SQL is executed there will be an unexpected delay while the DLL is loaded back into memory. To force the VMM to keep the DLL in memory, design a simple dummy table with only one record and one field and set up a Cicode task that frequently (say every 10-15 seconds) calls a SELECT Query based only on the dummy table. This has no significant effect on CPU load and keeps the DLL in memory.

Converting Times in 32bit Windows

Converting times has become more involved with the introduction of 32 bit Windows. In the 32 bit Windows the locale that is specified in the Control Panel is taken into account when reading the time. In Windows 3.x, Redmond, WA, USA was always assumed. Remond is 8 hours (ie 0.33333 days) behind GMT hence the 0.66667 in the conversion. In 32 bit Windows, it is now necessary to calculate which time zone the locale is in. The following VBA for Excel code shows how this was taken into account in DDEFORMU.XLS. If daylight saving is correctly set up for the locale then this code will also take that into account. Note that two API calls and the SYSTEMTIME data types need to be declared. Note that Excel and Access use common Date/Time variables.

'Sample VB Code

Declare Sub GetSystemTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)
Declare Sub GetLocalTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)

Type SYSTEMTIME
   wYear As Integer
   wMonth As Integer
   wDayOfWeek As Integer
   wDay As Integer
   wHour As Integer
   wMinute As Integer
   wSecond As Integer
   wMilliseconds As Integer
End Type


Sub SetnTime()

   Dim ExcelTime As Double
   Dim CitectTime As Double
   Dim lTimeZone As Long
   Dim tzSystem As Double
   Dim tzLocal As Double

   ExcelTime = CDbl(TrendTagsWS.Range("StartDate")) +    CDbl(TrendTagsWS.Range("StartTime"))

   If CitectTagsWS.Range("Environment32") Then

      Call GetSystemTime(trSystem)
      With trSystem
         tzSystem = DateSerial(.wYear, .wMonth, .wDay) + TimeSerial(.wHour, .wMinute, .wSecond)
      End With

      Call GetLocalTime(trLocal)
      With trLocal
         tzLocal = DateSerial(.wYear, .wMonth, .wDay) + TimeSerial(.wHour, .wMinute, .wSecond)
      End With

      lTimeZone = Application.Round(24 * (tzLocal - tzSystem), 0)
      CitectTime = 86400 * (ExcelTime - (25569 + (lTimeZone / 24)))
   Else
      CitectTime = 86400 * (ExcelTime - 25568.66667)
   End If
   TrendTagsWS.Range("nTime") = CLng(CitectTime) + (TrendTagsWS.Range("nPeriod") * (TrendTagsWS.Range("nLength") - 1))
End Sub

// Sample Cicode

// Returns the current time zone's offset from Greenwich Mean Time in seconds.
// Takes daylight savings time into effect
INT
FUNCTION
GMTBias()

     // Constants
     INT DAY_SECS = 86400;
     INT GMT_1_1_1980 = 315532800;     // Citect time/date variable for 1/1/80

     // Variables
     INT iBiasStandardTime;     // Current time zone's bias from GMT when in standard time
     INT iBiasCurrentTime;     // Current time zone's bias from GMT. May or may not be daylight savings time
     INT iLocal_1_1_1980;               // Citect time/date variable for 1/1/80 in local time zone
    
     iLocal_1_1_1980 = BuildDate(1,1,1980);
    
     iBiasStandardTime = GMT_1_1_1980 - iLocal_1_1_1980;
     iBiasCurrentTime = TimeMidNight(TimeCurrent()) MOD DAY_SECS;
    
     IF (iBiasStandardTime >= 0) AND (iBiasCurrentTime <> 0) THEN
          iBiasCurrentTime = DAY_SECS - iBiasCurrentTime;
     END
    
     IF iBiasStandardTime < 0 THEN
          iBiasCurrentTime = iBiasCurrentTime * (-1);
     END
    
     RETURN iBiasCurrentTime;

END

// Build a Citect time/date value from separate day, month, and year values.
// Takes into account local month/date/year order and separator character
// Supporting function for GMTBias()
PRIVATE
INT
FUNCTION
BuildDate(INT iDay, INT iMonth, INT iYear)

     // Constants
     INT          DATE_ORDER = 1;
     STRING     MMDDYY_ORDER = "0";
     STRING     DDMMYY_ORDER = "1";
     STRING     YYMMDD_ORDER = "2";
     INT          DATE_DELIMITER = 2;
    
     // Variables
     STRING sDelimiter;
     STRING sDate;

     sDelimiter = DateInfo(DATE_DELIMITER, 0);
    
     SELECT CASE DateInfo(DATE_ORDER, 0)
          CASE MMDDYY_ORDER
               sDate = iMonth:# + sDelimiter + iDay:# + sDelimiter + iYear:#;
               RETURN StrToDate(sDate);
          CASE DDMMYY_ORDER
               sDate = iDay:# + sDelimiter + iMonth:# + sDelimiter + iYear:#;
               RETURN StrToDate(sDate);
          CASE YYMMDD_ORDER
               sDate = iYear:# + sDelimiter + iMonth:# + sDelimiter + iDay:#;
               RETURN StrToDate(sDate);
          CASE ELSE
               RETURN 0;
     END SELECT
END