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