Access 2000: Version comparison

[Tara via AccessMonster.com]

Hi there -
I'll do my best to explain my dilema.

I'm using Access 2000. In this database, there is one table with about 150
columns of information, and 206 rows. There are numerous queries and
reports. I've set up input forms for staff to enter data easily. There are
8 staff members.

My boss wants to ensure that data entered into this database is correct
right off the bat. She's asking for a program or code to do the following:

- Auto-generate a form immediately after someone has updated the database
with new data
- The report should compare the latest backup version of the database with
the current "live" version
- The report should take the fields that were updated in the "live" db, add
them in the report, and do the same from the backup so she can compare the
old vs. new data
- If the data is incorrect, she wants this program to automatically
"reactivate " the latest backup copy as the live version

I need to know if this sort of thing is possible. If so, how long would
something like this take to do? Should I hire a contractor to custom-build
this? Is there an existing program out there that can do this?

Any advice or suggestions would be helpful.
Thanks a million!
Tara

[Steve Jorgensen]

If this is on a per-record basis, it would make more sense to print the report
before saving the data, then make the user confirm that it is correct before
allowing the save to complete rather than printing the report after saving,
and rolling back the entire database if it was wrong.

You could create an unbound report, and copy the values from the form controls
to the report controls so that what is printed is the pre-saved state of the
data.

On Wed, 27 Apr 2005 14:06:32 GMT, "Tara via AccessMonster.c om"
<fo***@nospam.A ccessMonster.co m> wrote:

Hi there -
I'll do my best to explain my dilema.

I'm using Access 2000. In this database, there is one table with about 150
columns of information, and 206 rows. There are numerous queries and
reports. I've set up input forms for staff to enter data easily. There are
8 staff members.

My boss wants to ensure that data entered into this database is correct
right off the bat. She's asking for a program or code to do the following:

- Auto-generate a form immediately after someone has updated the database
with new data
- The report should compare the latest backup version of the database with
the current "live" version
- The report should take the fields that were updated in the "live" db, add
them in the report, and do the same from the backup so she can compare the
old vs. new data
- If the data is incorrect, she wants this program to automatically
"reactivate " the latest backup copy as the live version

I need to know if this sort of thing is possible. If so, how long would
something like this take to do? Should I hire a contractor to custom-build
this? Is there an existing program out there that can do this?

Any advice or suggestions would be helpful.
Thanks a million!
Tara

[Robert]

Tara,

There are programs that compare files and you can also write one if need be.
A procedure such as the one you describe is probably doable, and more
difficult for a newbie.

The real question is why your boss wants to do this. She wants to compare
old data with new data or old data with a copy of itself? Or new data minus
the new records with the old data? It sounds to me like it's just
unnecessary. Not sure though. Maybe you could explain a little more if I'm
off track. If not, talk her out of it.

And why do you have 150 columns of data?

Robert

"Tara via AccessMonster.c om" <fo***@nospam.A ccessMonster.co m> wrote in
message news:43******** *************** *******@AccessM onster.com...

Hi there -
I'll do my best to explain my dilema.

I'm using Access 2000. In this database, there is one table with about 150
columns of information, and 206 rows. There are numerous queries and
reports. I've set up input forms for staff to enter data easily. There are
8 staff members.

My boss wants to ensure that data entered into this database is correct
right off the bat. She's asking for a program or code to do the following:

- Auto-generate a form immediately after someone has updated the database
with new data
- The report should compare the latest backup version of the database with
the current "live" version
- The report should take the fields that were updated in the "live" db,
add
them in the report, and do the same from the backup so she can compare the
old vs. new data
- If the data is incorrect, she wants this program to automatically
"reactivate " the latest backup copy as the live version

I need to know if this sort of thing is possible. If so, how long would
something like this take to do? Should I hire a contractor to custom-build
this? Is there an existing program out there that can do this?

Any advice or suggestions would be helpful.
Thanks a million!
Tara

[Tara via AccessMonster.com]

Hi Robert -
Thanks so much for your reply.

I also believe it's unnecessary, but she's adamant that the database have
this feature.

