Calculating Space for Records in a Database (1 Viewer)

DBApprentice

On Error GoTo AWF *****:
Local time
Today, 14:36
Joined
Dec 24, 2018
Messages
150
Hellos Masters!

I am here again evoking your wisdom!

How can I calculate the capacity Access has to handle records in a database?

I have seem some people saying it has up to 2gb per table, but it looses Relational Integrity in some cases.

Specifically in my case, I am learning DB Design and following Hernandez DB Design Process and I came to 17 tables, they will hold a total of approximattely 85 fields and I have set them to the number of characters in case of texts and some are long text fields. The Audit Trail and User Activity tables are set to have replication ID. I am building this to last at least 10 years receiving up to 300 records per day (Will hold at least 1.095.000 records) give or take.

How can I know for sure how to correctly calculate the space this could take in Access and if it could handle that amount of records before going for archiving or sanitizing records?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:36
Joined
Oct 29, 2018
Messages
21,358
Hi,


Access has a file size limit - not necessarily a limit on the number of records per table. For example, let's say all you want to store are Text information in your table, it might be possible to store (I'm just exaggerating here) to store 10 billion records in one table or maybe 1 billion records each in ten tables. However, if you were planning on storing video files in your Access database, then perhaps you can only save up to ten video files in one table - that's it.


So, I wouldn't worry too much about the number of records but just watch the file size as you go along. Otherwise, if you think you'll need more space for the file than 2GB, then perhaps consider using SQL Server instead.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:36
Joined
Feb 19, 2013
Messages
16,553
but it looses Relational Integrity in some cases
not to my knowledge

With regards sizes, you can make a reasonable calculated estimate based on field type and indexes times the number of records. See this link for examples https://www.oreilly.com/library/view/access-database-design/1565926269/ch10s04.html

Note that strings take up the actual space required (plus 10 bytes) not the space limit - so a field which allows 255 chars but has a value in one record of 'xyz' will take 13 bytes for that record, not 265. So you will need to make an estimate of the number of chars for these fields

For indexes, you need to add the field plus space for a pointer (to the record) which I think is 4 bytes for a 32bit application and 8 bytes for 64bit application

One of the reasons it is better to use numerical indexes (4 bytes) rather than text for large tables. Not sure why you are using replication ID's they are 16 bytes rather than 4.

Further the db has its own system storage requirements for tracking tables, indexes, relationships and the like. However this is quite small - create a db, don't add anything, compact it and the files size is the minimum it can be so needs taking off the 2Gb limit. As you add tables, relationships etc this requirement will grow, but not by much. And of course queries, forms, reports will be in a different front end file so will have no impact.

Finally, remember that deleting records or amending them can lead to gaps in the database continuum (same as happens on your drive when files are deleted or modified) which is resolved by regular compacting - equivalent to defragging a drive.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:36
Joined
Feb 28, 2001
Messages
26,999
CJ covers it quite well for most things, but I believe there is a bit of overhead for even a null string because what is ACTUALLY stored for a string is a length and offset. If the length is zero, the offset is immaterial. If the string is non-zero then what happens is that all the fixed-length stuff is stored in order, but in the slots for SHORT TEXT and for MEMO (LONG TEXT), there is a place-holder/pointer that defines how many bytes are stored and WHERE they are stored. For a SHORT TEXT the strings are stored immediately after the rest of the record and that offset field points to where the first byte of the SHORT TEXT field is actually stored. The size field is one byte because SHORT TEXT cannot exceed 255 bytes. The offset is probably not more than 2 bytes because the offset potentially only has to point to something within a 2048 byte record (the maximum Access record length not counting large objects) and that only would take 12 bits. For a LONG TEXT and a BINARY LARGE OBJECT (BLOB), I don't know where that is stored but it is NOT necessarily kept close to the base record.

Therefore, in dealing with SHORT TEXT strings, add 3 bytes overhead to your estimate plus the string length expectation. If you have statistics on the distribution of sizes for your text field, your "expectation value" is either the average string size, or the statistically most likely string size if that isn't 50% of the stated maximum SHORT TEXT size. The more you know about your expected strings, the better off your guesstimate will be.
 

DBApprentice

On Error GoTo AWF *****:
Local time
Today, 14:36
Joined
Dec 24, 2018
Messages
150
not to my knowledge
One of the reasons it is better to use numerical indexes (4 bytes) rather than text for large tables. Not sure why you are using replication ID's they are 16 bytes rather than 4.

The reason I have opt for Replication ID is because I have taken the code and technique from Steve Bishop´s "Programming Access" series and he uses that. I know Long Integer could leade me to more than 2 million records without running out of IDs.

What you recommend: Replication ID or Long Integers for a table that will track the editions made to records on a database? :)
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:36
Joined
Feb 19, 2013
Messages
16,553

DBApprentice

On Error GoTo AWF *****:
Local time
Today, 14:36
Joined
Dec 24, 2018
Messages
150
long integers - it is actually circa 4 billion, not 2 million - you can use negatives as well (choose the random option rather than increment)

Because of their size, indexing replicationID's can be slow, particularly for large datasets - simplistically 4 times slower than longs - see post#1 of this link

https://www.access-programmers.co.uk/forums/showthread.php?t=291268&highlight=indexing&page=4

A-ha! I didn´t know I could use negative Autonumbers to broad the number, but I guess 2 billion is large enought for my database.

Thanks a lot for your help!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:36
Joined
Feb 19, 2013
Messages
16,553
I didn´t know I could use negative Autonumbers to broad the number
the purpose of an autonumber (or to be more specific, a primary key) is simply to be unique so you can uniquely identify a specific record. It should have no other meaning than that, so signage is irrelevant.
 

Users who are viewing this thread

Top Bottom