473,322 Members | 1,566 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Bloated Table

108 64KB
I have a table in one of my projects called "Days" that contains a record for every day over the next five years (1,826 records). It contains the fields ChargeDate, Total, Count, and Notes. I use the table to subtotal individual charges from another table "Charges" into their respective dates so that I have record of charges grouped by day. It is the same concept as the totals query below, except I couldn't use my Note field because it is a memo field and I got an error:
Expand|Select|Wrap|Line Numbers
  1. SELECT ChargeDate, Sum(ChargeAmount) AS DailyTotal
  2. FROM Charges
  3. GROUP BY ChargeDate;
I need to preserve the data in the Note field so I wrote a procedure that totals the charges for me, while at the same time concatenating the note fields for each date together. When I run this procedure it does a lot of other operations, but the part that is of interest to me now is when I step through each record in my Days table and clear out the values to get them ready for the next round of calculations:
Expand|Select|Wrap|Line Numbers
  1. Dim db as Database
  2.     Set db = CurrentDb
  3. Dim rstDays as Recordset
  4.     Set rstDays = db.OpenRecordset("Days", dbOpenDynaset)
  5. Dim DaysRC as Integer
  6. Dim DaysProgress as Integer
  7.  
  8. With rstDays
  9.     .MoveLast
  10.     DaysRC = .RecordCount
  11.     .MoveFirst
  12.     SysCmd acSysCmdInitMeter, "Step 1 of 2...", DaysRC
  13.     For DaysProgress = 1 To DaysRC
  14.         SysCmd acSysCmdUpdateMeter, DaysProgress
  15.         .Edit
  16.             !Total = 0
  17.             !Count = 0
  18.             !Notes = ""
  19.         .Update
  20.         .MoveNext
  21.     Next DaysProgress
  22.     SysCmd acSysCmdRemoveMeter
  23. End With
  24.  
  25. rstDays.Close
  26.     Set rstDays = Nothing
  27. db.Close
  28.     Set db = Nothing
  29.  
I just learned how to use the Progress Meter, but I don't think that has anything to do with my problem. Other than that there is nothing that complicated about it; I've used similar code plenty of other times with no bloating issues.
I had to run this part of the routine several dozen times while I was experimenting with different data and debugging the rest of the procedure. Before I opened the file last night it was at about 80 MB, then at some point several hours later I checked again and my file was over 360 MB.
The first thing I tried to do was compact and repair, but that only cut it down a few MB. I ended up importing all of my other tables, forms, and queries into a new file, re-creating the Days table and re-establishing my relationships. After I did that I'm below 4 MB total, but now I'm afraid it is going to happen again because I need to run this procedure repeatedly. Does anyone know why this would happen? I'm nearly positive it is because I'm re-writing the values into those three fields of the Days table because that was the only table that was a problem, and that is the only operation I did on that table (*edit- I also re-wrote the field values later in the procedure). I don't understand why that would happen and why it wouldn't get fixed by compacting.
Sep 3 '14 #1
3 1177
twinnyfo
3,653 Expert Mod 2GB
GKJR,

This happens because it is MS Access. it is just how Access reserved resources for your table. Because you are using a Memo field, MS Access reserves the maximum amount of space needed for a memo field every time you edit a record (if my understanding is correct). Since you have 1,826 records, just do the math. Then, when you clear out the records the same happens. This continues until MS Access reaches it magical 2.000000 GB limit (i've tried and it wil NOT go one byte beyond 2 GB).

Based on what you are using your table for, unless there is a need to maintain the consolidated data, I would recommend just using the query that gathers the data as a record source for any other objects in your database.

The continual editing and deleting is your problem and the only cure is to compact and repair when no one is using the DB.

Hope this hepps!
Sep 5 '14 #2
zmbd
5,501 Expert Mod 4TB
TwinnyFo has this correct for the Memo fields.
Which I suggest avoiding if not absolutely needed as they have a whole host of finicky and buggy behaviors.

What you are doing here begs the question about proper normalization of the data.[*]> Database Normalization and Table Structures.

The second solution is to make a temporary working backend.
This is something I have done in the past when needing to use temporary tables to hold data. This working backend is simply deleted at the end of the user session after saving any required data to the permanent recordset.
I've found this to be less of a need since I learned about database normalization and rarely need these anymore.
Sep 6 '14 #3
GKJR
108 64KB
I did a little more checking after your inputs and realized I made a careless mistake with my Notes field in the Days table. I had the Append Only option set to Yes, so every time I ran procedure it was not really erasing the field value but actually making it larger. With the number of records in the table I guess it just grew out of control. Thanks for your help.
Sep 6 '14 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

7
by: mr_burns | last post by:
hi, is the table percent value for height used for displaying in browsers. i have a table i want to run to the bottom of the screen so it seemed best to set the height value to 100%. when i...
117
by: phil-news-nospam | last post by:
Is there really any advantage to using DIV elements with float style properies, vs. the old method of TABLE and TR and TD? I'm finding that by using DIV, it still involves the same number of...
4
by: TomB | last post by:
Hello, For the "shows" listing on my band's homepage I use tables. See the page at http://deimos.curious.be/~dusk/v2/shows.php Since we aren't exactly dealing with "tabular data" here I...
9
by: Daniel Kabs | last post by:
Hello there again! On my quest to remove layout tables in favor of CSS controlled elements, I am faced with quite a problem. A table cell that has no explicit width attribute set usually...
13
by: Davo | last post by:
Hi Folks, There seems to be something about not using tables for layout, but use divs instead. I'm not sure if I've got this right. If the output looks like what you want, then it shouldn't...
11
by: CoreyWhite | last post by:
So I'm reading books about perl, which may not be quite as powerful as C++ but at least has more power than C & is very easy to learn from the manuals. I'm also buying books on C++, and books...
2
by: adrin | last post by:
hello, i am a c# newbie, and i'm writing an application that requires to show some numerical data in a table(it is a distance matrix). i wonder if there is any component that serves such...
3
by: Shailesh Patel | last post by:
Hi, How do I export Html table to excel file? Thank you in advance. Shailesh
2
by: Brock | last post by:
Is there a way to place a vertical scrollbar on the table, or a way to embed the table in another control type that I could put a scrollbar in?
11
by: MKragh77 | last post by:
Hi I need to split a table consisting of different customers. I want to execute a query that group the customers identity and then uses this query to create n tables with the customers name as...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.