Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

access recordset vs database size

Posted on 2013-01-06
5
Medium Priority
?
192 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 31

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 46

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 31

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

715 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