To clarify, she wants to compare old data (from the backup) with the new
data that was just entered. She only wants to see those fields that have
been updated.

I've found a few program out there that can do this, but I think it's
unlikely we have the budget for something like that. Hard to say. I'll do
my best to talk her out of it. That's the easiest solution, obviously.

I have 150 columns of data because there's so much information to be
recorded. It's all in one table, rather than in multiple tables, because
all the information is related to a unique agency name. It was designed
this way when I got here, sadly.

Again - thanks for your help.
Tara

--
Message posted via http://www.accessmonster.com

[Stuart McGraw]

"Tara via AccessMonster.c om" <fo***@nospam.A ccessMonster.co m> wrote in message
news:43******** *************** *******@AccessM onster.com...

Hi there -
I'll do my best to explain my dilema.

I'm using Access 2000. In this database, there is one table with about 150
columns of information, and 206 rows. There are numerous queries and
reports. I've set up input forms for staff to enter data easily. There are
8 staff members.

My boss wants to ensure that data entered into this database is correct
right off the bat. She's asking for a program or code to do the following:

- Auto-generate a form immediately after someone has updated the database
with new data
Not sure what "auto-generate" means. How often do people do updates?
Are there multiple users doing simultaneous updates?
- The report should compare the latest backup version of the database with
the current "live" version
Do you have to compare two database files, or would comparing an
"old" and "current" versions of the table (within the same database)
suffice?
- The report should take the fields that were updated in the "live" db, add
them in the report, and do the same from the backup so she can compare the
old vs. new data
- If the data is incorrect, she wants this program to automatically
"reactivate " the latest backup copy as the live version
Not sure what "automatica lly" means. If she does it, it is "manual", yes?
What does "reactivate " mean? Replace an .mdb file with a different
one? Replace the table with an earlier copy in the same database?
Replace the changed values in the table with the old values?
I need to know if this sort of thing is possible.
I will guess that you are want to audit the changes to one table.
Many databases have a requirement to record an audit trail
of changes. Do you want the report for your boss to list every change
made, by who, and when? Or just a report (at the end of the day for
example) that says these fields had these values this morning, and
now they have these values? The latter would be quite simple to do
I think. The former would require you to add code to every form that
can update the table -- some work but probably straightforward .
If so, how long would something like this take to do? Hard to say without more details. In the simplest case I can imagine,
a few hours. But my experience is these problem are always way
more complicated that the initial description implies.
Should I hire a contractor to custom-build this? How good are your Access skills?
Is there an existing program out there that can do this?

The way you described it, I get the feeling you are thinking of copying
your database and comparing the old and new databases. There are
products that do this but I think they are mostly aimed at developers
and focus on documenting changes for reverse engineering/refactoring.
I suspect they are overkill for you and not really what you want. My
approach would be to either make a copy of your table in the same
database and some code or a query to compare the old and changed
tables, or a table and code to record each change to the base table
as it occurs.
Of course, I may also have misunderstood what you want...

[Steve Jorgensen]

On Wed, 27 Apr 2005 17:51:00 GMT, "Tara via AccessMonster.c om"
<fo***@AccessMo nster.com> wrote:

Hi Robert -
Thanks so much for your reply.

I also believe it's unnecessary, but she's adamant that the database have
this feature.

This sounds like a classic case of the manager prescribing an implementation
rather than describing a requirement and letting you determine the best
implementation. The way to deal with this situation is to echo back the spec.
you think you hear behind the suggested implementation and see if you got that
right, then suggest the simpler, cheaper, more reliable implementation you see
for it.

If your boss disagrees with your interpretation, that simply starts a
conversation in which the true requirement and the best plan to meet the true
requirement can be unearthed. Believe me, your boss is not intentinally
asking you to waste time implementing an overengineered spec that will take
more time than it should and be dangerous and trouble-prone to operate.

The requirement I read from between the lines is that after the user says
they're done with the record, it is printed out, and it's only kept in the
database if it's still deemed correct after printing the report. That
requirement does not have to be implemented by saving the bad change, then
rolling back the entire database to reverse it. It can be better implemented
by printing out the record and confirming it -before- committing it to the
database.