Each has advantages and disadvantages. In general
DDE is suitable for simple requirements but ODBC give serious
thought if the limitations of DDE become too restrictive.
DDE Advantages
No need to set up a Data Service
Name (DSN); however, a DDEShareName is necessary for Network
DDE.
Can call Access Macros &
Functions.
DDE Disadvantages
Record sets with rows that exceed
the maximum Cicode string length cannot be read directly.
Rows (records) are returned to
string variable with TAB characters between columns. You need to
parse the string in Cicode to obtain the column (field)
values.
SQL over DDE cannot perform
actions (such as INSERT, UPDATE or DELETE).
DDE Client and server
applications need to be be running at the same time.
ODBC Advantages
MS Access does not have to be
running. ODBC uses the JET Engine DLL on the same PC. This an
advantage in many ways but can consume excessive PC resources if
not managed properly.
Large SQL statements can be split
into sub-strings.
SQLGetField makes easier to get
data from fields (columns). There is no need to parse the data in
Cicode.
Can handle large numbers of
fields (columns) in the Recordset.
SQL can perform actions (such as
INSERT, UPDATE or DELETE).
ODBC Disadvantages
Requires that a Data Service Name
(DSN) be set up.
The JET Engine DLL cannot be directly called on a
remote PC, (Reports or MsgRPC() can be used however, to run SQL
statements on a Remote Computer which needs to be be running
CitectSCADA).