Temporary Tables & Arrays

Topic

New Reply

W98, Office 97

My office is migrating over to MSOffice from WordPerfect and I am converting a number of databases from Paradox to Access 97. Lets not even go into why we aren’t using Office 2000.

A common technique in Paradox is to use temp tables, since each table exists as an independent object, unlike Access. If you add an underscore before a table name, Paradox treats the table like a temp system table and handles your cleanup.

Does Access have any construct like this? I build a number of temp tables to create complex reports and would like to do the same in Access. But this store everything in one file greatly hinders this. While testing a temp table scenario in Access, I quickly exceeded some memory or storage limits, until I compacted the database. Given what I’ve read on corruption when compacting, I would like to reserve this for more judicious usage on my main database. I am getting around this concern by writing the temp table out to another database designed for this purpose, but this seems asinine.

Why do I need to have temp tables? Primarily because I build up report fields that are concatenated versions of data in the same field. For example, I have a name field that contains all the names of all people. Some are related to each other. When I print a report I generate several fields that contain concatenations of the names, depending on their relationships. So you might see “surname, name1 & name2” or “surname1, name1 & surname2, name2” in one field and “name1 age; name2 age” in another field. There is also quite a bit of formatting that gets added.

Also, I tried a test doing all of this with an array and it locks the system. System PIII/800MHz-256MB ram, dedicated 3GB SCSI swap drive, 2x40GB IDE’s. So plenty of horseys here. There seems to be some Access memory limit I am hitting. I am processing around 400K records. I just set a bookmark to a record, determine the person’s type, use findfirst/next to loop through the other records to determine their status, make some decision based on the status & copy the data to the appropriate location, set a handled flag, and move on to the next record. Each record only gets handled once, beyond testing the handled flag. Once the array is build, I write it out to the temp table (in PDOX-doesn’t work yet in Access).

Reply | Quote

Replies

Well, if you don’t want to talk about why you are using Access 97 and not 2000, then how about why you are using access instead pf SQL server with that many records?

However, to address your question, there is nothing that says that the temp tables have to be in the same files as the permanent tables. Just put the temp tables in a sepatate table and connect to the tables in that db. If you aren’t already doing this to separate your data from your forms, queries, code, etc, then you need to setp back and take another look. I NEVER put my data in the same db file as the forms, queries, etc., and I seldom put all of the data in the same db file. Fixing a corruption problem is a major disaster when everything is in one file. If you put your temp stuff in a sepatate db file, then you can clean up by just replacing the temp db file with an empty template periodically.

Reply | Quote

Thanks for your comments.

Not my decision. I just play the cards I am dealt. Since we pull about 40% of our data from another client/server system, I would just like to use links, but alas, we are not allowed. We just get to use SQL to pull what we need at the start of each day.

Once completed I was going to use the database splitter to separate the data and the front end. But I do not believe that this will change the underlying questions. If the system works as a whole then it should work independently. So if I get you correctly, you use a db for forms & queries, a db for vb code, and multiple dbs for data?

Reply | Quote

I would normally put the forms, Querries, and vb code all in one db file, and then split the data into however many db files seems to make sense. For example, I would keep names and addresses in one db file, but might put order that go with those names in a second, and the inventory to fill the orders in a third. I do split the forms, querries, and code for different systms into different db files. For example, even though they both might connect to the inventory data db file, I would have sepatate db files for an inventory management system and an order entry system.

Reply | Quote

So back to the original post, the technique is to utilize temp tables in a separate db. I notice you commented that you delete and recreate the db object. I was going to have a temp db that held all of the temp db objects. Then I would just delete the data with a query and periodically compact the db. So if I follow your logic correctly, it would probably better to have individual dbs for each temp object I wish to create. Right now, one system I have creates 16 temp objects on which dozens of reports are based.

Reply | Quote

What Legare means is to have a template temp.mdb which contains the table objects you wish to use. In your code, before linking to the temp tables, check the file size of your ‘PRODUCTION’ version of the temp.mdb. If it is over a size you feel comfortable with, just copy the ‘TEMPLATE’ version of the temp.mdb over the ‘PRODUCTION’ version. Then connect yout table links and proceed as usual.
I actually use a variation of this technique with my distributed front-ends which can become bloated over time. My variation also checks for a NEWER version depending on whether I have added/modified/fixed anything to the front-end ‘TEMPLATE’

