File Size Spiralling Out of Control

[rm]

What have I done wrong? Our database has ballooned. I was worried when
the file size moved to 20 MB. But now the file has grown by 67,653,632
bytes while we have on added 650 + 339 records! When I compact and
repair the size moves back down to 10 Mb. Here is the def of the two
primary tables. There are 6 lookup tables - but there has not been
more than 5 records added to those tables in the last 4 months.

I am using lookup fields. I didn't realize that I shouldn't be using
them (per the MVPs that so graciously offer advice on this NG) unitl
it was too late. Could that be doing it?

Table1

id AutoNumber Long Integer PK
pid Long Integer
field1 Text(10) Unique Index
field2 Long Integer
field3 Long Integer
field4 Yes/No
field5 Date/Time
field6 Long Integer
field7 Date/Time
field8 Date/Time
field9 Date/Time
field10 Date/Time
field11 Date/Time
comments Text(255)

Table2

id AutoNumber Long Integer PK
field1 Text(18)
fname Text (45)
mname Text(45)
lname Text(45) Index Non Unique
field2 Long Integer
gender Text(1)
DOB Date/Time
field3 Date/Time
comment Text(255)

Some time in August-

Select count(id) FROM Table1 38,719
Select count(id) FROM Table2 22,611
Total 61,330 Records
DB Size on Disk 11,096,064 bytes

--------------------------
Some time in September

Select count(id) FROM Table1 39,369
Select count(id) FROM Table2 22,950
Total 62,319 Records
DB Size on Disk 78,749,696 bytes

 

[rm]

Table2 is the parent of Table1. "pid" in table1 is "id" from Table2.
And btw - no, these are not the real field names.

 

[boblarson]

How are you "adding" your records in?
--
Bob Larson

Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

 

[rm]

Through a form. The forms are bound to the tables. The form for Table2
is the main form. The form for Table1 is a subform.

My biggest sin is probably the lookup fields. But would that cause
gargantuan growth like this?

 

[boblarson]

Normally, things that generate and delete objects are the main causes of
bloat, but if you are storing OLE objects that can do it as well.
--
Bob Larson

Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

 

[rm]

Thank you for the help - No OLE stuff here sir.

One thing that I noticed - Where I have relationships I have two
indexes on one of the tables.

For example: Table1 and a "lookup" table - employer. The child record
in Table1 from "employer" is "eid". In Table1 two indexes are listed
"eid" and "employerTable1". (I am getting index names from the MS
Access Database Documenter tool)
Should I have 2 indexes there? This is actually a consistent condition
for all "lookup" tables only on Table1. So I have 7 indexes on Table1
plus the primary key with 39K records. The Table2 has only 4 indexes
plus the PK with 22K records.

What I notice is that there are no "duplicates" on the indexes for the
"lookup" tables in Table2. Also, I don't recall ever creating 2
indexes on any one field.

Another thing - we didn't start having this problem until we
"upgraded" the database to the latest version of Access - 2007 (8?).

Is this reason enough that the database size to go from 10 Mb to 54 Mb
within 5 hours use?

(for those interested Table1 and Table2 are not the actual table
names).

This is a mission critical application. I fear that the app is about
to collapse!