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'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?

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.

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.

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.

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.

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. :)

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.

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.

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 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.

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

No such thing as foolproof. Fools are too ingenious.:D

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.

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.

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.

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.

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.

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.

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?

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

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.

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.

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

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

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?

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

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.

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?

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.

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

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.

Would love to know what these simpler strategies are.

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

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.

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

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....

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

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.

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.

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.

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.

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

This is great information, although I do find some of it slightly difficult to follow. Perhaps because you tend to use some different conventions and different methods (both figuratively and literally) than I do. I prefer to use DAO to insert and update data. I find that it's much easier to write and debug even if it does require more lines of code.

What I'm hoping and planning to do is take the theories at work here and wrap it in a class. I did this recently for something completely different and I've been amazed now how easy it is to use that particular feature because I have such a limited amount of code and configuration to do each time I need to use it.

I'm leaning towards having a temp database "skeleton" (empty of data) with tables that are preconfigured with the correct fields and logic, rather than making tables on the fly. I think the only reason I'm leaning this way is because it would allow me to assign a different, "temporary" primary key (using autonumber) in the Temp records which would then be ignored when the records get transferred to the real tables.

It does seem like it would be a easier if I could create the tables on the fly. I would never have to worry about having my so-called temp database file properly configured. I can only imagine that making table changes will be a nightmare if I have to remember to make those changes in a separate file.

Thinking...

I still think temporary tables for this task are clumsy clutter. As Steve has shown they have considerable overheads to set up. He doesn't mention the bloating they cause if they are held in the FrontEnd or the management of a SideEnd if that strategy is adopted instead. They must be held on the disk and possibly cleared from it afterwards

Far better to use ADO recordsets for local transient data. Fabricated ADO recordets exist only in RAM unless they exceed the available capacity (unlikely in this scenario) but even then the overflow to disk is managed by Windows.

I've been experimenting with Fabricated ADO recordsets and they do work OK. However, I've found there are quite a few limitations with binding forms to fabricated ADO recordsets.

1) The Form's filter property doesn't work.
2) The Form's Order By property doesn't work.
3) User's cannot use the built-in Access sorting menu shortcuts.
4) Subform "linking" does not work. In other words, you cannot use the child/master fields to setup the linking relationship. This has to be done manually in your SQL, or in this case, in the creation of your fabricated recordsets.
5) I don't think there's any Autonumber in a fabricated ADO recordset.
6) In my testing, fabricated ADO Recordsets allowed entry of duplicate Primary Keys, even when you had specified which field was to be your primary key.

I don't think this list is comprehensive.

I guess to me, the issue of using disk space and having to manage that space is a non-issue. I would never put my temp tables in my front-end file. I would just create a separate temp database and possibly even have an empty, skeleton temp database that resides on the "server" in a shared folder so you can copy it over every time the app starts (overwriting the old one).

Hard Drive Space and RAM considerations are not the issue today that they were at one time. It's true, a good developer will still be concerned, and not careless about either one. But a little bit of logic and planning is all it takes. Logic and planning are something you do all day long when you develop database applications.

I've found there are quite a few limitations with binding forms to fabricated ADO recordsets.

1) The Form's filter property doesn't work.
2) The Form's Order By property doesn't work.
These features are expecting a DAO recordset. For ADO they must be applied directly to the recordset and the recordset reapplied to the form with this line:
Set Me.Recordset = Me.Recordset
3) User's cannot use the built-in Access sorting menu shortcuts.
These are easily replaced with far more elegant systems. I like using Continuous Forms set up as simulated datasheets. This provides a header where that stuff can be placed with total layout flexibility.
4) Subform "linking" does not work. In other words, you cannot use the child/master fields to setup the linking relationship. This has to be done manually in your SQL, or in this case, in the creation of your fabricated recordsets.
Even with bound DAO I find the performance is far superior when using the OnCurrent Event of the Master to load a new RecordSource. It isn't noticeable on small amounts of data but dramatic where there is a lot of data available to the subform.

With fabricated ADO, a filter on the subform recordset applied by the OnCurrent Event of the main form does the job.
5) I don't think there's any Autonumber in a fabricated ADO recordset. Correct. But not often an issue in the context of transient data on a client machine. If required, numbering a recordset can easily be done in code.
6) In my testing, fabricated ADO Recordsets allowed entry of duplicate Primary Keys, even when you had specified which field was to be your primary key.
I have not used keys in ADO recordsets. In the context of transient client side data it doesn't really apply anyway since the key needs to be aware of any other new values in the destination table when the records are inserted. Consequently it must be managed during the insert process. This is also the case with the temporary table technique.

