• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

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?
0
BillTr
Asked:
BillTr
  • 2
  • 2
1 Solution
 
hnasrCommented:
Upload a working sample of the database with few test records. You may remove any unnecessary objects (tables, forms, ...)
0
 
aikimarkCommented:
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?
0
 
hnasrCommented:
" 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.
0
 
BillTrAuthor Commented:
Sorry for the delay in getting back. I was able to avoid the issue by replacing my RS update statments with update queries. It made a huge difference in database size.
0
 
BillTrAuthor Commented:
My solution resolved the problem.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now