Dear Richard:
The typical best way to go is to first have some unique key in the
data from the source table. This is actually essential. Based on
that unique key you can determine which rows from the source table
have already been added to the destination (Access Database), which
have already been added but have changed, and which have been deleted
in the source table.
Once you have performed the insertion, update, and deletion queries
the data will be synchronized. But without some unique way to
identify the source rows and compare them to the destination table
there is really no way to do this.
This does not mean there has to be a formal primary key in the source
table, but it means there has to be some equivalent of that.
The theory is that every row in a table has some identity - that is,
some way to identify it. This identity can consist of one or more
columns. It can be a natural key or a surrogate. But whatever you
choose for that identity, it must be promulgated throughout the life
of that row, including its existence in other databases.
If that identity is based on a natural key, then it is subject to
being changed. That means it cannot be tracked in the destination
database. A change in any part of the natural key would result in the
row in the destination table being deleted and a new row added in its
place. While this is not necessarily destructive, it can cause
problems with and table in the destination database that are made
dependent on the destination table. Referential integrity will not
permit you do delete the row in the destination table in this case,
unless you drop referential integrity each time you update the
database from the source. However, this solves nothing, as such a
change would prevent re-establishment of that referential integrity
after the update.
In this case, having a surrogate key in the source database would be
preferred. This give the destination rows an unchanging identity on
which subsequent changes to the natural values has no effect.
Some of those with whom I have debated the issue of surrogate keys for
relationships may be surprised that I do advocate them when crossing
the line between databases. The fact that this is the ONLY way to
readily accomplish your needs is something I must definitely concede.
The ability to design relationships on natrual keys is absolutely
dependent on the ability to cascade those changes, and this cannot be
done across databases as the relationships cannot be designed and
enforced in that case.
While this digresses a bit from your situation, it is actually an
essential piece of what may challenge you.
Do you have any tables in the destination database that are dependent
on the source data?
I understood from your original question that there is no primary key
in the source database. I take it then that there may well be no
surrogate key.
If you have no dependent tables on the destination table, then you
could probably just clear that table and insert all the source rows
fresh each time. That would be sufficient. For efficiency's sake,
you could drop any indexes on the destination table before doing this,
then re-establish them afterward.
If you do have dependent tables, then some study of all I have written
here, and perhaps some additional discussion, may be of assistance.
Another long "windbag post" from me!
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
