Using TempTables Vs Multidimensional Arrays 3

[sub5]

Hi all,

I currently have a DBase with several tables that are cross referenced with a series of autonumber IDs. It is more complex than this but to illustrate the principle:

tblCustomer has an CustID field and a CustName field.
tblOrders has an OrderID field and a OrderName field.

My user fills in a series of unbound text boxes on a form which includes both the customer and order details.
They input two new customers which both have the same order. They click update.

Code creates two new records in tblCustomer. It also puts the new customer's CustID's into tblTempCustID as two records. The code then accesses tblOrders, adds two order records using the CustID's stored in the tblTempCustID.

Does anyone have any comments on the upsides / downsides / recommendations to creating a multidimensional array to use in the process instead of the tblTempCustID?

 

[formerTexan]

Hi sub5,

Kind of hard to answer your question without knowing what you intend to do with the new info you are currently sending to the temp table. Arrays and temp tables will have respective advantages depending on the situation and where you are headed. I like using arrays, but have to weigh the extra coding that goes with them. Also Access is natively designed to play with tables and table based recordsets - for example: you can bind a form to a table, but not to an array
You will also need to consider how the primary key for your records is going to be generated and referened.

CHeers,
Bill

 

[Ed2020]

Sub5,

Following on from formerTexan's reply I'd say it not only depends on what you are planning on doing with the data but also the volumes of the data.

Temporary tables will cause your database to bloat over time (unless you compact on a regular basis). Arrays will not cause this problem, however the data will be stored in RAM - large amounts of data will quickly eat into system RAM, especially if you are using large arrays of variant data types.

Ed Metcalfe.

Please do not feed the trolls.....

 

[sub5]

Hi Ed and FormerT

What happens is I have one form open with a lot of tab controls. Each tabcontrol represents the data for a particular table. When the user has filled in the unbound controls on a tab they click update and a recordset is opened on the pertinent table and the records updated - probable max 10 records with say 20 data elements. At the same time the id's for those 10 records are added to a temptbl, the tab is made not visible and the next tab visible. They input the new data elements and click update. A recordset is opened on table2, and the records are created using the id's from the temptbl and the data in the unbound controls of tab2. When all the tables are updated, the form is closed and all the records removed from all the temp tables.

 

[MichaelRed]

you can generally avoid the bloat of temp tables / recordsets via use of ADO disconnected recordsets.




MichaelRed

 

[formerTexan]

Hi sub5,

I'm just ruminating here and trying to get a better picture.

When the user begins the process on the first tab and a set of PK's (ID's) ar created, is this set then used for all subsequent tabs or is a new set sequencially created on each tab? Either way, it seems that if there are going to be multiple users, you will need to generate your own PK's rather than leave them to Access's autonumber system. This way you will know in advance what they will be and can store them for the next tab.

I am curious as to how you intend on using unbound controls with multiple records. Remember that continuous forms are basically ruled out with unbound controls.

Definitely consider Michael's suggestion of disconnected ADO recordsets, although I suspect that this will work best in the post A2K versions. A good question will be to find out if a form can be bound to a disconnected recordset and therefore whether this will resolve the continuous form problem.

I am rambling here, but maybe it will help focus some of the questions.

Cheers,
Bill

 

[sub5]

Hi formerT

sorry to change the scenerio on you a bit here, but:

The user is using a form to input contract details. The tab has a multiselect listbox that looks up all the product types from a tblproductsmaster. The user highlights the products on the contract (say prod1 and prod2 are selected). They then fill in unbound controls which contain contract attributes that apply to all those products. When they click update, code opens a recordset on tblContractAttributes1, it then creates two records with all the same data except product type which is prod1 and prod2. The id's autogenerated from tblContractAttributes1 re put into a tbltemp1. The user is then taken to a second tab. On this tab a listbox has all the subproducts related to prod1 and prod2. The user selects the relevant subproducts which will have the same contract attributes at subproduct level (say they select prod1a, prod1b, prod2a). They fill in another set of unbound controls and click update. A recordset is opened on another table tblContractAttributes2 and three records are created which have the same info except two have prod1ID from the temp table, and prod1a and prod1b in the subproduct field (from the listbox) and the other record has prod2ID from the temp table and prod2a in the subproduct field (from the listbox). Hope this expalains it a bit clearer! NB the second list box lso uses the tbltemp to filter the subproducts list (which again comes form a seperate master table).

The way I deal with multy users is to have a a frontend on their c drive asnd the temp tables are stored there.

 

[Ed2020]

Sub5,

If your current setup is using temp tables then I'd continue to do so. For the amount of data you're using the bloat is going to be so minimal as to be not worth bothering about.

