Determining max theoretical size of database

[ironfelix717]

Hi all!



With my database project early in production, I would like to eliminate any possibilities of 'lack of space' later before i go coding an entire front end. I am aware of the 2GB max accdb file size. But was wondering if someone could give me some more sense of security by answering a question or two. I'd like to know early whether i need to design a multi-backend database set or am fine with one master backend.

Scope of this DB would be long-term (5 years +) use as a full ERP system.

Lets assume this one backend file would...

• Contain no forms and no attachments
• Contain the following tables (estimate)
• 20 x tables: 20 fields, 100 records - short text datatype
• 15 x tables: 35 fields, 20,000 records, short text & number (50/50)
• 20 x tables: 20 fields, 20,000 records, long text

Is this within the scope of my 2GB back end file?

I guess, really what I am asking, is how would you recommend calculating the disk space used for each data type? Provided the majority of my DB will encompass short text and numbers, I'm not well versed in the number of bits, say, a 50-character string contains or a 300 digit number contains.

Thanks for the help!

[kd2017]

You forgot to state your questions

[Minty]

This may sound daft, but only you know your exact type of data.
So why not simply mock up the exact scenario you have suggested with dummy data, and see how big the file gets.

It wouldn't take long to run some make table queries, or import some excel tables with the sort of data you are describing.

Once done then add and remove some data from some tables without running a compact and repair, see if things "grow" out of hand.

[CJ_London]

Is this within the scope of my 2GB back end file?

you are stating a tiny amount of records - perhaps only 0.1% of capacity. Multiply by 1,000 and perhaps you should be concerned about hitting the limit.

here is a link to field sizes for reference
https://www.oreilly.com/library/view...9/ch10s04.html

with regards calculating your db size, you can use these values for each field - text is a bit of an unknown since it is data dependant and only takes the space actually required - i.e. a 3 character string will take 13 bytes, a 10 character string 20 bytes. With regards numbers, with the possible exception of autonumber (long), choose a type which is no bigger than required

if a field is indexed, you need to double it and add (I think) 7 bytes for the pointer. Sometimes a field may be indexed more than once - look at the indexing parameters for the table

Those are the 'big' numbers, An Access database also has system tables and other hidden objects which take space - and will grow as you add more tables/objects/relationships etc. To give you an idea of the minimum number, create a new db and see how big the file is, however overall will have minimal effect on the size of your db.

bit concerned about what you mean by a 300 digit number

[isladogs]

Just to reinforce Ajax's points about using the most appropriate datatype and indexing.

I have s UK postcodes BE data file which I often refer to in forum answers.
It is currently around 1.6 GB and almost all of that is in one table of 2.6 million records and 54 fields.
Other than being updated every 3 months it doesn't get altered by end users.

However I was naturally concerned about file size and performance issues when I first worked with this file.
I checked every field and optimised the field sizes for text and used the smallest number type possible for each field
e.g. Byte where possible, single instead of double for coordinates etc.
This reduced the file size by around 350 MB.

However, searching was very slow so I indexed all the fields that would ever be used in searches.
This increased the file size again by around 200 MB but searches are now very fast indeed.