A Case for Temporary Tables in Data Entry

[Thales750]

Over the years I have become more and more convinced that Data Entry should be performed on forms bound to temporary tables and at the end of the transaction a query should append the records to the permanent tables.
Any thoughts on this?

 

[pbaldy]

I've done it occassionally, but not normally. I've done it when the users wanted to input in a denormalized format that I was going to normalize going into the actual table(s), and in cases where they wanted to enter multiple records and be able to balance/reconcile the group before the entries were finalized.

Why do you feel it should be done that way?

 

[boblarson]

Over the years I have become more and more convinced that Data Entry should be performed on forms bound to temporary tables and at the end of the transaction a query should append the records to the permanent tables.
Any thoughts on this?

I'd be interested as well to hear the arguments for as I've been working with Access databases for 14 years now and I've not had that same conclusion.

 

[Thales750]

I guess that I’m not satisfied with Access’ ability to reliably end transactions and delete the current record on interrupted or halted transactions.

When I first encountered this method, it was in QuickBooks. At first I didn’t like having to manually “save” a transaction, but I believe they got it right when they forced the user to update.

 

[boblarson]

I guess I haven't had the need since I have rarely, maybe once, ever needed transactional processing from Access. If you do, then maybe that would be something to consider.

Most of the apps I've ever worked on, and including the 5 big ones we use here at the bank, don't require anything but the current record to be considered when entering data.

 

[Thales750]

I'd be interested as well to hear the arguments for as I've been working with Access databases for 14 years now and I've not had that same conclusion.

You're showing your age Bob, and reminding me of mine. I switched to Access from Paradox in 1996.

 

[boblarson]

You're showing your age Bob, and reminding me of mine. I switched to Access from Paradox in 1996.

I hit the half-century mark later this year.

And I came into Access fresh without any previous database knowledge in 1997 and wound up having to figure out some Paradox stuff to help a co-worker when he went on vacation.

 

[Banana]

Over the years I have become more and more convinced that Data Entry should be performed on forms bound to temporary tables and at the end of the transaction a query should append the records to the permanent tables.
Any thoughts on this?

My experience is that there's always more than one way to skin a cat.

In some cases, I found that all I needed to do was bind a form to an ADO recordset. Other time, it was temporary tables, but most of time, it's simply directly to the table/one-table query.

In my experience, the requirement for a bulk transaction is relatively rare for two reasons.

1) By updating row by row, we don't need as many locks which is good thing for all of us.
2) By making the update more immediate, it's less likely that we'll run into deadlocks due to two users retrieving same rows at wrong time.

Overall, it's more simpler and safer to do row-by-row update. That should, however, not imply that there can never be a use for bulk transaction. I've written some forms where bulk transaction were an essential feature. More often than not, whenever a bulk transaction was needed, it was because it'd span more than one table. As an example, if the business rule was that there cannot be a parent record with no children, it would be a good candidate for bulk treatment and Access does not really make it easy to wrap such thing in a single transaction (at least not with bound forms). For those cases, I turn to alternatives already mentioned or on occasions, use a multi-table query form allowing the user to enter data for both parent & single child in a single form. This only works as an insertion and if they want to separate the insertion from updates. I know at least few other Access developers believe that insertion should be separated from updates for reasons like those.

But for large majority of data where a minimum unit would be a row, BeforeEvent suffices to validate the integrity of data and thus we don't really need the extra overhead that bulk transactions introduce.

At least that's how I see it.

 

[Thales750]

I should have clarified this better.

This particular form is for new sales at a cash register in a multi-store environment. Even without any other consideration the records will be stored locally with an automatic sync up with the main store on periodic bases. But that is a separate problem.

The problem here is there are so many unexpected problems with a cash register in a retail environment, from stopping a transaction in the middle, to changing their mind at the end, to credit cards not processing. The amount of variables to consider is staggering.

So I reckoned that was why QuickBooks did it that way. Every “t” is crossed and every “I” is dotted, before a record in any part of the transaction is completed.

Why wouldn’t I do it that way?

Which reminds me, we interfaced QuickBooks with one of our Access databases back in Jan and it was interesting how QuickBooks actually creates child records first then inserts them into a table and then updates the parent record, weird.

But a company FlexQuarters sells a product QODBC which is a rich and powerful tool to interface with QuickBooks, it even automatically does that little reverse insert routine for you.

 

[Galaxiom]

I would not consider the following the example of something on the basis that it was included in QuickBooks. In my minimal experience with it I found it was one of the klunkiest most unintuitive applications I had ever encounterd.

 

[Thales750]

I would not consider the following the example of something on the basis that it was included in QuickBooks. In my minimal experience with it I found it was one of the klunkiest most unintuitive applications I had ever encounterd.

I don't disagree. But it does make it fool proof.

 

[Galaxiom]

I don't disagree. But it does make it fool proof.

No such thing as foolproof. Fools are too ingenious.

 

[gemma-the-husky]

I think it depends on what you are doing

Say you are posting a NL journal, so the debits and credits must match.

Put them all in a processing table - and if it doesn't balance, and the user has to abort, then the temp table can just be ignored.

If you have already added items to the real tables, it becomes much more problematic, to undo the postings.

Temp tables are very useful for some processes.

 

[Thales750]

I think for POS (Point of Sale) systems it makes sense. Those folks need a very narrow path, and any mistakes forces them to fix the problem or start all over.

