mdb 2 giga

[Fabio Furukawa]

Hi
I have a table with 4 million records and the structure is like this
Processing_date Date
Account_number Long
Balance double
LastMonth_Balance double

This table is inserted every month with 400.000 records.
For every record I have to get last month balance and update to the field
LastMonth_Balance of this month record.
I'm using dao to seek the last month record and update the value to this
month record, but when the mdb reaches 2 gb size, the process stops and I
have to compact/repair the mdb and restart the process. Is there a way to
avoid this ?

Thanks
Fábio

 

[Jack Leach]

2gig is the max size for an access database. Some workarounds include
utilizing seperate backends with linked tables, but if you only have one
table that won't work.

Possibly purge old records to a seperate db to free up room in the active?
If you can manage without the records anyway...

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[John Spencer]

If you can use a query to do the update, you will probably get less bloat in
your database. However, the size limit on a JET database (Access native db
format) is 2 gigabytes and when you run into that limit you are going to have
a problem.

If you have to do this, you might consider using a different database engine -
Microsoft SQL Server versions (I think SQL Express will support 4 gigs of
data?) You can check that out.

You might consider posting the code you are using to update records. Perhaps
someone can suggest a more efficient way of updating your records.

Other possibility is to segment your records and store the older records in a
separate database and link to the table(s) on that database when the older
data is needed.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Fabio Furukawa]

Hi
Tks Jack. I was wondering why the dao update method takes so much space.
Once I read that is because the "undo" feature. when I compact and repair the
database, a large amount of space is freed. If its true, is there a way to
"turn it off".

rgds.
Fabio

 

[Fabio Furukawa]

Hi John

It may work John. I'll try it later.

Tks a lot
Fábio

 

[David H]

1) What is the specific reason why LastMonth_Balance needs to be a designated
field in the table? What is the bigger picture as to what the database does?
2) Do you honestly need all 4 million records?
3) SQL Server express allows databases up to 4 GB. I mention SQLServer
Express as I've quickly become a fan of it, not to mention that it supports
triggers which is essentially code that executes any a record is inserted,
edited or deleted.

 

[Tony Toews [MVP]]

Tks Jack. I was wondering why the dao update method takes so much space.
Once I read that is because the "undo" feature. when I compact and repair the
database, a large amount of space is freed. If its true, is there a way to
"turn it off".

Not sure what the exact reason is but this behavior did change from
Jet 3.5 to Jet 4.0. I suspect added records are inserted each to
their own 4 kb page so as to simplify the maintenance of indexes.
Append queries, I think, don't do this but will put as many records on
each page as possible. Also access the database in Exclusive mode
might help as well. Definitely without any other users.

Tony

 

[David H]

Do you *have* to have the last month balance added to the record on the
import? If not have you considered importing the records as-is and then
running code to update the balances?

 

[david]

There was a specific bug associated with record locking. To
get record locking instead of page locking they imported each
record to a new page, and it was happening even when page
locking was active.

Attempts to import duplicate records are also a source of bloat.
All records are imported, then records which cause a duplicate
primary key are deleted.

Generally speaking, the "undo" feature doesn't cause lost space:
there is no "undo" feature. Rather, lost space is a just the way
Access works. You don't get back deleted space until you do
a compact and repair, but you can't use the lost space to "undo"
changes.

(david)