Solved

access recordset vs database size

Posted on 2013-01-06
5
189 Views
Last Modified: 2013-05-10
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
Comment
Question by:BillTr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 30

Expert Comment

by:hnasr
ID: 38750350
Upload a working sample of the database with few test records. You may remove any unnecessary objects (tables, forms, ...)
0
 
LVL 45

Expert Comment

by:aikimark
ID: 38757004
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
 
LVL 30

Expert Comment

by:hnasr
ID: 38757588
" 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
 

Accepted Solution

by:
BillTr earned 0 total points
ID: 38816442
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
 

Author Closing Comment

by:BillTr
ID: 39154799
My solution resolved the problem.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question