Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-02-2019, 01:59 PM   #1
HillTJ
Newly Registered User
 
Join Date: Apr 2019
Location: Tropical North Australia
Posts: 158
Thanks: 61
Thanked 1 Time in 1 Post
HillTJ is on a distinguished road
How Big is 2gb ?

Hi, I understand that the max database size (unless I split it) supported by Access is 2gb. I know this may seem a funny question, but realistically what does this mean (apart from the obvious)? In context, I'm beavering away at my manufacturing application and many of the threads I've posted are a result of my progress. The application includes typical tables for a 'manufacturing' type app. My vision is to capture stock control, contacts, employee records, production reporting, QA records, dispatch etc. I welcome any feedback about members experiences.

HillTJ is offline   Reply With Quote
Old 09-02-2019, 02:18 PM   #2
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,149
Thanks: 0
Thanked 503 Times in 499 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: How Big is 2gb ?

2GB will hold a lot. I have seen posters mention 'millions' of records. Depends on what is stored in database. Embedding files in db uses up limit.

If you are concerned about exceeding limit, look at freebies MySQL and SQLServer Express which have 10GB limit. For more than that will probably have to pay big $.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 09-02-2019 at 02:25 PM.
June7 is offline   Reply With Quote
Old 09-02-2019, 02:22 PM   #3
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,085
Thanks: 110
Thanked 2,739 Times in 2,500 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: How Big is 2gb ?

Almost all the space taken up in an Access file is the data in the tables.
Queries, forms and reports occupy almost no space at all and unless you have a huge amount of code nor do macros/modules

Unless you use things that bloat file size such as attachment fields or regularly use make table queries, it is unlikely you will ever hit 2GB. Splitting your database will make that even more unlikely.
The largest FE file I have is around 150MB and that's a monster.
The largest BE file is around 1.7GB almost entirely due to one table with almost 3 million records and 50 fields. That is a reference datafile so is never edited by users. However when a typical BE file goes much above 1GB, its size should be closely monitored. With regular backups and occasional compacting, it is unlikely to cause problems ….BUT if you ever do hit 2GB, your database may be permanently corrupted.

For info, a BE in SQL Server Express can be up to 10GB

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 09-02-2019, 02:37 PM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,134
Thanks: 46
Thanked 969 Times in 950 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: How Big is 2gb ?

Hi. As others already have said, the 2GB limit is a file size restriction. It doesn’t matter what objects you have in the database, if they occupy disk space, it contributes to the limit. It’s hard to say how many tables you can have or how many records or how many forms and reports, you’ll just have to watch the file size. Doing a C&R can recover some of the file size used, so you might try that occasionally.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 09-02-2019, 03:09 PM   #5
HillTJ
Newly Registered User
 
Join Date: Apr 2019
Location: Tropical North Australia
Posts: 158
Thanks: 61
Thanked 1 Time in 1 Post
HillTJ is on a distinguished road
Re: How Big is 2gb ?

Thanks, your posts pretty much explain the 'scope' I have. I've learn't about 'attachment' files & with assistance from the forum I now archive attachments in a separate folder on a network drive (arnelgp & June7 helped with that). THanks.
HillTJ is offline   Reply With Quote
Old 09-02-2019, 03:14 PM   #6
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,248
Thanks: 86
Thanked 1,624 Times in 1,507 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: How Big is 2gb ?

First, you are not in trouble until a single table reaches 1 Gb, because at that point, an UPDATE that modifies every record will probably crash.

I frequently had tables with tens of thousands and even hundreds of thousands of records, for which serious normalization allowed me to compress the heck out of what I was storing. As I recall, I had maybe 25-30 fields in the main table and a few descriptive (one/many) tables had more fields than that. The down side of serious normalization is that to get an "aggregate" record, I had five-way or more JOIN queries.

The tricks you play to make this work are simple, though.

1. Split the BE into two or three files. EACH can be up to 2 GB, but typically you would split at the point where a file became over 1 GB.

2. Archive things to external files in another format. For instance, I never kept more than six months of event logs online. Once per month I archived the event logs to a text file, after which I erased the records followed by a Compact & Repair. This app also had records that could reach a state of "all required actions complete" and once those records exceeded 90 days old, we archived them to a text report as well. (The times were based on probable need for reports reaching back to some actions.)

3. We moved some temporary tables out of the back end and into the front end, and used the "copy new FE before launch" method to get rid of bloat.

Eventually, if you have records that go back far enough, you will reach a point where they can be archived. If so, your app will reach an "equilibrium" state and you will know just how close you are to the ragged edge of ruin. However, I've never personally brought any app to its knees on size concerns. (Did, however, have speed issues now and then.)

More likely than not, you will find that size won't be the driving force to make you move away from Access. It will be the need to have a dedicated BE active SQL type of server so that you can send pass-thru queries and have the results processed on the server. As long as you are using native Access and a split FE/BE configuration, you take more time with network delays than anything else. Having an active server as a BE cuts back on network load BIG-time.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 09-02-2019, 04:44 PM   #7
HillTJ
Newly Registered User
 
Join Date: Apr 2019
Location: Tropical North Australia
Posts: 158
Thanks: 61
Thanked 1 Time in 1 Post
HillTJ is on a distinguished road
Re: How Big is 2gb ?

OK, so i glean from this thread that I have no immediate concerns (if ever). Thanks to everyone whom contributed.

HillTJ is offline   Reply With Quote
Old 09-09-2019, 11:11 AM   #8
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,002
Thanks: 20
Thanked 376 Times in 369 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: How Big is 2gb ?

There is one area in manufacturing you may want to look at; are you keeping a running log of machine sensor outputs?

If a machine is putting out dozens of reads from meters per minute, recording them may be very useful for tracking performance, but the logs they create will become massive rather quickly. If this is an area you are going into, please start a thread on it.
Mark_ is offline   Reply With Quote
Old 09-09-2019, 12:14 PM   #9
HillTJ
Newly Registered User
 
Join Date: Apr 2019
Location: Tropical North Australia
Posts: 158
Thanks: 61
Thanked 1 Time in 1 Post
HillTJ is on a distinguished road
Re: How Big is 2gb ?

Mark, nothing that ophisticated. I'd love data logging of certain parameters, but that's a project fot the future. Cheers

HillTJ is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 09:49 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World