access recordset vs database size
Posted on 2013-01-06
I have an access app that I use to compare two sets of data. I use a recordset to do this so I can have some flexibility when actually comparing fields. I write the results out to the same two tables where I have added test columns for each field I interogate.
So before I run my code I have a database size of 123,672KB. This is with the test columns added, but empty because I haven't run the test. When I do run the test the file size spikes to 2,097,136KB at which point, of course, Access crashes. I expected the file to get bigger but the volume of the spike surprised me.
My code does a series of lookups of background data as part of the compare and I use Recordsets for those. I just threw this together and I didn't add close statements for these because I call these functions over and over again. Does Access hang onto space when I call a recordset multiple times?
Is there a way I can leverage the recordset and minimize my space useage?