Solved

access recordset vs database size

Posted on 2013-01-06
5
180 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
  • 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Some time ago I was asked to create a VBA function that would calculate a check digit for an input number, using the following procedure: First, sum up all the individual digits in the number If that sum value has more than one digit, then sum up …
Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now