A Case for Temporary Tables in Data Entry

[gemma-the-husky]

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?

no, you don't get problems with multiple users, with bound forms - you have two basic record locking choices with access, but you could also implement your own system, as you have with sentinels/markers etc in records

access's 2 choices
pessimistic locking and
optimistic locking (default)

with pessimistic locking, access tries to acquire an actual lock on the record to prevent other users reading the record while the lock is in place. my understanding is that because of cluster size/granularity and locality issues - access will actually lock a page(s), rather than a single record so other items near the target record can also become locked. For this reason, and for general performance reasons, it is generally critical to maintain locks only for the minimum time possible, to avoid other users being locked out. You also need a way of being able to recover from a deadlock situation, so a purpose built locking system would need a way of releasing any locks it has already obtained, if it cannot obtain all the locks it needs to complete a transaction.

with optimistic locking, access allows any number of readers/writers to see a record simultaneously. Clearly the only problem occurs with writing changes, as reading is immaterial to this process. so in this scenario, before access writes a record it re-reads it, locked, to see if it changed since you originally read it. If so, it disallows your change and gives you the standard "another user changed your record". Generally, I have found that when I get this message, it's because of a programming error, as you can get this message if you yourself have the same record open in multiple forms.

The truth is, in many years programming with access, I have never had to use anything other than optimisitic record locking.

And because managing record locks is so very complex, I prefer in general to leave it to the database manager (ie the database engine) to do this behind the scenes, as it were.

-----
This is really nothing to do with temporary tables. I use temporary tables, when they provide an easy way to manage a complete process, that a user might want to cancel. Sometimes I can achieve this instead by managing flags in a table.

 

[stevemanser]

Thanks gemma-the-husky and Simon_MT, that's locking sorted. Can I ask you about rolling back forms and subforms, I am really struggling with bound forms when it comes to cancelling changes.

 

[gemma-the-husky]

a user can just press escape to undo what he has done.

 

[stevemanser]

OK Thanks, I'll try that.....

 

[stevemanser]

OK, the good news is that the escape key worked for the main form but bad news is it didn't seem to work on the subform. Once I move to another "record" in the subform it appears to have saved the data on the preceding subform record. Is there a way that I can undo the entire record change?

 

[gemma-the-husky]

no - once you move off a record to another record it saves,

if you include record selectors you can see the record changing from dirty to clean - you can only undo unsaved changes in a dirty record

 

[stevemanser]

Yes, I know. That's why it turns out you can make A Case for Temporary Tables in Data Entry as suggested by Thales750. In an environment that uses a form/subform interface where you need to cancel changes to the entire record/subrecord and to prevent gaps in your order number sequence, this is exactly what is needed. The attached database demonstrates temp tables being used and should be split into front-end / back-end and used on mulitple machines to demonstrate locking.

 

[Galaxiom]

Thanks for the example. However it does demonstrate the suggestion of relative simplicity over deleting records from live tables is certainly not supported.

In an environment that uses a form/subform interface where you need to cancel changes to the entire record/subrecord and to prevent gaps in your order number sequence, this is exactly what is needed.

There are other simpler strategies to deal with this requirement.

I also note that the temporary tables are being created in the Front End. Personally I believe this is a bad practice and temporary tables should be put in what I call a Side End. Search the forum for Side End if you are interested in that discussion.

BTW Why would you include "WHERE 1=2" in an SQL clause?

 

[stevemanser]

However it does demonstrate the suggestion of relative simplicity over deleting records from livetables is certainly not supported

I don't understand this comment - please rephrase.

There are other simpler strategies to deal with this requirement.

Please let me in on them!

BTW Why would you include "WHERE 1=2" in an SQL clause?

For speed - one database I am supporting has over a million records. The WHERE clause speeds up the SELECT INTO (make table) statement.

 

[Simon_MT]

