Reading Data from an Access Table

To read records from a Table, DataBaseName; TABLE TableName is an appropriate Topic. The DataBaseName is the name of the MDB file. It may be necessary to include the file path but generally not if it is known that Access will be running with the target MDB open. The MDB suffix is optional.

The APPLICATION and TOPIC are used to Initiate a Channel.

hChannel = DDEhInitiate("MSAccess", "C:\TEMP\TEST.MDB; TABLE tblRecipes");

The handle (hChannel) can then be used in subsequent DDE Requests to read the Tables Rows into a Cicode string variable.

sDATA = DDEhRequest(hChannel, "FirstRow");

The data is returned as an entire Row from the table into the Cicode string variable with TAB characters between the fields (Columns). Cicode should be used to find the TAB characters and equate the values in the fields (Columns) to Cicode variables. Tables with Rows that exceed the maximum string length for Cicode (counting the TAB characters) cannot be accessed in this way. This problem can be worked around by calling a Query (with fewer fields) rather than directly accessing the Table (see also a discussion comparing DDE and ODBC Q1841).

Subsequent Rows (records) can be read by using NextRow as the Item. Items PrevRow and LastRow can also be used as required.

To free up the resources used by the channel when it is no longer required, use

DDEhTerminate(hChannel);

DataBaseName; QUERY Queryname can be used in a similar fashion to the TABLE topic. To get data from a table that has too many characters per Row, a Query can be defined returning only the Columns (fields) of immediate interest and accessed via DDE in place of the table.

Changing Data to an Access Table

There is no Item to directly write information to an Access table and there is no provision to execute Action SQLs (such as INSERT, UPDATE and DELETE). To perform actions on Access Tables via DDE, design an Action Query in Access then design a Macro that Opens that Query. To suppress to usual warnings that Action Queries generate, include a SetWarnings = No instruction in the Macro before opening the Query.

If the Query is called qdeDeleteRecipe, for example, and the Macro mcrDeleteRecipe then the following applies.

In Access:

mcrDeleteRecipe

SetWarnings, No
OpenQuery, qdeDeleteRecipe, Datasheet, Edit
SetWarnings, Yes

In Cicode:

hChannel = DDEhInitiate("MSAccess", "SYSTEM");
DDEHExecute(hChannel, "[mcrDeleteRecipe]");
DDEhTerminate(hChannel);

Append and Update queries can be designed in Access to copy data to or from attached DBF files which function as temporary buffers. Cicode Dev functions can then be used to write to or read from the DBF files.

Using DBF files

Access can ATTACH to DBF files thus providing a means of overcoming some of it's shortcomings as a DDE Server.

To write to a Table, for example, a DBF file can be used as a temporary buffer. Ideally the field (Column) names of the DBF file should match those of the target Access Table (the Access Query Definition will automatically match them up). An Access APPEND QUERY can be designed to copy all records (Rows) from the DBF file to the Access Table.

Before writing to the file (using DevOpen, DevAppend, DevSetField etc), DevZap can be used to clear any existing records from the DBF and after the data has been written, the APPEND QUERY can be called (via DDE and an Access Macro) to copy it into the Table.

To change Rows (records) in a Table a similar method can be used using an Access UPDATE QUERY.

In similar way Access APPEND or MAKE TABLE QUERIES can be used to select and sort data from Tables into to DBF files that contain only the required Rows (records) and Columns (fields) sorted in the desired order. DevOpen, DevFirst, DevGetField, DevNext etc can then be used to read the subset of data.

The advantage this has over simply using DBF files is that the relational, security, multi-user & query optimisation features of Access can be utilised.

Getting the Correct Syntax

In general, the easiest way to develop SQL text is to use the Access Query Designer then switch to the SQL view and copy the SQL text that has been generated. The text generated by Access is usually more verbose than is absolutely necessary to avoid any chance of ambiguity. For example "Sugar = 10.4" would be "tblRecipe.Sugar = 10.4" and there are often unnecessary extra brackets in the WHERE clause. If the SQL text exceeds the maximum Cicode string length, trimming out the redundant syntax may be necessary.

Executing Access Macros and Code

To execute a Macro use DDEExec or DDEhExecute. Note that, DDEExec has only one parameter, it cannot be used for Network DDE because it cannot address a remote computer. For Network DDE, DDEhExecute must be used.

hChannel = DDEhInitiate("MSAccess", "System");
DDEhExecute(hChannel, "[mcrAppendRecipe]");
DDEhTerminate(hChannel);

There is no way to directly call Built In or User Functions over DDE to Access. To call a function, design a simple Macro using the RunCode instruction and call the Macro as described above.

If the function requires parameters that change at run time it is necessary to use the Eval() function in Access as follows.

Firstly write a simple Access function that reads text from a Text File to which Citect can write the function calls (with parameters). The file can be used as a simple scheduling/queuing system for function calls. The following Access VBA is a working example:-

Public Function funcCallAccesFunction()
   Dim varResult As Variant
   Dim intFileNumber As Integer
   Dim strInputLine

   intFileNumber = FreeFile
   Open "C:\My Documents\AccessCall.txt" For Input As intFileNumber

   While Not EOF(intFileNumber)
      Line Input #intFileNumber, strInputLine
      varResult = Eval(strInputLine)
   Wend
End Function

Then design a Macro that uses RunCode to call this function called, for example, mcrCallAccessCode. The following Cicode can then be used to write any Access function to the text file and call the Access Macro to read the file and execute the function calls.

Function CallAccessCode
   INT hChannel;
   INT hTextFile;
   hTextFile = FileOpen("C:\My Documents\AccessCall.txt", "w");
   FileWriteLn(hTextFile, "MsgBox(^"Hello^", 48, ^"Message from Citect^")");
   FileWriteLn(hTextFile, "MsgBox(^"Goodbye^", 64, ^"Message from Citect^")");
   FileClose(hTextFile);
   hChannel = DDEhInitiate("MSAccess", "System");
   DDEhExecute(hChannel, "[mcrCallAccessCode]");
   DDEhTerminate(hChannel);
END

The code can be used to call any Access Inbuilt or User Defined Function with appropriate number of parameters.