Database Exceedes Maximum Size

[DB]

Hi. I have a fairly simple piece of code which reads each record in a table,
does some calculations based on several different fields, then updates a
field in the table. This is done in VB. On some occasions, for which I can
find neither rhyme nor reason, the database grows tremendously in size and
effectively blows up - exceeds the 2GB maximum size.

The starting DB is typically in the 150MB range. The records in the table
vary from 400k - 900k. I am totally stymied as to how the DB can grow 10+
times it's size by simply updating the records through VB code.

Any thoughts are greatly appreciated. This is a major hassle for me.

Thank you.

 

[Daniel]

You are going to need to supply us with the code so we can review it.

On a seperate note, do you compact and repair you db on a regular basis?
Just a thought.

Daniel

 

[Larry Daugherty]

Daniel's ideas are good ones. If you compact before you execute your
code is the database size always the same? The other question is do
you always compact your database? FWIW I automatically backup,
compact and repair my critical applications every time I use them.

A note on a much higher level is that you are causing E.F. Codd and
C.J. Date a great deal of anguish! (They are the folks who invented
the rules for relational database management systems while at IBM in
the 1970's). No field in a record may depend in any way on any other
field in the record. Each field must accurately reflect the state of
that single attribute in the current record. Stated differently,
"Each value in each field of this record is based on the Primary Key,
the whole key and nothing but the key, so help me Codd"! Where you
are analyzing fields within a record and then altering the value of
any field in that record (or any other record) based on that analysis,
you are breaking the rule. I suggest you visit www.mvps.org/access
and look for the "Rules ..".

HTH

 

[DB]

Here is a sample of the code:

Private Sub CalculateWhiteCollarPercent()
Dim tblBTBRecords As DAO.Recordset
Dim tblWhiteCollar As DAO.Recordset
Set tblBTBRecords = CurrentDb.OpenRecordset("tblBTBRecords")
Set tblWhiteCollar = CurrentDb.OpenRecordset("tblWhiteCollar")
tblWhiteCollar.Index = "PrimaryKey"
With tblBTBRecords
Do Until .EOF
tblWhiteCollar.Seek "=", Left$(!SIC, 3)
If Not tblWhiteCollar.NoMatch Then
.Edit
!PercWhiteCollar = tblWhiteCollar!PctWh
.Update
End If
.MoveNext
Loop
End With
End Sub

This is NOT the only code segment for which this occurs. There are numerous
others seemingly as simple.

In this specific case, I compacted the database before running this code.
The DB size was roughly 175MB. There were about 900K records in the table.
I tossed in a debug statement to "print" every 10,000 records. After 520,000
records I received the Invalid Argument error as the DB exceeded the 2 GB
limit in size.

Still clueless as to why it blows up this way.

Thanks for the help.

 

[DB]

Here is a sample of the code:

Private Sub CalculateWhiteCollarPercent()
Dim tblBTBRecords As DAO.Recordset
Dim tblWhiteCollar As DAO.Recordset
Set tblBTBRecords = CurrentDb.OpenRecordset("tblBTBRecords")
Set tblWhiteCollar = CurrentDb.OpenRecordset("tblWhiteCollar")
tblWhiteCollar.Index = "PrimaryKey"
With tblBTBRecords
Do Until .EOF
tblWhiteCollar.Seek "=", Left$(!SIC, 3)
If Not tblWhiteCollar.NoMatch Then
.Edit
!PercWhiteCollar = tblWhiteCollar!PctWh
.Update
End If
.MoveNext
Loop
End With
End Sub

This is NOT the only code segment for which this occurs. There are numerous
others seemingly as simple.

In this specific case, I compacted the database before running this code.
The DB size was roughly 175MB. There were about 900K records in the table.
I tossed in a debug statement to "print" every 10,000 records. After 520,000
records I received the Invalid Argument error as the DB exceeded the 2 GB
limit in size.

Still clueless as to why it blows up this way.

Thanks for the help.

PS: The value is calculated and stored in the table so that it may be
shipped to the customer for simple queries. Ideal database design? No.
Practicle for the customer? Yes. Customer wins

 

[Albert D.Kallal]

There is a number of bloat issues that are fixed with the jet updates. You
don't mention what version of ms-access, but installing the updates to
ms-access, and ALSO installing the updates to JET do fix a number of issues.

It is moot point that you could replace your looping code with a single sql
update, but lets leave that for now (the code would run faster also).

You code looks fine, and while some serious bloat can occur, give your size
of the database when you start, I think a update to JET would fix this
problem for you...

You can read about some solutions to bloating here:

http://www.granite.ab.ca/access/bloatfe.htm

Also, before you check the above, make SURE you turn off record locking...as
that might be the problem here too.

that is in tools->advanced tab

set default record locking to no locks, and un-check the "open databases
using record locking".

Compact...exit after do the above..and now re-enter...and try your code...

(row level record locking is a known bloat issue, since the record are
PADDED to force fit into frame so not other records get locked when
ms-access locks the page. So..you *most* defiantly want to check the above
options).