It is rare that I have data entered on a subform, any main record information is on a proper form and guess what - there can be no deleting records. If there needs to be any deletions there will be a Delete Query and locked down to a form and on a button. I even have buttons to add records attached to a subform.

There are instances where careless entry has damaged a record but how else are users going to learn. We could lock all the keyboards away and there would be no data integrity issues.

Personally, the sequence of entry is not my concern, in fact, I know one instance where a number of IDs are deliberately reserved, new records are entered after that block. The reserved records have been updated at a later date. Obviously, autonumbering is not used.

Simon

 

[stevemanser]

In an environment that uses a form/subform interface where you need to cancel changes to the entire record/subrecord and to prevent gaps in your order number sequence, this is exactly what is needed.

There are other simpler strategies to deal with this requirement.

Would love to know what these simpler strategies are.

 

[Galaxiom]

Would love to know what these simpler strategies are.

Allocate the sequential number at the last possible moment. The BeforeUpdate Event of new records.

 

[Galaxiom]

BTW. If I needed to enter data in that way I would not use temporary tables.

Instead I would use disconnected ADO Recordsets and add the records to the main tables in a code loop. Disconnected ADO Recordsets don't generally have a disk footprint.

 

[stevemanser]

BeforeUpdate with subforms is a problem because the main form record is saved as you move to the subform.

 

[gemma-the-husky]

i still struggle to see the problem.

why worry about the main form updating. that's what you want isn't it? Just add suitable verification to the beforeupdate event in the main form....

 

[Galaxiom]

Seems to me to be an excellent solution in search of a problem.

Seriously though I can see the point steve is making. When creating, say an invoice, the header record is saved when you move to the lines and the records are saved.

In systems I have encountered this is dealth with by recording a Quote. Click a button and the Quote becomes an invoice, it is posted, gets a number, game over. Old Quotes are easily cleared out with a delete query after a certain time.

With the temp table the customer comes back in and says, I'll buy that stuff we discussed yesterday and the user has to sit down and type it all in again. Advantage over.

Sorry steve, I don't see it getting anyone excited.

 

[stevemanser]

With the temp table the customer comes back in and says, I'll buy that stuff we discussed yesterday and the user has to sit down and type it all in again. Advantage over.

Do I have to create a "quote" form for each of the other data entry forms such as Customer Details, Payments, Stock, Suppliers, Bills, etc., yes I see your thinking, make all the changes in a staging form of some kind, like a separate, temporary table, and then convert, or update, to the real tables, assigning the order number at the last moment if it's a new record. You could then cancel or save these changes whilst still in this staging area - all without disrupting the real tables. Thank you, I will go and work on this approach. Thanks to everyone for their comments, looks like bound forms are the right approach after all, just with the addition of forms bound to extra temporary "quote" tables and an extra convert, or "save", routine.

 

[hk1]

I came across this post/thread because I'm facing the same problem (and am of the same mind) as SteveManser as well as the original poster Thales750.

Not to be cantankerous or augmentative, but somehow I just can't wrap my head around the position of Gemma-The-Husky or GalaxiomAtHome. It seems so logical to me that a typical Invoice or Order form will have a Main Form (Header) and a Subform (datasheet view or continuous form) for related child records. And it also seems so perfectly logical to me that there should be a cancel button on the main form to cancel all the changes that the user has made to the entire "document" (be it Invoice, Quote, Purchase Order, etc.) including all header edits and child record changes such as edits, additions, and deletions. This is actually the default behavior in many other development environments. Edits are cached locally until the user does something that causes the edits to be committed to the database.

Steve, I see that your last post indicated you were going to try a different strategy where you would somehow use different "stages". I haven't really been able to make sense out of this idea. I'm wondering if you've reached any sort of conclusion or final solution on this problem that you now could elaborate on and recommend to other developers who are concerned about the same problem.

 

[Pat Hartman]

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.

