asked on
access recordset vs database size
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?
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?
Microsoft Office
Last Comment
BillTr
Upload a working sample of the database with few test records. You may remove any unnecessary objects (tables, forms, ...)
There is a 2GB limit on database size.
What do you mean by "call a recordset"?
What kind of "comparison" are you trying to do?
What do you mean by "call a recordset"?
What kind of "comparison" are you trying to do?
" I write the results out to the same two tables "
Access tries to speed up operations, hence when you delete an object then recreate it, it adds to the size of the database. To tidy up the database, compact and repair is used.
Updating field tables, deleting and adding records add to the size of the database.
Record sets do not add to the size, they depend on available memory. You need to dispose of them when done finished working with them.
We need to check your code to find a possible solution.
This is why a sample database is required.
Access tries to speed up operations, hence when you delete an object then recreate it, it adds to the size of the database. To tidy up the database, compact and repair is used.
Updating field tables, deleting and adding records add to the size of the database.
Record sets do not add to the size, they depend on available memory. You need to dispose of them when done finished working with them.
We need to check your code to find a possible solution.
This is why a sample database is required.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
My solution resolved the problem.