Microsoft Access Capacity Planning

[LizA]

Hi,

Im had to plan out the capacity the microsoft access database can hold. I am
supposed to find out how much is the size for each record per table and times
it with approximately the number of records extimated.

For example

I have table account_tbl, product_tbl, order_tbl

Let say i have 3 fields in each table
1) table_account - name_field, id_field, password_field
2) product_tbl - productId_field, productName_field, productDesc_field
3) order_tbl - orderId_field, orderDesc_field, orderDetails_field

Thus for each row of record in each table, i need to estimate the
approximate size of the data, and times it with the total record in the table.

How do i go about calculating and finding out the file size. I mean the
queries, reports and forms all goes together contributing to the file size.

I've tried trial and error deleting and adding records and queries,
tables.... to find out the file size but its seems that the file size stay
unchanged.

Please help me with this. Thanks

 

[John Vinson]

I've tried trial and error deleting and adding records and queries,
tables.... to find out the file size but its seems that the file size stay
unchanged.

Have you Compacted the database? The space occupied by deleted records
or objects isn't recovered until you do so.

One other factor: as I understand, Access grabs new disk space in
32KByte chunks, so your measurements will necessarily be coarse.

Given the vagaries of index sizes, the undocumented nature of
user-interface objects, and so on, this is probably an exercise in
futility. You can certainly store different tables in different
backend databases (linked to a common frontend), if you recognize that
you cannot enforce relational integrity between different databases;
so long as each backend stays under the 2 GByte limit Access will
work. In practice, if you are storing multiple gigabytes of data, a
client-server solution such as SQL/Server or MySQL or Oracle will be a
better choice; if you're talking about tens to hundreds of megabytes,
just compact your database regularly and don't worry too much about
the exact size.

John W. Vinson[MVP]

 

[Marshall Barton]

Im had to plan out the capacity the microsoft access database can hold. I am
supposed to find out how much is the size for each record per table and times
it with approximately the number of records extimated.

For example

I have table account_tbl, product_tbl, order_tbl

Let say i have 3 fields in each table
1) table_account - name_field, id_field, password_field
2) product_tbl - productId_field, productName_field, productDesc_field
3) order_tbl - orderId_field, orderDesc_field, orderDetails_field

Thus for each row of record in each table, i need to estimate the
approximate size of the data, and times it with the total record in the table.

How do i go about calculating and finding out the file size. I mean the
queries, reports and forms all goes together contributing to the file size.

I've tried trial and error deleting and adding records and queries,
tables.... to find out the file size but its seems that the file size stay
unchanged.

You will have to add a LOT of records to figure it out by
just adding records.

I used to know all this stuff, but the old memory is fading
and I can't find it in Help.

Each numeric field takes the number of bytes specified in
the field size, e.g. Integer - 2, Long - 4, etc.

I think a text field takes 4 bytes plus one byte for each
character in the text.

There is also some relatively small amount of overhead for
each record and some more for each index.

Note that your application should be split to a front end
with everything except the data tables and a back end with
just the data tables, so the only concern is the data
tables. Note that you can use multiple back end databases
with the restriction that a table must be in a single file
and Referential Integrity can not be enforced between
saparate files.

Each mdb file is limited to 2GB.