Solved

Why does the MS ACCESS 2007 database exceed 2GB?

Posted on 2011-09-12
22
553 Views
Last Modified: 2012-05-12
After importing a file the database size is 36MB. After running the the following code it grows to 1124MB. After compacting and repairing, it shrinks to 60MB. What is causing it to grow so large?
The first time the patient is seen all fields are populated. The following rows have four fields that are blank.

Sub UpdFields()
Dim LastACCSeen, LastMRNSeen As String
Dim LastAdmSeen, LastDisSeen As Date
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim reccount As Long
DAO.DBEngine.SetOption dbMaxLocksPerFile, 15000
Set db = CurrentDb()
Dim lngTotRecs As Long

 Set rst = db.OpenRecordset("Import")
' Set rst = db.OpenRecordset("select * from Import where ID >= 500000")
' this is the name of the index not the field name for access it is usually "PrimaryKey"
 rst.Index = "PrimaryKey"
rst.MoveLast
reccount = rst.RecordCount
lngTotRecs = reccount
rst.MoveFirst
Do While Not rst.EOF
  '  Debug.Print reccount
  '  If rst.Fields("ACCOUNT_NO") = "12345" Then Stop
       If IsNull(rst.Fields("MRN")) Then
             rst.Edit
             rst.Fields("MRN") = LastMRNSeen
             rst.Fields("ACCOUNT_NO") = LastACCSeen
             rst.Fields("Admit_Date") = LastAdmSeen
             rst.Fields("Discharge_Date") = LastDisSeen
             rst.Update
       Else
                LastMRNSeen = rst.Fields("MRN")
                LastACCSeen = rst.Fields("ACCOUNT_NO")
                LastAdmSeen = rst.Fields("Admit_Date")
                LastDisSeen = rst.Fields("Discharge_Date")
       End If
       rst.MoveNext
       reccount = reccount - 1
Loop
Beep
rst.Close
db.Close
Debug.Print lngTotRecs
End Sub
 
0
Comment
Question by:avgplusguy
  • 7
  • 7
  • 4
  • +1
22 Comments
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility

 Are you dumping the table "Import" after your through with it or deleting all the records it contains?

Jim.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility

 Scratch that, I didn't look at what you were doing close enough.  If those fields your updating are variable length, then more then likely what is happening is that when modified, the record will no longer fit on the page, so it's moved to a new page.

Jim.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
Comment Utility

 Rebuilding of indexes might also be coming into play.

Jim.
0
 

Author Comment

by:avgplusguy
Comment Utility
Import is feed into a different database because of size limitations.
One date field. Three Text fields 15char length.
I load about 300k records with only a couple of other fields.

While the visual basic code is running the table grows in size over 32 times.
I can see a database table growing 2 times or 4 times or MAYBE 8 times it size, but it grows 32 times  After all the compacting is done at the end it is less than 2 times its original size.

When I try to process an entire year it exceeeds the 2GB limit.
What sort of temp records or fields are being created?
Is there any way I can say do not keep them?
If it crashes, I can reload.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 125 total points
Comment Utility
1. Please avoid using the "Multi line" declarations:
Dim LastACCSeen, LastMRNSeen As String
Here only LastMRNSeen  is actually a string, ...LastACCSeen will be a *Variant* because you are not explicitly setting a datatype.
So use the more standard syntax:
Dim LastACCSeen as string
Dim LastMRNSeen as string
(The same goes for your date variables)
Variants generally end up "Bigger" (for lack of a simpler explanation) than any other datatype.

2. You are not setting the recordset to Nothing...
rst.close
set rst=Nothing
db.Close
Set db=Nothing


3. Access uses memory simply as a function of what it needs to.
Some operations just "Bloat" the DB more than others.
This is normal.
Access does not "Clean Up after itself" completely in some cases.
This is why C&R ,should/has to, be run manually.

Kinda like you having a spouse who cooks you a great meal, but then does not clean up afterwards... (so you have to)
;-)

4. How many records are we dealing with here?

5. Is this really neede to be this high:
DAO.DBEngine.SetOption dbMaxLocksPerFile, 15000


JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
I am no SQL Expert,....
But I can't see why anything that you are doing there can't be done via SQL.

Running SQL almost always uses less resources than running a recordset.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Ditto everything about what the others have said about your Variable declarations.
Now, these are odd construction

 Set rst = db.OpenRecordset("Import") '<--- what type, dbOpenDynaset
' Set rst = db.OpenRecordset("select * from Import where ID >= 500000")
' this is the name of the index not the field name for access it is usually "PrimaryKey"
 rst.Index = "PrimaryKey"  '<---I've never seen this.  A Recordset doesn't need to mess with this for the most part
