Avatar of BillTr
BillTr
 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?
Microsoft Office

Avatar of undefined
Last Comment
BillTr

8/22/2022 - Mon
Hamed Nasr

Upload a working sample of the database with few test records. You may remove any unnecessary objects (tables, forms, ...)
aikimark

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?
Hamed Nasr

" 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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
BillTr

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
BillTr

ASKER
My solution resolved the problem.