A Case for Temporary Tables in Data Entry

[Galaxiom]

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.

 

[hk1]

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.

 

[Galaxiom]

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.

 

[stevemanser]

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

 

[Galaxiom]

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.

 

[hk1]

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.

 

[Galaxiom]

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.

 

[gemma-the-husky]

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

 

[hk1]

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.

 

[Galaxiom]

I just stumbled across this old thread when searching for one about Transacted Bound Forms.

The Transacted Bound Form completely supplants both the Temporary Table and the Fabricated Recordset approaches discussed in this thread for the requirement of delayed saves across multiple records.

Here is where Spike introduced the idea and includes links to Microsoft's explanation.

A crude example database is attached.

 

[stevemanser]

I really like it. I will have a play with it and try to substitute this into one of my forms in my networked, multi-user database that has been running now for 4 years using the temporary tables approach.

p.s. Couldn't you have

If Me.Saved Or Me.Dirtied Then

in the Private Sub Form_Unload so that it transacts Deletes too?

Steve