Update logging possible?

  • Thread starter Thread starter FP1
  • Start date Start date
F

FP1

Using the jet database (not my choice), I'd like to know it anybody has any
ideas as to how to determine what/when tables are being updated. In
SQLserver I could set a trigger, but not Jet.

The reason is that I'm linking two back-end database together and I need to
keep data synchorized and trying to find out what tables are being updated
where is a nightmare in a large, complex application. Many of the record
sources are queries or changed on the fly quit often, and some are with
update and insert statements dynamically constructed. Any ideas?
 
Using the jet database (not my choice), I'd like to know it anybody has any
ideas as to how to determine what/when tables are being updated. In
SQLserver I could set a trigger, but not Jet.

The reason is that I'm linking two back-end database together and I need to
keep data synchorized and trying to find out what tables are being updated
where is a nightmare in a large, complex application. Many of the record
sources are queries or changed on the fly quit often, and some are with
update and insert statements dynamically constructed. Any ideas?
Click to expand...

Tables have a modification date property, but it doesn't always get
updated when a record changes. It's best to keep track of updates
yourself: Create an update table with fields Updated and TableName.
Everytime someone adds a new record or changes an old one, call a
function to update the update table.
 
Tables have a modification date property, but it doesn't always get
updated when a record changes. It's best to keep track of updates
yourself: Create an update table with fields Updated and TableName.
Everytime someone adds a new record or changes an old one, call a
function to update the update table.
Click to expand...

Thanks, but that's the problem: I don't know when a table is updated and
I need to catch *when* it's updated so I can make updates to a linked
table with values from the current form. Right now, I'm just grunting
through it, but I'm worried I'll miss a few, which wouldn't be good
because this is a legal-related system and ths important not to lose
data. (not that it's ever a good idea!)
 
Thanks, but that's the problem: I don't know when a table is updated and
I need to catch *when* it's updated so I can make updates to a linked
table with values from the current form. Right now, I'm just grunting
through it, but I'm worried I'll miss a few, which wouldn't be good
because this is a legal-related system and ths important not to lose
data. (not that it's ever a good idea!)
Click to expand...

Not sure I understand here: are you storing two redundant copies of the data
in two different tables - or even different databases? If so, that's the root
of the problem!

John W. Vinson [MVP]
 
Not sure I understand here: are you storing two redundant copies of
the data in two different tables - or even different databases? If so,
that's the root of the problem!

John W. Vinson [MVP]
Click to expand...

It isn't a problem, it's just what we need to do. Example

Table A (database A, linked, proprietary)
PK int
field one
field two
Table B (database B, linked,locally developed)
FK int
field three
field four

Fields one,two,three and four are on a form from a cross-database join
query. When the values on the form are updated, tables A and B will be
updated, but in the case of an insert, the FK will NOT be updated. Trouble
is that table A can be one of several similar tables depending on
conditions, sometimes a table, sometimes a query and (the real trick)
sometimes not bound to the form at all, but constructed in form code and
executed with docmd.runsql.

Essentially, we are extending a proprietary system to accomodate local
demands.

The original question, which I hope is clearer, is there any way I can trap
changes (instantly) to any of the tables? As I mentioned, what I need is a
real trigger, but this is Jet. Any non-brute force way around this?
 
is there any way I can trap
changes (instantly) to any of the tables?
Click to expand...

:-{(

Not that I know of. Doesn't mean there isn't, but I've never heard of one.


John W. Vinson [MVP]
 
FP1 said:
The original question, which I hope is clearer, is there any way I can
trap
changes (instantly) to any of the tables? As I mentioned, what I need is
a
real trigger, but this is Jet. Any non-brute force way around this?
Click to expand...

Unless I'm missing something obvious, which is entirely possible, you can
track changes using code in form events. Is there any reason you can't do
this?

Keith.
www.keithwilby.com
 
Back
Top