Best Practice Advice: How to find out when a record is updated in read only BE (1 Viewer)

GBalcom

Much to learn!
Local time
Today, 10:53
Joined
Jun 7, 2012
Messages
459
I'm creating an application that will look into our ERP back end (Borland, through read-only ODBC), and find new or changed records in a specific table, then take actions, such as adding them to our google calendars, or pushing that information to a webhook for Zapier to do something with.

I'm looking to brainstorm the best way to alert myself to when these records have changed. I tried Data Macros, but they only work with Local tables. One option could be a local table, where I house only the last 100 or so records every time it closes, that I can compare the live data to, and find any differences.

I will also have a log table, where I will document every time I take action on a record. I can compare the time stamp on this to the live data, and find any new records, but it doesn't help me find any updated records.

The ERP has an alerts table, that I've used in the past to find updated records, but it relies on another application, which has proven to be buggy at times. I'd prefer to keep all this logic under my ms access roof if possible.

So, what have the experts on this forum done when faced with this type of situation?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:53
Joined
Feb 19, 2002
Messages
42,971
If the ERP does not flag each record with a last update date, then there is no good solution.

The only way you can identify which record changed since the last time you downloaded data is field by field, record by record.

Perhaps you can convince the PTB to add triggers to the ERP database to create log records for you. This request will almost certainly be denied but you never know, you might get a pleasant surprise.
 

GBalcom

Much to learn!
Local time
Today, 10:53
Joined
Jun 7, 2012
Messages
459
Thanks Pat,
Believe it or not, this is an off the shelf ERP, so we don't really have any control over the underlying db. It's a shame, as it makes it harder. Somedays I think it'd be easier to re-write the whole thing myself....


Given above, do you think the route of copying the last 100 or so records to a local table upon closing, then comparing them to the live data next time is prudent? or would you do it some other way? :banghead:

This app will run automatically on the server, at some given frequency.
 

Mark_

Longboard on the internet
Local time
Today, 10:53
Joined
Sep 12, 2017
Messages
2,111
The '100 record' approach would require your ERP to have sequential records OR a unique, auto inc key. If it does, you should be able to tell quickly when something NEW comes in, but you won't catch updates.

Does the underlying DB have a "Last updated" field or something similar? Even though YOU don't have rights to do so, do you have a DBA who does that could add something like that (and a stored procedure to update the date any time the record is updated)?
 

GBalcom

Much to learn!
Local time
Today, 10:53
Joined
Jun 7, 2012
Messages
459
Hi Mark,
It does have a sequential key. There is no last updated field. It is a 3rd party out of the box application, so we only have Read access to the DB via ODBC. No way to add it, unfortunately. I wish it was in MS SQL, then I could get into it a bit more. I have admin rights to it, but not the right tools :)
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:53
Joined
Jan 23, 2006
Messages
15,364
@GBalcom,

Have you tried talking to the product supplier? Is there a user group for the product?
Any product documentation?
Can you point readers to Product overview and marketing materials?

Are there procedures within the product to extract files/records? If so, you might be able to concoct something after some analysis of "updated records you/users are aware of". Just a guess for consideration.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:53
Joined
Feb 28, 2001
Messages
26,999
I always trot out the Old Programmer's Rules for cases like this, but here I have to refer to a corollary of a rule:

Old Programmer's Rule #2: Access won't tell you anything you didn't tell it first.

Corollary to rule #2: If you don't tell Access (or any other DB) that you will want it to return something, you usually won't get what you wanted.

In this case you are seeking a "metric" that will let you determine that something has changed. Here is the critical question. OK, you have a sequential key. Does it change in a predictable way after an update to a record? Or is it invariant? (If it is a valid PK, it SHOULD be invariant...).

If it does change, you just record the highest key value at the end of a scan for changes and find changes with higher numbers next time you scan.

If it does not change, you could be up the proverbial creek with no sign of a paddle unless you are ready to keep a "shadow" DB that tracks something about your master DB to let you find the changed records.

My next question is, how big is that table you wanted to monitor, in terms of the number AND SIZE of the records? If it is small enough, a "shadow" database would work.

If it is not too many records AND that sequential key doesn't change after an update, you could try something like generating a hash of the record and associate THAT with your key in a local table. Then the next time, you could regen the hash for each record and easily identify which records have changed. But that only works, of course, with a relatively small number of records. If it is too many records, it rapidly becomes too much of a challenge.
 

GBalcom

Much to learn!
Local time
Today, 10:53
Joined
Jun 7, 2012
Messages
459
Jdraw,
All product docs are directed towards the user interface. No user group, small niche product. I know the developer, but, I don't think he'll oblige.

I don't want to give him any bad publicity by listing it here.


Doc_Man,
I'm strongly considering a "shadow" db, or at least a "shadow" table. A record in this one table would only change during the parent jobs lifecycle. We only ever have at most 100 projects active at any given point. I could even to go 200 to be extra safe. Maybe put that recordcount variable into the program options to ensure a safe future of the application.

So, if we can walk down this hypothetical road, I would query the latest 200 records ouf the live table, place them in the "shadow" table, then compare them next time the application runs, to look for any changes. It may not be that bad, because there are only 3 fields I would need to look for changes in. Does that seem reasonable?
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:53
Joined
Jan 23, 2006
Messages
15,364
I don't think he'll oblige

But you bought his product. Certainly a question or 2 seeking advice should not be a show stopper. Are you suggesting that a purchased product has 0 support?

Whether shadow database or purposeful extract, I think we're in the same concept area.

You won't know for sure until you try. And that goes for speaking with creator or shadow database.

I'm a little surprised that there seems to be "no way in" (for an administrator) to a purchased product.

Any way you know your environment and the history of the product better than readers. You were seeking some advice and I gave what I thought I might do given the situation.

Good luck and keep us up to date with whatever course your take.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:53
Joined
Feb 19, 2002
Messages
42,971
If you know the developer, you might be able to influence a change. Adding a last update field to each table is pretty easy as far as changes go. First you add the column to each table. Then you add a trigger to populate it.

Granted, BE changes can be difficult to propagate but this particular change can be easily implemented by tuning a script to make the BE modifications.
 

Users who are viewing this thread

Top Bottom