Initially, you will probably find that sticking with DDE is the simplest,
because almost everything you are currently doing will still work.
The main disadvantages of DDE for people are probably
a. it seems to be increasingly affected by security patches
b. it requires you to have Access
c. it starts Access, thus complicating things if, for example, you are
automating a merge
d. it cannot see Unicode data
ODBC and OLEDB have similar advantages and disadvantages to each other, but
OLEDB is the more recent and (in theory) better supported). The main
practical differences between them, and DDE, are that
a. each method returns date/time results and Yesno results rather
differently
b. you will probably find that each method deals with long text/memo fields
differently. You should consider testing that with the data you have (i.e.
don't do tests with simpler data than the data you have).
c. if you have Unicode data, only OLEDB will work
d. Only DDE can "see" some types of query (parameter queries, queries that
contain user-defined functions (i.e. defined in Access VBA). ODBC may be
able to see Union queries but OLEDB cannot unless you wrap them up in
another query
e. to access password-protected databases via ODBC/OLEDB you may have to
use VBA to specify a connection string
f. DDE wil probably work properly with any old-style wildcards (? and *)
you have in your queries. OLEDB will only work with the new ones (_ and %).
If you upgrade your database to use the new ones, you may find that DDE no
longer works with the old ones (I forget, to be honest) - i.e. if you
upgrade, you /may/ find that you can no longer use parameter queries with
wildcards.
What to do about Access 2000 format databases depends on what you are using
to get data from them. If you are moving towards a mixed scenario where some
people are using Access 2000 and some are using Access 2003, you will
probably have to keep your databases in 2000 format. If you want to use
OLEDB to access that database, you should probably consider duplicating
every query that uses wildcards and providing an "old wildcard" and a "new
wildcard" version. If everything is moving to 2003, you can consider
"upgrading" your database format and modifying all the queries - in my view,
you should always test all the known consequences of such a change before
committing to it, and of course that may involve considerable effort if a
lot of things rely on all your database-related activities still functioning
after the upgrade.
Peter Jamieson