I prefer to use DAO to insert and update data. I find that it's much easier to write and debug even if it does require more lines of code.I went with DAO as well originally but it is slow over a network so I converted all my long-running DAO stuff to SQL for speed because the customer wasn't too happy with the speed of saving big orders (with lots of sub-items) and when running a convert-to-invoice procedure and batch printing at the end of the day. Now all these things are relatively quick - maybe at the cost of debugging and possibly error codes?

As you mentioned, you do lose certain features with temporary tables - Filter By Selection, Sort Ascending etc because you are pulling up just one record into a temporary table at a time, but my users wouldn't use those features anyway. I can't tell you the number of times I have shown my customers those features in the past and they look at me like I'm from Mars. I give them other ways to do things instead.

One other thing I'll mention is that I have a standard set of "VCR-style" controls on each form, a combo box and a query button. The combo box is key to the whole design as it contains the entire underlying recordset for the particular form. When a user chooses a record from here the system grabs that record and stores it in the temporary table, and the form is of course bound to the temp table so all the data is displayed. the code is something like this:


Application.Echo False
If vTable2 <> "" Then
CurrentDb.Execute "DELETE * FROM " & vTemp & vTable2
CurrentDb.Execute "INSERT INTO " & vTemp & vTable2 & " SELECT * FROM (" & vTable2 & " ) WHERE " & vLookup2 & ""
Me_Form.Child1.Form.RecordSource = ""
Me_Form.Child1.Form.RecordSource = vTemp & vTable2
End If
Application.Echo True
(You'll notice this line:- Application.Echo False - that's to turn off the screen momentarily whilst Access clears the RecordSource and then updates the RecordSource, preventing that #?Name or ?#Deleted thing showing).

I would definitely persevere with temporary tables. As far as I'm concerned I couldn't possibly implement anything other than this type of system as regular Access just isn't up to the job for multiple users on a network who are used to Word and Excel and who want master/sub forms for data entry.

Steve

IAs you mentioned, you do lose certain features with temporary tables - Filter By Selection, Sort Ascending etc because you are pulling up just one record into a temporary table at a time, but my users wouldn't use those features anyway. I can't tell you the number of times I have shown my customers those features in the past and they look at me like I'm from Mars. I give them other ways to do things instead.

Absolutely. I think of those features as a convenience for the developer. I have never seen a user relate to the sort and filter stuff unless it is driven by a button.

Similarly with the record selectors in the bottom of the form. I did have one user who managed to use the Search box but its performance is so miserable that he complained.

Far better to build efficient tools with nice big buttons the user can relate to.

First off, Galaxiom, I'll admit you've done a good job of refuting my complaints with ADO recordsets. I found while programming my example database (more on that in a minute) that some of my complaints were true even using DAO.

As far as filtering and sorting using Menus and/or form properties, I don't see why I would need either one in this type of form. I'll admit that I do feel that I need some of those features in my larger search forms but then I don't need the editing features we've been discussing so the point is moot.

OK, now for some explanation and disclaimers on the 2007 .accdb Access database Example I uploaded.

1) This is a fully working example of what I'm trying to achieve, but with a lot of polish missing.
2) I intentionally limited my tables down to only a couple fields, just enough to use for testing. There's little to no validation happening here because I didn't see it as being important in this example.
3) My Detail form has only one subform while in real life yours may have a lot more than that.
4) I used very little error handling. I could never recommend this practice for anything other than example databases such as this.
5) This example allows you to have multiple instances of the Detail form open at once. This is a feature I now consider to be nearly a requirement in my databases as it really increases the usability. There's also an option to open a "Popup" version of the form (as opposed to the tabbed version). The popup version is an exact copy of the non-popup version but it has the Popup property enabled. I always make my code changes on the non-popup version, delete the existing popup version, and recreate it.
6) My temp tables and my main tables both in this one single frontend file. In real life I would move all the tables to two different db's and run compact and repair on the "temp" one at startup to prevent bloat.
7) In this example, you can actually delete the temp tables and they will be re-created as needed.

There are numerous dependencies built into my system. It's assumed that the original/main/non-temp tables will have a single Autonumber field as their primary key. Temp tables are assigned a TempID (numeric but not autonumber), assuming that that field does not yet exist. In the case that it did exist, it would be a big problem. This design also depends on you making use of only a single table on each form/subform while in real life your forms or subforms may actually be based on a query that includes joins. I hope to make this work for that because the project I need this on actually has Joins in the subform.

