Transactions Tables?

[TVining]

I'm hoping someone has done this before.
I have a database in MySql that many members access via PHP.

However, the new person to update this database does not have the drive or desire to learn anything new.

What I would like to do is build an access database where he makes the changes in a familiar environment (access) and sends me a table with all the changes he's made, but ONLY the changes he's made.

My current hosting plan makes it way to difficult to link the tables, so offline updating is the best bet. In addition, they only support MySQL and PostGres.

Can I use Access to build transaction tables for me to receive from him? (A table with the record Number, name of the field changed, and new value would suffice) I.E. {167,Home_Address,155 North 5th Street}

Or can anyone here think of something more simple?

I'm probably really overlooking the simple solution here.

[NeoPa]

I guess it's possible.

What stages of the process do you have control over? Are you about to design the Access database that he'll be using?

[TVining]

Yes, I've started by importing the tables and making his forms, but know I will probably trash them. :-P

Even more simple, could I just mark each record that he changes, and have him export those, and then I could just replace the entire MySQL record. I'm up for that. Just make it so if ANYTHING on a record changes, a YES/NO field is triggered.

Wow, My mind wanders- Would it be feasible to import a table twice (tbl_main and tbl_main1) he changes an address and then when he's done, I compare all data in the table and build the transactions based on any difference?

Arrgh...so many possibilities. ACCESS is just too powerful. Where's my DB3 from DOS days?

Tony

[TVining]

Incidentally, I have full access to the MySQL, and am fairly decent at MS Access, but just need a good kick in the pants to get moving the right direction.

Tony

[NeoPa]

Welcome to Bytes!

Rereading your post I guess you are, and you also have complete control over the design of the database he'll be using.

OK. Consider maintaining a table which would contain the original data. This would be the same data as the main table at the start point of the whole process. Each update export resets to the start point again of course.

The Export process would have to build up a third table of all differences you need it to log. This would effectively be a table of deltas, that would be exported and sent up to the main web-based (I assume) MySQL database. This process must include resetting the copy table to bring it up-to-date with the latest data. That way the next time the process is run only deltas from that point are registered.

Does this help?

[NeoPa]

Well Tony, it looks like you beat me to some of these ideas :)

Let me know if you have what you need to get going with.

[TVining]

Okay, now how do i compare each and every field of each and every record to find the deltas? :-P

[TVining]

ahh, I am going to try this: Compare each field the same as a duplicate field, and build the table (Append query) based on only that data which is changed.

[NeoPa]

@TVining
I'm afraid with such an open question you can only get an open answer. "You process through the various values in code". Not remotely helpful I know.

If you want a more helpful answer then you will need to rephrase the question as a more specific question, rather than something akin to "Please tell me how to do the whole job".

I'm happy to help you along, but remember whose project it is.

[NeoPa]

@TVining
Ah. I understand you better now. The previous post was your initial (somewhat perturbed) response.

Have a crack at it. Give me a shout if you get stuck (although I'm late to bed now so may not respond until tomorrow).

A possibly helpful tip is to use the "For Each" construct to go through the available fields in a table.

Update us with how you get on.

[NeoPa]

Remember also with deltas, that it's important to log deletions and additions as well as simple amendments.

How many professional software vendors still need to learn that lesson?!