Data override in access

[Charles]

Hello

I am facing the following problem: I have an Access database that will
be linked to an external database to get a set of tables. This
external database is updated regularly, and I need to be able to keep
my data up to date.

Now, some information are simply not captured in the external
database, which I will store in my database. That's the easy part. My
problem, is that some information is also incorrect in the external
database, and I want to be able to override certain data contained in
the external database with some data I will have put and stored in my
database.

And it get worse: it may happen that only one field for one specific
entry in one table needs to be overridden, but the other fields of the
same entry are fine. I do not want to overide all the fields of that
entry, just the one that is wrong, so that the other keeps getting
updated even if I do not update my override.

I was thinking of two possible solutions. One is to have "shadow
tables" that would have the same structure as the external DB, and be
totally empty except for the overridden datapoints, and when I am
running a query, I need to replace the data by its equivalent in the
shadow table whenever the shadow table is populated. Or alternatively
having a table containing 3 fields, one with an ID corresponding to a
specific table/field name to be overridden, another ID for the entry
to be overridden, and last field for the value to use for the
override.

I am relatively new to access, so I am not sure if there is not a
simpler way to deal with overrides. Would anyone have a better
solution?

Thanks in advance
Charles

 

[Pieter Wijnen]

The best would naturally be to fix the erranous data in the external Db <g>
I would personally use the seccond approach, albeit probably with one shadow
table per external table.
a note of warning, things may get syrupy pretty quick

HTH

Pieter

 

[Charles]

Fixing the external DB,... wishfull thinking.

Thanks for the answer!
Charles