I have an access Database. It has several tables, but one (possibly
two) in particular, need to be kept in Sync with an authoritative
source of information on an SQL Server.
The table in question is the Customer Details. i.e. customer ID NUmber,
Name, address, phone number etc.
While within the Access application, we manage a bunch of other data,
this really needs to be aligned with the info on the SQL Server. As
such, we won't allow changes to the local customer table. IN fact,
initial setup will be an import from the SQL Server. However, for
ongoing data integrity, we need to "check the SQL Server" for updates,
whenever we start up the access application.
How, the situation will exist where there may not be connectivity to
the SQL Server Database. SO, in effect the Access Detail table, needs
to be a "cache" of the SQL server table. (we are talking about around
3000-5000 entries)
So, the line of thinking, is
1. application starts up
2. attempts connect to SQL DB.
3. If it can, it downloads that data to the access application.
(there are two thoughts - download the entire table irrespective
from sql to access or run through all records and look for changes, and
download. I would err on the side of the first...
If it can't connect, it uses it's most recent copy of the downloaded
table, sitting in the Access Application.
Does this make sense - can anyone think of other ways to approach this,
and better still, codelets that do it...
q