Is it a good practice to use "intermediate" tables? (1 Viewer)

[Etxezarreta]

Hello everybody,
In order to allow the user to check, modify and also to prevent bad consequences of an error, I often create an empty copy of the table I want to populate with a form.
Then I create an INSERT TO query with VBA and sql code that inserts the data form the intermediate table into the final table, and then I delete all the date in the intermediate.
It works fine, but when I need to modify the structure of the tables, I need to change both table structures and code.
I would like to know how expercienced Acces user manage it: do you work directly in the final tables, or do you use different ways?
Many thanks in advance.
Etxe

 

[Minty]

Frequent changes to table structure would possibly indicate a poor design.
If you are doing this as an insert anyway you may as well use an unbound form and miss out the intermediate table.

Alternatively, perform validations on a bound form that prevent the errors in the first place.

I'm not saying you should never use an intermediate table, but only when the other better options simply don't work.

 

[plog]

That sounds like a weird set up. Let's call your main table MAIN and your temporary table TEMP. Here's how you can simplify the process:

Create a new query based on MAIN and go into Design View.
Change it to a MAKE TABLE query and have it make TEMP.
Bring down the star from MAIN.
Bring down the autonumber field from MAIN.
Uncheck the Show box under the autonumber field.
Put the criteiria "<0" under the autonumber field.
Save that query and run it whenever you need to re create TEMP with the same structure as MAIN and without any records.

 

[The_Doc_Man]

I have used "staging" tables in the past, but rarely from a form as input. Forms are usually single-record entry cases. You can have complete control over what is going to be inserted into your real table, and the form can simply refuse to do the insert if something is wrong. Then all you have to manage is the code on that staging form. Doing anything one record at a time gives you the luxury of going straight to the final table because you can be excruciatingly nit-picky in the form for a single record. If needed, you can even do a Me.Undo to erase the form and cancel the append.

I used staging tables when importing spreadsheets for which certain associations needed to be determined for each of several rows of input. I.e. translate some project names and other names we tracked, names for which there were lookup tables with keys that we used as part of normalization of the data. Then when ready, the validated records would be transferred in bulk from the staging table to the final table. I never needed to alter the staging table structure once I got that part working because the INSERT INTO ... SELECT sequence allowed me to make any association I wanted between the fields of the staging and final tables.

In the case where I used that staging table, the rationale was that I had a bulk import to do. If I did it directly and there was an error, I would have to go back to see which rows made it and which rows didn't. Then I would correct the failed rows in the source material and then would have to re-import the corrected rows. OR I would have to back out the correctly imported rows and re-import the whole sheet again. Having the staging table allowed me to validate everything and just refuse to import ANYTHING if I knew it would cause an error. No backing out required, and the error messages I issued were specific enough that I could identify what rows and what fields to edit.

 

[Isaac]

I agree with the previous comments, there is a place for staging tables to bulk upload raw data but not from a form. The form is the whole point of where you are supposed to be doing intricate and thorough data validation - allowing no garbage in. That is its power and prowess - one of a handful of primary meanings for its existence in Access.

 

[Pat Hartman]

And even if you do decide to use staging tables, you should do them in a separate database so you don't bloat either the FE or the BE. Create a template with the empty tables and whatever indexes make sense. Link to the template. Close everything. Rename the template to Temp_something. Go back to your code and at the beginning of the import process, delete the local copy of the staging database. Add code to copy the templaate and rename it to the name you used for the staging db. Now you are always working with a compacted, empty staging db so you never have to worry about bloat.

I delete the staging database BEFORE the import operation so that it is available for review when testing. If you delete it at the end of the process, you have more trouble testing.

If you need code, I'll post some.

 

[The_Doc_Man]

Pat, the other scenario for "staging tables" in the FE is when you use the automatic FE updater that always reloads a new FE from the master copy every time you launch the app through a batch-based icon. In that case, your cleanup is wholesale, not retail. If you are dumping the old copy and refreshing with a new copy, you start clean every time.

 

[Etxezarreta]

Very interesting thread indeed, thank you for sharing.
Basically I use staging tables to expose "explicit information" (names, surnames, descriptions etc..) out of normalized tables (almost only foreign keys in the permanent tables, not understandable by the user). Since I have to write join queries all the time and then to write a INSERT TO query with only the few fields that will be put in the permanent table, I wondered if it was worth the time.
Have a good day.
Etxe.

 

[Pat Hartman]

@Doc, I would agree except that sometimes the tables need to exist for more than a short time and having them blown away because you closed and then reopened the app can certainly disrupt testing.

 

[The_Doc_Man]

@Pat Hartman - This problem you noted would also apply to having "side-end" tables in a third Access file, which is a common approach. Closing the FE restarts the process either way.