Simon and perhaps others mentioned the solution I generally use which is to have a Status field in the "header". Only "complete" records are selected for further processing. As long as a record is incomplete or cancelled, only the edit form has the ability to access it. I think this might be what Steve referred to as "stages".

I have used the temp tables solution but that is only viable if it is not possible to get any type of error at all when the data is copied from the temp tables to the permanent tables.

 

[stevemanser]

Hi hk1, I managed to implement a solution that has been working for some time in a small golf business in Surrey with 10 users. I am using temporary tables to present a copy of the data and then the changes are written out using a SQL statement if the users saves at the end.

Step1 (for the initial copy):
==================

At first I used:-

CurrentDb.Execute “SELECT * INTO tmp1” & vTable1 & ” FROM (” & vTable1 & “)”

but found that the following was 100 times faster:

DoCmd.TransferDatabase acExport, “Microsoft Access”, CurrentDb.Name, acTable, vTable1, "tmp1" & vTable1, True

and works flawlessly.


Step 2 (to save the changed data):
=======================

For new records:

INSERT INTO qrySales
(txtID,txtConID,txtName,txtAddress1)
SELECT
’195285′,txtConID,txtName,txtAddress1
FROM tmp1qrySales

For updated records:

UPDATE qrySales INNER JOIN tmp1qrySales
ON qrySales.txtID=tmp1qrySales.txtID
SET qrySales.txtConID=tmp1qrySales.txtConID,
qrySales.txtName=tmp1qrySales.txtName,
qrySales.txtAddress1=tmp1qrySales.txtAddress1
WHERE qrySales.txtID IN (’195285′)

For deletions:

Deletes just ask for confirmation before actually deleting using a SQL statement.

This code is generated in VBA by a loop where I get the field names and pair them with the updated values. I generate the record number using a function (getGid) that grabs the last record number + 1. You also have to have some mechanism to determine if it's an update or insert. e.g.:

(If DLookup("txtID", vTable1, "txtID='" & Me_Form.txtID & "'") > 0 Then)

Also you need to lock the table at the start and unlock at the end in a multi-user environment:

BusyLoop:
vBusy = DLookup("blnBusy", "tblLock", "intUID=1")
If vBusy = False Then
CurrentDb.Execute "UPDATE tblLock SET blnBusy=True WHERE intUID=1"
If IsNull(vNewId) Then vNewId = getGid(vDocType, Me_Form)
Else
MsgBox ("Table locked by " & DLookup("txtUser", "tblLock", "intUID=1") & ". Will try again...")
x = x + 1
If x > 2 Then If MsgBox("Force save?", vbYesNo) = vbYes Then DoCmd.RunSQL "UPDATE tblLock SET blnBusy=False WHERE intUID=1"
GoTo BusyLoop
End If

You need to do the same "copy" and "save" steps for the sub-table records (all before unlocking) so that the whole order and sub-items are saved.

Deleting sub-records is similar to updates but I use WHERE qrySales.txtID NOT IN (’195285′) to determine what sub-records have to be deleted, generate this in VBA. The main and sub tables have an autonumber but the primary and foreign keys are txtID and txtOrdID respectively, not the autonumber fields.

This initially sounds like a lot of work, but actually once you have got a solution it can be implemented for every single table in any database you write, it's multi-user, it runs quickly over a network and the user has that CRUCIAL option:- "Do you want to save the changes?". It also negates the need for loads of weird afterupdate, beforeupdate, oncurrent stuff and you can also create duplicate records by simply assigning a new ID behind a Duplicate button.

I will put together a sample database for you with this code in it and upload it here.

Regarding Pat Hartman's quote...

I have used the temp tables solution but that is only viable if it is not possible to get any type of error at all when the data is copied from the temp tables to the permanent tables.

...interesting. I will have to look into this. So far I have not had any update errors, except when I had to run an AutoNumFix (see Allen Browne) because some sub-records were overwriting each other on save. Is there really no way to get a an error code back?

Steve