Bob,
I don't know how many, how big or interconnected the tables are that
are involved, but I think that when more than a few independent tables
are involved your approach could end up difficult to manage. Think of
how to initialize or resync after database or connection problems, how
to implement changes in what to sync, how to adapt to heterogenous
systems after a merge of companies etc.
If you could live with a scheme with a sync once or a few times every
day then I could have a much more generic solution not based upon any
code within the involved applications.
I developed a flexible table-sync tool (one way) based upon Access
select-querys. Your solution can be set up in a seperate mdb-file with
linked tables to both systems through ODBC or direct. The generic code
is in a Access menu add-in and provisions are made for safe unattended
operation by safe storage of needed database passwords and one-click
generation of a Windows-scheduler task.
It has a wizard to accelerate a sync-definition. Fine-tuning of the raw
queries can be done in the Access-Query-designer and eventually VBA can
be used for specialized datatype transformations . Statistical logging
of additions, modifications or deletes is done and
field-transaction-logging is an option for analysis with or without
writing the record-changes.
The user-interface and helpfiles are still written in dutch, but if you
are really interested I could make an english version as well.
The SQL- and VBA-performance is so good that I rejected the plan to
make a compiled VB6-version for the synchronizer-modules. More than
1000 record-modifications in Oracle throug a network-connection for a
very complex query (15 tables in 3 heterogenous databases) and a VBA
soundex-function processing 1 column in 12000 rows with 18 other
columns in 2 minutes was much better than I ever expected.
I use the tool now for more than five years for a few unatteded
sync-operations and still develop features to make it as generic and
powerful as possible based on the underlying jet/dao/odbc-technology.
Other nice aspects are:
* the end-to-end on the fly modification approach without import/export
files or transaction logs.
* the optimistic locking (busy ? next time I try again, no direct
interference)
* the powerful datamodel-transformation possiblities throuh SQL to
support very different systems involving commmon or translatable
data-content.
Depending on the involved data there are possibilities to optimize
daily updates effectively by filtering out data on a date provided by
the Head-office to be able to resynchronize a longer period if ever
needed.
Could this be a useful approach ?
Marc
Bob wrote:
I have been asked to modify an existing Access database so that it can
update a MySQL databse in head office from multiple MS Access databases in
branches. The idea is that when the HO database is available then any
changes made in branch will automatically update HO but when HO database is
not available then changes are 'batched' until it is available and then
update. (There is a Virtula Private Network involved in this). The update
is not really time critical so I am thinking along the lines of batching
updates anyway and then trying to send them every few minutes. I figure
that it should be a relatively easy job to write a generalised function that
is called by every form or subform 'on change', 'on delete' or 'on addition'
event (with table name and key field name and value and type of transaction)
which would then keep a record of the info and then send the table/s as
required - possible when a connection is available.
I don't feel too worried about the technical side of this operation but
don't really like re-inventing the wheel and so am wondering if anyone has
generalised code that will do most of what I want to do. Happy to pay a
reasonable commercial price for the software if necessary longs I can us it
in other databases I sell.
PS DO NOT want to use Access Replication!!!! !!
Bob Collinson
Colsoft Pty Ltd
13 Watson Avenue,
Rose Park, SA, Australia
Phone/Fax: International 618 8331 3713 Australia 08 8331 3713