A suppose arrays ought to be (faster than temporary tables, but then if the code is quick enough already (and it should be with the amount of data you are dealing with) who cares? Don't optimise code that is already quick enough.

Ed Metcalfe.

Please do not feed the trolls.....

 

[Ed2020]

Sub5,

One other thing...

If I was creating the system you describe I would probably be more inclined to use two dimensional arrays instead of temporary tables, but only because I don't like using temp tables in MS Access (for the reasons already stated).

Ed.

Please do not feed the trolls.....

 

[MichaelRed]

the entire process sounds like it is a bit more convolouted than necessary. a thought is to look into the subject of cascaded combo boxes. This concept essientially set (or at least limits) the recordsource for subsquent combo boxes. Wheather they (the cascaded combo boxes) are on different 'tabs' doesn't really matter. Also, since the selected value of each combobox is available, setting text boxes to these doesn't accomplish much more than hte 'waste' of a few cpu cycles.

I haven't really followed this thread closely, and do not know wheather this is 'your desig' or an inherited one, but -without intending offense- it sound like a fairly amaturish approach and you should have it throughly reviewed by someone with more experience.



MichaelRed

 

[sub5]

Hi Michael,

I've never heard of cascaded combo boxes, perhaps you could enlighten me or point me in the direction of good link that I can learn from.

That said, I don't know how a combo box would work because the user needs to be able to select multiple products on one page at one time and the number of products could increase at any time, hence the list box population could expand at any time and I don't want to have to go in and add new comboboxes, or have the user having to reselect products again and again - maybe I'm missing your point because I don't understand cascaded combo boxes.

But I do take your point that I could use the selected items in the list box to build an SQL for the row source of the second listbox.

I am the one doing the application and there is no-one more experienced to look at it. I value your input but not sure what you consider to be amaturish:
using temp tables?
replacing them with arrays?
using multiselect list boxes?
using unbound controls?

 

[MichaelRed]

sub5, I was / am refering to the unnecessary aspects, like copying the combobox values to text boxes for 'other' uses. Again, I havn't followed the thread closely, and do not intend to offend anyone, just mention what I think are a few obvious points / issues which seem to have been overlooked by the group.

You can find several to numerous references to 'cascade combo' using advanced search. A brief review of these threads should quickly give you sufficient information to construct the ones applicable to your application. The basic concept is to start with one combobox (usually the recordsource for this one is (or can be) a stored query. A following combobox would use the selection of in the first combobox as part of the selection of the recordsource for itself. This selection may, depending on the situation and your desires, be a parameter for another query, or a where clause for inline sql. Subsquent comboboxes / recordsource selections follow the pattern until the complete sequence is obtained. One quite general use of this is to permit users to 'inspect' a set of values (a single field) in any TABLE. The first combobox presents a list of tables, and the recordsource for the second one is generated as the field list of the selected table. Finally, the user is presented with the set of values.

The use of temp tables, while widespread in the Ms. A. community seems -to me- to be a large scale waste, in that theer are several ways to avoid them and the subsquent bloat they inevitably generate.

SOme of the other specific items you list are more general and have no direct bearing on my opinion re the discussion. These may be used either wisely or foolishly depending on the context of their use.

I have been programming for a bit longer than many, starting with FORTRAN IV circa 1967 and have used over a dozen different 'languages' since then. I only mention this 'history' as the prefix / introduction to the one consistient lesson to have been given to me in ALL of these environments. If it can go wrong, IT WILL GO WRONG (with a computer program anyway). The wrongness going may not happen soon, and you may not be there to see it, but it WILL go wrog. From this limited perspective, I try to progress to the avoidance of structures, techniques, approaches and conventions which seem to lead off into the directions which may lead to the etherial wrongness of it all. For Ms. A. -in particular- bloat growth is one of these areas. Ms. A. (Jet db engine amongst many relational db engines) has no on-line/automatic method of compacting the data. It is perhaps all to easy to forsee someone generating some make-table query which copies the largest recordset in the app to a temp table to generate a report, and running hte report a few times a day. Ms. A (Jet et-al) will simply keep expanding until there is no more storage available - and crashing, with potential carrear altering consequences.

sorry about the 'soap box'




MichaelRed

 

[sub5]

Thankyou for your thoughts Michael - no offence taken. I am being driven by the same premise as you - if it can go wrong, IT WILL GO WRONG! Hence I am trying to make maintenance easier, ie rationalising a DBase which has grown organically and hence haphazardly by employing a variety of code and techniques which despite their differences have the same results. My inexperience tells because I am searching which of those techniques / styles to choose ie will be the least likely to cause problems - especially as I will have to hand the system over sometime and want to make a good pass. nb Unbound controls mirroring comboboxes have been axed!