Applies To:
  • CitectSCADA 5.xx

I have defined a query in Microsoft Access. I'm trying to read the results in Citect using a command like SQLExec(hSQL, "SELECT * FROM Query1"), but SQLGetField() returns "" (a blank string) for one of the fields. If I execute "SELECT * FROM Query1" in MS Query, I can see the field with no errors. 

You can view the results of a query built in MS Access by using SQL commands in Citect as if the query was a table in the database. However, if a field contains computed text, its size may be incorrectly reported by the MS ODBC Jet driver as being 2147483598 characters long (2 GB). Citect's SQLFieldInfo() function will report this invalid size as -49.

Here is an example of a computed field in a query in MS Access: SELECT IIf([Table1]![field2]=0,[Table1]![field1],[Table2]![field3]) AS Expr1 FROM Table1, Table2;

This problem is caused by Odbcjt32.dll version 4.0.5303.1 which is installed by Windows 2000 SP 1 and by Citect 5.30. See Microsoft Knowledge Base article Q272951
for details about this problem and access to the fix (odbcjt32.dll version 4.0.5629.0). The fix is not included in Jet SP 5 or MDAC 2.6, but is expected to be included in MDAC 2.5 SP 2. With the fix, the field size will be correctly reported as 256 characters in Citect.