I don't see this as a problem since the way I normally used the temporary FE tables was only to build lists for which some other query could later (a few SECONDS later) use "WHERE xyz IN (SELECT zyx FROM sidetable)" situations. You load up the list, use it, and then blow it away when done. (Or... blow it away first to assure a fresh start, THEN load the list, then use it.) The data in these lists was ALWAYS ephemeral and would ALWAYS reload only when needed. This included cases where the list would have to be loaded in multiple steps of "INSERT INTO" syntax and also cases where I would build a larger list but then remove some elements via a "DELETE" query before using the final "IN" sub-query syntax.

However, if you use the FE tables for translation just to minimize remote traffic, you could always reload the tables when the FE starts up. That is a bit dangerous if the translation tables are dynamic because an update to a translation table would end up with desynchronized data between the FE and BE files. I won't describe how I eventually had to solve THAT problem. But it was soluble. And truly ugly.

 

[Pat Hartman]

The process of creating the temp tables file isn't connected to the opening of the app. It is connected to the initiation of some process such as importing a new set of data.

I use temp tables differently. A lot of my apps import data from other applications. The import files may or may not be well formatted so importing them directly into the permanent tables isn't a good idea so the app does pre-processing. Sometimes in can be done with a query but often it is better to fix up the data and put it in a temp table and then compare the temp table to the linked file to identify what data is being rejected.

 

[Rene vK]

Is it goodpractice to have a small temp table in your FE or is it better to CREATE TABLE when necessary? My wife sometimes sells a product or two during a consult. The table will be used in a listbox for overview and emptied after the invoice is done or when choosing another client.

 

[isladogs]

@Rene vK
Whether you create temp tables 'on demand' or populate empty temp tables will cause database bloat and both should be avoided wherever possible (except for the staging or buffer tables discussed above).
However creating and deleting tables repeatedly can lead to instability so in that sense is a bigger problem.

Wherever possible, use queries instead

 

[moke123]

And even if you do decide to use staging tables, you should do them in a separate database so you don't bloat either the FE or the BE. Create a template with the empty tables and whatever indexes make sense. Link to the template. Close everything. Rename the template to Temp_something. Go back to your code and at the beginning of the import process, delete the local copy of the staging database. Add code to copy the templaate and rename it to the name you used for the staging db. Now you are always working with a compacted, empty staging db so you never have to worry about bloat.

I delete the staging database BEFORE the import operation so that it is available for review when testing. If you delete it at the end of the process, you have more trouble testing.

If you need code, I'll post some.

I use a temp database for a word merge procedure. I did a test a long time ago and found that there is a very small amount of bloat associated with linking and unlinking from the temp tables in the MySysNameMap table. It was very small, a few kb's, over a period of time.

 

[The_Doc_Man]

Depending on just how extensive you want to go, there is always the method of having a blank database handy to copy, after which you open that database, create the tables you want, populate them, do whatever else you need with them, and then just delete the temporary copy. When you do that, you start fresh on all internal things. If that blank database is essentially "virgin" then there is no bloat, no garbage to collect, and you have a play pen of up to 2 GB for anything ugly that you might wish to create.

Granted, this is the extreme end of "temporary tables" - but we've already had cases where someone wanted to build PERMANENT extra files because of the capacity of what he needed to build. We steered him towards an external SQL solution but he was ready to do this extreme step for real.

 

[Rene vK]

I only created the table to make change possible during the salesmoment. It is a 7 field table and makes life easy. The FE is less then 1.8 MB in size.

Thx. guys!

 

[The_Doc_Man]

Rene vK, if you really need it to make things work and don't mind that every so often you will need to do a compact & repair, it's all good. In the final analysis, we are all computer engineers, not computer theoreticians. We are purveyors of the possible, not proselytizers of perfection.

 

[Rene vK]

Thank you Doc_Man. I will keep that in mind!

 

[Isaac]

Depending on just how extensive you want to go, there is always the method of having a blank database handy to copy, after which you open that database, create the tables you want, populate them, do whatever else you need with them, and then just delete the temporary copy. When you do that, you start fresh on all internal things. If that blank database is essentially "virgin" then there is no bloat, no garbage to collect, and you have a play pen of up to 2 GB for anything ugly that you might wish to create.

Granted, this is the extreme end of "temporary tables" - but we've already had cases where someone wanted to build PERMANENT extra files because of the capacity of what he needed to build. We steered him towards an external SQL solution but he was ready to do this extreme step for real.

Your post had a tinge of "apology" to it, but honestly I think it's a great idea. I haven't had hardly any occasions to use Temp tables in the past, for some reason, BUT...I think if it came up now, very probably this would be the first route I'd go. After all, it's only a few lines of code to create and/or delete a database, and being free and clear of it afterwards is quite appealing. Good suggestion to add on.

 

[The_Doc_Man]

Isaac, the apology is that every time you have to manipulate another thing, it can legitimately be questioned as to whether it is bad engineering. Good engineering minimizes interactions because of the old phrase "Too many cooks spoil the broth." It is ALWAYS a matter of need vs. complexity of the alternatives. Somewhere in there you hope to strike a happy medium. Though it becomes problematical as to whether the medium will still be happy after you strike him. (Some days I get a little whimsical.)