'<--- why bother with this.  Grab the record count after it completes
rst.MoveLast
reccount = rst.RecordCount
lngTotRecs = reccount
'<--why bother?
rst.MoveFirst

Try this



Sub UpdFields()
Dim LastACCSeen As String
LastMRNSeen As String
Dim LastAdmSeen as date
LastDisSeen As Date
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim reccount As Long
DAO.DBEngine.SetOption dbMaxLocksPerFile, 15000
Set db = CurrentDb
Dim lngTotRecs As Long

 Set rst = db.OpenRecordset("select * from Import Order by ID", dbOpenDynaset, dbSeeChanges)
rst.MoveFirst
Do Until rst.EOF
       If IsNull(rst.Fields("MRN")) Then
             rst.Edit
             rst.Fields("MRN") = LastMRNSeen
             rst.Fields("ACCOUNT_NO") = LastACCSeen
             rst.Fields("Admit_Date") = LastAdmSeen
             rst.Fields("Discharge_Date") = LastDisSeen
             rst.Update
       Else
                LastMRNSeen = rst.Fields("MRN")
                LastACCSeen = rst.Fields("ACCOUNT_NO")
                LastAdmSeen = rst.Fields("Admit_Date")
                LastDisSeen = rst.Fields("Discharge_Date")
       End If
       rst.MoveNext
Loop
Beep
rst.Close
db.Close
Debug.Print rst.RecordCount
End Sub

Open in new window

0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility

   As for the other comments, yes your code could use some cleanup, but there is nothing there fundamentally that would be causing the bloat that I can see.  Possibly the openrecordset as Nick pointed out, but the open you have should be fine.

  I think what's going on is the expansion of the variable length fields when you do the update.  JET never splits a record across pages, so if the record no longer fits on a page, it's moved to a new page.  

  A change a while back (think it was JET 3.5), was to always place a new record on a new page.  This was done to avoid concurrency issues when multiple users would be adding records.

  What I would try is opening the DB exclusive and re-test.  See if that makes any difference. It will certainly change the locking that's done, but I'm not sure r not if it will change the new record placement.

  And with that, not sure this is a one-time deal or not, but if not, when those records are firest created, create them with the the text fields filled with spaces rather then letting them get created with zero length strings.  The update then will not force the record to be moved to a new page.

  Also as a seperate test, I would drop any indexes on the table, just make sure you use Nick's open.  If you really need the ID>= 500000 check, do that as an If check after you've read a record and just read through the whole table.

Jim.
0
 

Author Comment

by:avgplusguy
Comment Utility
The old code would 2GB bomb somewhere between 500,000 and 600,000 so it was inserted and later commented out when the imput file was broken into multiple parts.
Making sure all string and date were declared on separate lines saved 24kb out or 1,067,964KB.
Closure cleanup rst and db=nothing did not help, but is cleaner code. Not keeping track of record count did not save any space. I removed the index from ID and it still did not help, but I think this is the area for the most chance of improvement
0
 

Author Comment

by:avgplusguy
Comment Utility
Opening exclusively does not help. Using Access 2007, but on an Access 2000 file format.
Will convert to 2007 and try again.
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 125 total points
Comment Utility
Can you make sure that my understanding of your code is correct?

You are walking through a very large recordset.
If Import!MRN is null, you are writing 4 previoisly stored values to the table
If it is not null, you are saving four values to write the next time that you hit a null MNR value.

Let's rework this again
Let's see if a With and ! instead of full listing everything everytime makes a difference
Sub UpdFields()
Dim LastACCSeen As String
LastMRNSeen As String
Dim LastAdmSeen As Date
LastDisSeen As Date
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim reccount As Long
DAO.DBEngine.SetOption dbMaxLocksPerFile, 15000
Set db = CurrentDb
Dim lngTotRecs As Long

Set rst = db.OpenRecordset("select * from Import Order by ID", dbOpenDynaset, dbSeeChanges)

With rst
    .MoveFirst
    Do Until .EOF
           If IsNull(.Fields("MRN")) Then
                 .Edit
                 !MRN = LastMRNSeen
                 !ACCOUNT_NO = LastACCSeen
                 !Admit_Date = LastAdmSeen
                 !Discharge_Date = LastDisSeen
                 .Update
           Else
                    LastMRNSeen = !MRN
                    LastACCSeen = !ACCOUNT_NO
                    LastAdmSeen = !Admit_Date
                    LastDisSeen = !Discharge_Date
           End If
           .MoveNext
    Loop
    Beep
    
    Debug.Print .RecordCount
    .Close
End With
db.Close
Set db = Nothing
End Sub

Open in new window

0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
avgplusguy,

