Temporary Recordset in Memory

[Michael]

I have a situation where I need to populate a temporary recordset, then
process the records in it and then delete it. This process is done for each
individual in my database (a couple of thousand) and at no time do I need to
save the data to disk - it's just a temporary recordset I need to use while
doing other things for each individual in my database.

Is there anyway to do all this in memory so I don't end up with database
bloat?

Thanks,

Michael

 

[Michael]

That is currently my problem. I have temporary tables in my database that
are used to store these values and then deleted for each individual in my
database. The end result is that I am having significant bloat.

I am interested in programmatically creating a temporary database, using it
for this purpose and then deleting it, but I'm not sure how to do that
programmatically (i.e., create the database, create all the tables with their
related fields, link the database, and then delete it.

Any help would be greatly appreciated.

Another alternative, is that I could manually create the database with the
tables and link them to my database. Then use the tables as needed and then
programmatically do a Compact & Repart on the temporary database but I wasn't
sure I could do that if Access would consider this database as open since it
was linked to my main database.

Thanks,

Michael

 

[Rick Brandt]

That is currently my problem. I have temporary tables in my database
that are used to store these values and then deleted for each
individual in my database. The end result is that I am having
significant bloat.

I am interested in programmatically creating a temporary database,
using it for this purpose and then deleting it, but I'm not sure how
to do that programmatically (i.e., create the database, create all
the tables with their related fields, link the database, and then
delete it.

Any help would be greatly appreciated.

Another alternative, is that I could manually create the database
with the tables and link them to my database. Then use the tables as
needed and then programmatically do a Compact & Repart on the
temporary database but I wasn't sure I could do that if Access would
consider this database as open since it was linked to my main
database.

You could do one of three things.

It is possible in code to create the temp db file and then create inside of it
all of the tables and relationships that you need. Depending on the number of
those this might be a fairly cumbersome bit of code to create.

You could do as you suggested in your final paragraph. Once you close all
active data connections the external file will no longer be "open" so you could
compact it via code.

You could create one external file for this purpose and just use it as a
template. Each time you would copy that file, do all the operations on the
copy, and then delete it when finished.

 

[Michael]

Also, one more thing I forgot to mention:

I'm not merely working from a recordset. I need to create temporary values
from a recordset to then use in another set of processes. The process I am
currently using is:

1. Open a recordset

2. Go to the first record and create new temporary values having multiple
records and save them to a temporary table.

3. Open the temporary table and process those records.

4. Delete the records from the temporary table.

5. Move to the next record in my recordset and do the process all over again.

This obviously creates database bloat (my database goes from 22MB to 150MB
just because of this) which is what I'm trying to avoid. If there was some
way to hold these temporary records in memory, process through them and then
delete them, it would really help. I can't just set up an array because I
the number of temporary records will vary by individual and there's no way to
know how many temporary records there will be until I've completed the
processing for each individual.

Thanks,

Michael

 

[Michael]

Hi Rick,

Yes, your last idea is something I thought of also and is perhaps the best
solution. I can figure out how to do this programatically. However, could
you show me some sample code to programmatically create the database, it's
related tables and link them to the current database? If would really be
helpful to me to learn how to do this. Also, how do I close the connection
with the linked tables so I can compact/repair the temporary database (if
that's the direction I decide to go)?

Thanks for all your help.

Michael

 

[Rick Brandt]

Hi Rick,

Yes, your last idea is something I thought of also and is perhaps the
best solution. I can figure out how to do this programatically.
However, could you show me some sample code to programmatically
create the database, it's related tables and link them to the current
database? If would really be helpful to me to learn how to do this.
Also, how do I close the connection with the linked tables so I can
compact/repair the temporary database (if that's the direction I
decide to go)?

Thanks for all your help.

Tony Toews has a sample database at the link below...

http://www.granite.ab.ca/access/temptables.htm