asked on

How can i identify what data has changed on an Access 2003 form

Hi,
I'm a bit stuck on this issue and wonder if you could help? we have field workers who do not have access to the companies Servers and need to be able to download data from the live SQL Server datbases and work with the data offline on their tablets and then update the live database again at the end of the day. If is not possible for them to be logged on at all times due to issues with site locations etc. I would like to be able to trap any records that get changed during the day, probably on only a handful of specific tables and then to be able to run an update query to push the data back into the live system when they log back on. I'm imagining something that would know when a form has been updated and which field in an underlying table has been changed. I think I would then record that change to a table which would hold the name of the table and field to update along with the value to change. I'm stuck on knowing how to identify the control that has been updated.
Any help as always would be very much appreciated

ASKER CERTIFIED SOLUTION

omgang

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

omgang

Here's a screen shot of a log table displayed in Excel. The data is from an Access db. Excel is the front-end interface for the user. Each time a record is added/changed I capture the specifics. You could do something similar and use the log table to update your master db table records.

OM Gang
log-wksht.jpg

SOLUTION

Jim Dettman (EE MVE)

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

omgang

Forgot to mention, the felds that have _OV at the end of the name are the Old Values. If a new record was added I write 'New Record' into the log file.

OM Gang

JulesII

ASKER

Thanks to both Omgang and Jim for your lightening responses. Both of you have given me food for thought. The field workers are project managers and as such are assigned specific jobs to work on so the risk of other overwriting their data is minimal. that said there is at the office a group of call handlers who can change data to any part of the live database so there needs to be some thought into who is going to be the right person to update. I'll have a look at replication as its something I've never had to consider before.
thanks once again for your quick resonses. Would it be ok however to leave it open for a bit longer to see if anything else comes to light?

Jim Dettman (EE MVE)

<<Would it be ok however to leave it open for a bit longer to see if anything else comes to light?>>
Sure...
Jim.

JulesII

ASKER

Guys I've split the scores between you as you both gave me very valid adivice on how to go about solving this issue. I'm half way into it now and have used tips from both of you. Thanks for the very quick response. I've been away on a camping trip so sorry for the delay in closing this question.