No final sale button pushed...no sale.

 

[stevemanser]

I feel very strongly about this topic as it has plagued every database I have written in Access and after Thales750's comments I am pleased to see I am not the only one who thinks the way Access automatically saves records when using bound forms is against the general consensus of Office programs. E.g. when a user opens a Word document, makes some changes and then exits they are always prompted with "Do you want to save changes?". This is all I need to know when it comes to designing data entry forms for Office users.

In Access, the only way to achieve this, esp. with subforms, is to have forms bound to temporary tables and to prompt the user to save the changes. Sure, you can achieve some of this functionality with bound forms using a mad combination of beforeUpdate, undo, subform onEnter, onClose, afterUpdate, onCurrent and countless other workarounds not to mention avoiding calculated controls etc. but for me it's not worth the trouble. Much better I think to present the user with a temporary copy of the data and ask them to save it if that's what they want to do!

I acheive this by having a combo box on the form which AfterUpdate gets the data and stores it in a temp table. The form recordsource is set to the temp table, and a user can save the data to the real table using a button that runs a generic field by field update routine. You code your own first, last, next, previous, delete and add buttons etc. Everything is generic so that the system can accomodate new forms or changes without excessive coding. I'll post some code snippets if anyone is interested, not only to support my viewpoint but also in the hope that the code may be improved if people like this approach.

 

[Galaxiom]

Sure, you can achieve some of this functionality with bound forms using a mad combination of beforeUpdate, undo, subform onEnter, onClose, afterUpdate, onCurrent and countless other workarounds not to mention avoiding calculated controls etc. but for me it's not worth the trouble.

Much better I think to present the user with a temporary copy of the data and ask them to save it if that's what they want to do!

The form recordsource is set to the temp table, and a user can save the data to the real table using a button that runs a generic field by field update routine.

If this appears simple it is probably because it is being done without understanding the full implications. Data can be easily lost unless specific and relatively complex steps are taken to manage conflicts. Unbound forms have the same vulnerablity.

A bound form manages conflicts where multiple users are editing the same record. Using temp tables and update queries has no intrinsic facility to detect such a conflict. The first update is quietly overwritten by the second and may result in values being reverted.

 

[stevemanser]

You are spot on, GalaxiomAtHome - no doubt you've pondered these issues in the past.

I use a counter in the table which is read when the form is opened and which is incremented on save so that if the user attempts to save changes on that record and the counters don't match then the user gets a message - very similar to the Access prompt "Another user is attempting to change the same data", however with mine I am full control of the next action - e.g. reload or overwrite, timeout etc. I suppose you could note the username and workstation of the last updater too. A simple version is like this:-

On save...

If Me_Form.Form("intLockCounter") <> RealRS(i) Then
If MsgBox("Someone else editing this record. Reload this record?", vbYesNo) = vbYes Then

...and later on in the save...

If RealRS(i).Name = "intLockCounter" Then RealRS(i) = RealRS(i) + 1

Alternatively, see Albert D. Kallal (Access MVP) for his roll-your-own locking system ideas, he seems to be well clued-up on this type of thing.

Incidentally, my inspiration for all this came from DataEase v6.5.2 - it works incredibly well over the network, works with multiple users, prompts the user to save changes before writing any form or subform data, issues the id number at the end of a save and generally does the things I wish Access did. Unfortunately, it's not VBA or SQL compatible and is landlocked (no ODBC support), so I wouldn't want to use it for my customers.

 

[gemma-the-husky]

to be honest, I struggle to see the point of having a save button for general use.

doing this presumably means you can't use datasheets at all. It also slows input down as you need to add an extra click on every entry.

And importantly, although it may be the normal functionality for excel and word, it isn't the normal functionality for access. The difference is that access is multi-user out of the box, and needs to expose your data to other users.

The timing of record locking is often critical and should be kept to a minimum. wg - In your example, if a user opens a record, and never bothers to click the save button, does the lock stay on permanently?

As I say, I can see this being a useful technique in some cases, but not all.

 

[stevemanser]

When an Access form is bound to the actual recordset you are exposing the user to real data....argh, and a lock on that record. If a user changes the data in that form or a subform the record is automatically saved, yikes.

The best thing about going with unbound forms (i.e. bound to temp tables) is to be able to cancel changes and to enjoy a vast reduction in code, like getting rid of beforeUpdate, Undo, on_this or on_that, etc, not to mention an end to mousewheel scrolling, paging up and down, multi-user lock problems, and a multitude of other issues like updating calculated controls or having to avoid subforms or having to use .enable or .locked all the time.

Don't you get all these problems with your databases with multiple users? How do you get around these issues?

 

[Simon_MT]

Isn't that the point of a database - users seeing real data. I have a simply method particularly with Invoices, entry can be done in stages:

Initial state: Profroma
Once Approved: Confirmed
Subsequent Change: Proforma
Needs Approval again
Updated into Ledgers : Update Non-maintainable state.

Unless you are entering / altering Invoice information you can only use Enquiry screens.

Record locks are so rare and once a stock item has been sold then unless it is going into the Secondary Market it will be with a Client, not available and you can't sell it.

There made be instances with Clients being simultaneously updated but sales personnel are fairly territorial over their sales so generally one person deals with that client.

Exhibitions are again under the control of one person, who curates the show, the only time conflict can occur is any re-pricing.

The point I'm simply making is once understand your environment, you know when to take your hand off the brake.

Simon