Now, I'll be the first to admit that there's wayyyy too much code to make all this work, much of it far too tightly bound to the UI. I'd certainly invite any of you to scrutinize and criticize my design and my code, make changes, etc.

I will admit that I tend to favor code heavy solutions, not that I like lots of code, but it seems it's the only way I can get the user interface to behave like I think it should. And like most users think it should. For example, it took extra code to make it so that the New button works on any of the open forms but I was looking at it and thinking like a typical user would and I just couldn't see doing it any other way.

I'd love to see an ADO version of this. Maybe it would make this a little less code intense, but I'm actually not so sure that it would.

If I can solidify this design, I'd like to wrap as much of this code as possible into a class that can easily be "attached" to any form with just a few settings (probably consisting of the variables located at the top of frmContactDetail). It would really simplify adding this on to existing form's projects.

First off, Galaxiom, I'll admit you've done a good job of refuting my complaints with ADO recordsets.

It is all about fleshing out the subject and exploring the pros and cons of different approaches. This is how we all learn. Providing a sample database is a tremendous contribution to the discussion.

Now, I'll be the first to admit that there's wayyyy too much code to make all this work, much of it far too tightly bound to the UI.

Keep this in perspective. Compared to the coding in a VB.NET application anything is Access is lightweight. You are not afraid of code and I would suggest you seriously consider moving into full code based programming if you haven't already. Your recognition of the need to focus on modularisation is exactly the right kind of attitude.

And like most users think it should. For example, it took extra code to make it so that the New button works on any of the open forms but I was looking at it and thinking like a typical user would and I just couldn't see doing it any other way.

Working from a user prespective is also an excellent trait in a progammer.

I'd love to see an ADO version of this. Maybe it would make this a little less code intense, but I'm actually not so sure that it would.

It is not about minimising code but disk footprint and speed. ADO is a RAM based technology and highly suited to VB.NET programming where one does not want a whole lot of junk appearing on the disk. The applicability of ADO to VBA is a side effect but one worth invistigating by any Access developer.

I fell in love with ADO the first time I used it. I still remember when Bob Larson posted on this site to tell me how to turn it on.

just a further thought on this.

I don't think we can be too didactic. Really, whatever a developer does to make something work is generally OK.

The point about "batched entry" - ie pre-enter into a temporary table, and then process that when the batch is complete, is that it is simple to use, and easy to undo. Just forget the batch.

If everything is done live, then in theory problems can arise. Let's say you enter a new order live, and while you are entering the order, some other process (or user) begins to process your order. now cancelling the order becomes less straightforward.

so let's say you have a flag on a partially entered order to prevent it being used ....

to do it live, you need a more perfect understanding of the business model, your other code needs modifying to work with your partially entered orders/data

to do it a controlled batch needs none of this. either accept a properly completed batch, or don't. No other process sees the batch details until you press go.

Now it may be you have to go the other way. In a booking system, your partially allocated order needs to grab and reserve something - then confirm the reservation if the order is accepted, or scrap the reservation if the order is cancelled. So a batched entry system isn't going to work in this environment. However, you do need to consider how you time out the transaction, to prevent your reservations never being cancelled. Or is it first come - first served. Optimistic booking. Recheck when you press "buy", and if someone else got it in the meantime, then tough! Theatres, hotels, airlines, olympic tickets ..... Lots of businesses in this category

So a lot of this just comes back to the business model, and the system analysis.





one other thing - some posters in this thread have referred to some of the practices used by "big software suppliers" eg quickbooks - but although they sell in big numbers, we don't know exactly how they achieve their results, and I am sure a lot of us have looked at other companies offerings, and found there was room for improvement

I had some more time to work on this and I developed a second version (also Access 2007 file format). This includes some bug fixes from the first example file I posted but it also now includes code that creates a temp database, creates the temp tables, adds a TempID primary key to each temp table, and then links the temp table to the front end, as well as tearing all of that down when you close out of the main Datasheet form. It seems to me that this is where using ADO would probably be advantageous. Less code and fewer points of potential failure.

I don't think we can be too didactic. Really, whatever a developer does to make something work is generally OK.

I mostly agree with this statement, except for the fact that poorly written code might "work" but be very difficult to extend, read, and debug in the future. I think code manageability and maintainability are very important topics in anything other than a truly trivial project. And I think it's far too easy to "script" in MS Access. I find that having the GUI completely separate from your business layer and your data access layer is just basically impossible in MS Access, specifically on DAO bound forms.