Size of Ms Access

For Ms Access 2016 the size is 2GB, if we have 100 tables in BE how do we create 20GB space? Its just a suggestion what if we do the following:

(1) Put all related tables with referential integrity in one backed for example a sales accounting will have the following tables :
 - customers
 - Price details
 - Invoice
 - Invoice line datails
 - Products
 - Receipts/Line details
 - Credit notes/Memos
As long as the RI remain intact in blocks of 10 (Total BEs required 10)why can't an application work well, even I have not tried it , I still think that is the other way to get around  2GB limit

Any suggestion?

My current BE has 110 tables but is seating at 2.56MB while my FE is at 11.15MB which quite okay, I'm simply looking at the future, I have seen the challenges on migration , its very involving see below:

 Implementation

Configure SQL Server
Use the data diagram that is part of your development plan to implement the first
version of SQL Server objects, such as tables, views, and stored procedures.
Implement users, groups, and roles as needed. It is important to have these
objects in place before development starts—developers can’t work against a SQL
Server backend that isn’t there. Don’t worry about performance optimization yet,
that happens later.

Development

Based on your development plan, staff your development team and provide the
resources necessary. Make the existing Access application available to the team
for use a benchmark or prototype resource. Keep an eye on the milestones and
risk areas defined in your planning process.

Testing

Before the first test deployment of the new application, basic developer-based
testing should occur. Use the existing Access application as a model to reduce
the amount of time needed for the initial testing effort. Compare each functional
area in the original Access application against the new code base. If you are
completely rewriting the Access front end application as well as moving the data,
you should plan to involve dedicated quality assurance/testing staff to find critical
errors.

Documentation

Most Access applications are created by end users, and as such, lack
documentation. Since you are investing in the process of upsizing, now is a good
time to spend some time documenting the new application. At a minimum, create
a configuration and troubleshooting document that outlines where the
application’s component parts reside, desktop and network settings, and basic
troubleshooting techniques based on the results of your testing plan. If you have
the resources, you may want to consider more complete documentation in the
form of data diagrams, flowcharts, code listings, etc.
LVL 2
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

von AxlSystem AdministratorCommented:
Yes, move to sql database.
0
Gustav BrockCIOCommented:
What is your question? The many tables don't fill much, it is the data you may fill in that will make the database grow.

Create some sample data and follow the size. 2 GB can hold quite a lot of ERP data, so it might do. If in doubt, move to SQL Server Express which will hold 10 GB.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Migrating to SQL Server, or some other server-type database, would be the best way to handle larger database, but if your application is not "SQL Ready" you might have some significant growing pains. You must be sure to use proper client-server techniques, which include pulling over ONLY those records you need in your forms and reports, and creating queries that can make use of SQL Server (no Domain functions, no Form references, etc).

Here's some good reading on the subject:

http://www.jstreettech.com/downloads.aspx (the best of both worlds article)

https://support.office.com/en-us/article/Move-Access-data-to-a-SQL-Server-database-by-using-the-Upsizing-Wizard-5d74c0df-c8cd-4867-8d07-e6e759d72924

http://www.informit.com/articles/article.aspx?p=30118&seqNum=6
1
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<even I have not tried it , I still think that is the other way to get around  2GB limit>>

  That is correct; doing so works fine and this is the only way around the 2GB for JET/ACE.

  But in the end, the limitation is really 2GB per table along with its indexes because you can do RI in code if you really need to have it.

  As has been said, for most apps, 2GB is actually a lot of space for a database and is more than sufficient.  But if you think that you will exceed that in the future, use SQL Server as a back end to store the data.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.