Reply | Quote

Jim described pretty much what I do.

Reply | Quote

One way to handle this is by writing to text files, which can be overwritten each time you run the routine, and then linking them to the database. I use this technique fairly often, but it requires code both to create the text files and to link them.

You didn’t explain why you would be creating all those variations on name and concatenating them together, but it sounds as if you may be doing it the hard way. Your reports sound more than complex, and I think you might want to rethink your approach to them since Access/VBA is a very different animal from Paradox.

Reply | Quote

All names are stored in a single table along with what type of person they are and their relationship to an owner. The relationship can be spouse, co-owner, associate, child or anything else you want to define.

The report format is fixed. In the report format the owner and spouse first names are concatenated, then any co-owners or associates fullnames are concatenated on one to two lines (space restrictions), then the child’s name and ages are concatenated on another line along with the co-owners and associates children.

You are correct, Access and Pdox are quite different. This is quite simple in PDOX. I first tried doing it on the fly with arrays and then writing out to a table but ran into memory problems on an K6-2 500MHz with 196M Ram, 1GB swap drive, Win98.

So are you building up delimited text file,linking to it and basing a report off of this?

Yes I am having to rethink quite a number of things, especially since Access’s event model is not quite as robust as PDOX’s.

Reply | Quote

Access and Paradox have different kinds of event models, but you’re talking about object models and not events. Paradox is no more robust than Access, just different. I’ve worked with both, and my bias is toward Access.

What you’re trying to do may be simple in Paradox, but it isn’t logical anywhere. Why are you creating a report by concatenating a bunch of names instead of using the individual data fields and allowing the report to do the work? The Access report generator is one of the best around, so make it do the work for you rather than just recreating what you did in Paradox.

Maybe if you explain what the report is supposed to reveal, rather than just what it contains, you’ll get more helpful answers.

Reply | Quote

I realize you are trying to assist me, as well as I am trying to learn. The report format is logical. I do not know why you would state this. It may be unclear to you, however we can work to resolve this in order for both of us to more clearly understand and benefit. Perhaps it will assist someonelse also. I was talking events and not the object model. One example set is the inability of Access 97 to differentiate left and right mouse click events or single and double click events. Of which I have a large number of functionality attached to in PDOX systems. I apologize for this small defense of what I meant.

As I stated, the report format is fixed. I do not have a choice here. All of the names are stored in a names table.

salutation:first:middle:last:suffix:persontype:relationship

Pulling the info from this table is the issue, not the ultimate report format. Access’s report generator will not pull this info and assemble it. It has to be done before the report is generated.

The report format takes multiple permutations of the same format, eg:

Smith, Joe & Susan
221 Central
Suite 101
Chicago, IL 45125
Bill 12; Sharon 8

or

Jones, Bill & Susan
Thomas, Mike (Co-owner)
221 Central
Suite 101
Chicago, IL 45125
Bill 12; Sharon 8

Mike would also have his own listing in addition to the Jones.
Thomas, Mike (Co-owner)
Jones, Bill & Susan (Owner)
6000 North St
Chicago, IL 45125

These are only a few of the permutations. They actually fall into several classes based on person type and relationship.
As you can see, one needs to prebuild these strings. Utilization of temporary tables or arrays is the best way to this type of work. Temp tables, while slower than arrays are more stable and relatively easy to work with.

Reply | Quote

Actually Access does apply functionality to Click and Double Click events through property settings on Forms and can distinguish between mouse buttons using the Mouse Up Mouse Down events (see help). Building strings as you indicate in your example is also a simple matter in reports, e.g. [txtLastName]&” , “&[txtFirstName]&” and “&[txtSpouseName] etc. Creating a second instance of Mike Thomas can be handled in Sorting and Grouping of Reports. Having said that, you can also create the concatenation in the underlying query. I truly believe, Access will accomodate pretty much everything your asking for without the need to create temp tables. Just my Good luck, and keep posting.

Reply | Quote