Again, If your goal is to "reduce" bloating, then you will have to evaluate all of the suggestion.

But you will never really "Eliminate" it.

Again, Access will bloat the DB as just a function of how it works.
In other words, you can't expect to have Zero bloat after processing half a million records.


Other options might include breaking the procedure into multiple pieces (with manual C&R in between)

Or simply moving to one of the SQL variants where Bloat, or the firm 2GB File size limit is not really an issue.


JeffCoachman
0
 

Author Comment

by:avgplusguy
Comment Utility
Convert mdb to Access2007 accdb. Biggest help but file still grows to 725,636 KB before compressing to 33,564KB. Exclusive User. No index. 5000 MaxFileLocks
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Did you read my post(s)?

You will never Eliminate the bloat.

Perhaps you are reaching for some arbitrary file size (after the code is run) that is simply not possible...?

I am not saying that a suggestion here won't create a significant reduction in the size of the bloat.
I'm just saying that at some point, ...the bloat: "is what it is"...


....


Jeff
0
 

Author Comment

by:avgplusguy
Comment Utility
I know there is bloat. I can live with 2x bloat.
I can live with 4x bloat. 8x bloat. I can live with 16x bloat TEMPORARILY :-)
With my current data I can not live with 32x bloat.
If we deal in Access 2007, we may be able to process one year at a time, but we will probably stick to the File Format 2000 to maintain backwards compatability.

Looking at creating a link_id field in one pass instead of four fields.
Then we can do SQL merging and stay under the 2GB limit.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility

 Only one other thought here: check and make sure that you have TMP and TEMP environment variables and that they point to valid drives/directories.

  I'm also wondering if you tried pre-filling the text fields with spaces or not.

Jim.

0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility

 one other thing, are you up to date on service packs?  Not sure if that was mentioned or not.

Jim.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Did you understand and attempt @JDettman's suggestion:
<And with that, not sure this is a one-time deal or not, but if not, when those records are first created, create them with the the fields filled with placeholders rather then letting them get created with zero length strings or nulls.  The update then will not force the record to be moved to a new page.>

What Jim is suggesting here is that when you are writing thousands of new entries to fields that are "" or NULL that each record is being written to a new code page --which may be 4K or 8K (I could be wrong on size)  but is certainly not zero.
Jim suggestion--since you have said <The first time the patient is seen all fields are populated> is that instead of leaving them NULL or "" that you put in a placeholder value , "                  ", of some appropriate type in each of the 4 blank fields.  Your code, when it detects a placeholder, would then replace the placeholder with the appropriate value.  Because you will be updating values without increasing the size of each record, you may avoid the code paging behavior.  To be successful the placeholder would need to be of the correct data type and as large as the possible replacement.

The Compact and Repair utiltiy reorganizes the tables.  What Jim's suggestion is, is an attempt to avoid the need for any reorganization.
0
 

Author Comment

by:avgplusguy
Comment Utility
My expert added one additional field linkid to the database. This one field was updated for the next four records. Then the SQL merge works and the database only grows to 80MB instead of 1069MB.
Thank everyone for their suggestions.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
I admit to confusion.
Can you explain for posterity's sake what you were doing and how you corrected the problem.
What does this mean
<added one additional field linkid to the database>
Did you add an autonumber primary key field to table 'Import'?
Wasn't ID already an autonumber primary key?

I'm confused.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
Comment Utility
<<I'm confused. >>

 Sounds like he was avoiding the expansion of the fields by adding a foreign key to the table and using that as a pointer.

Jim.
0
 

Author Comment

by:avgplusguy
Comment Utility
Initial input sample which I should have included
ID autonumber field which initially was indexed, then not indexed to try and reduce bloat
most patients have four individual records where important fields are NOT repeated

MRN which on line 1 is populated  not populated for the next three lines
ACCOUNT_NO which is on line 1 is populated not populated for the next three lines
Admit_Date which is on line 1 is populated not populated for the next three lines
Discharge_Date which is on line 1 is populated not populated for the next three lines
Pat_Name which is on line 1 is populated not populated for the next three lines

There are seven other fileds which are populated on all four lines.
ID 1-4 are the same person, ID 5-8 are the same person. ID 9-12 are the same person.
We added link_id as a new field. ID 1 had no link_id value. ID 2,3,4 were updated to link_id 1
ID 5 has no link_id value. ID 6,7,8 have link_ID 5.
Most patients have four records, but acceptable balues are 1-7.

Adding lilnk_id and running essentially the same code only added about 5% bloat instead of 3200% bloat. Then it was a relatively easy matter to join link_id to ID and update the missing fields
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

762 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

11 Experts available now in Live!

Get 1:1 Help Now