Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-19-2018, 03:43 PM   #1
racdata
Newly Registered User
 
Join Date: Oct 2002
Location: Pretoria RSA
Posts: 71
Thanks: 2
Thanked 0 Times in 0 Posts
racdata is an unknown quantity at this point
Exclamation Limitations

I have a huge problem. I create a database in Access. The db is really big.
As far as I can remember Access have a limit (2gig) of data that can be stored. Is this correct?
If correct then I'm stuck. The data for register an asset, two different inspection and the planning for just one asset will take about 5mb, and there are over 9000 assets. The planning module for maintenance on these assets will also take up space.
This db is to be used over a long time to get information for future planning based on the inspections and trend of wear on the assets.

Is there a way to overcome this or is the a solution to convert to Java. My aim is also to have an App on a iPad where you can do inspections on site and enter the reading.

Is there an easy way out.

__________________
Using - Windows7 Ultimate & Office Professional 2010
Greetings from Pretoria, South Africa.
I don't know everything!!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
racdata is offline   Reply With Quote
Old 09-19-2018, 03:48 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,194
Thanks: 10
Thanked 3,893 Times in 3,836 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Limitations

Yes, the 2gb limit is accurate. Are you storing images or other objects in the db? Most of us just store paths to files, not the objects themselves.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 09-19-2018, 03:50 PM   #3
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 1,922
Thanks: 2
Thanked 412 Times in 405 Posts
Cronk will become famous soon enough
Re: Limitations

Is your database suffering 'bloat'? Try 'Compact and Repair' to reduce the database size.



Are you storing redundant data? That is, is your database fully normalized?



Does your asset database store images? These can take up a huge amount of space. Good practice is to store images externally to the database and maintain just a list of file names in database tables.


Another step is to split your data between two or more databases, each of which has the 2GB storage limit.

Cronk is offline   Reply With Quote
Old 09-19-2018, 04:00 PM   #4
racdata
Newly Registered User
 
Join Date: Oct 2002
Location: Pretoria RSA
Posts: 71
Thanks: 2
Thanked 0 Times in 0 Posts
racdata is an unknown quantity at this point
Re: Limitations

No images only relevant data from asset register and inspection readings.
A realistic figure is about 10gig space per year that will be used. I spend so much time to create this db and really don't want to do it all over from start. Convert to Java or SQL might be the solution but I don't know if it can be done and I don't know how.
__________________
Using - Windows7 Ultimate & Office Professional 2010
Greetings from Pretoria, South Africa.
I don't know everything!!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
racdata is offline   Reply With Quote
Old 09-19-2018, 04:00 PM   #5
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,603
Thanks: 58
Thanked 1,885 Times in 1,835 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Limitations

As others have advised the 2GB limit is real. Stored images eat up space; better to store paths to files on filesystem.

Quote:
Is there a way to overcome this or is the a solution to convert to Java. My aim is also to have an App on a iPad where you can do inspections on site and enter the reading.
I don't see where/how java would be involved. You could move to SQL Server (express) a free option.
Not sure what your development path to Ipad app would be??? I have not used Ms Access on it.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 09-19-2018, 05:01 PM   #6
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,992
Thanks: 10
Thanked 2,160 Times in 2,115 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Limitations

I looked it up and 1 mb is equivalent to 500 pages of text. Do you really have the equivalent of 2500 pages of text per asset stored within fields of a table?

I'm with pbadly on this one---you most likely are storing files in your database. Perhaps not images, but files that would be best stored outside the database. If that's the case, you shouldn't be loading that into the database but storing it on disk and then storing the path to those files in the database.
plog is offline   Reply With Quote
Old 09-20-2018, 01:48 AM   #7
BeeJayEff
Newly Registered User
 
Join Date: Sep 2013
Location: Somerset, UK
Posts: 198
Thanks: 37
Thanked 10 Times in 10 Posts
BeeJayEff is on a distinguished road
Re: Limitations

Can I enter a plea for folk to use Mb/MB or Gb/GB correctly, please ? There is nearly an order of magnitude difference.

BeeJayEff is offline   Reply With Quote
Old 09-20-2018, 01:53 AM   #8
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,798
Thanks: 140
Thanked 1,565 Times in 1,537 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Limitations

Another +1 for pbaldy - this sounds like image storage or bloat within the database.

We have 100,000's records in many tables (in SQL Server) (over a million records in one table) in one database and it isn't even approaching 2Gb in size.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

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.
Minty is offline   Reply With Quote
Old 09-20-2018, 04:57 AM   #9
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,992
Thanks: 10
Thanked 2,160 Times in 2,115 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Limitations

Quote:
Can I enter a plea for folk to use Mb/MB or Gb/GB correctly, please ?
Only if you tell us what post(s) it was used incorrectly in.
plog is offline   Reply With Quote
Old 09-20-2018, 05:15 AM   #10
BeeJayEff
Newly Registered User
 
Join Date: Sep 2013
Location: Somerset, UK
Posts: 198
Thanks: 37
Thanked 10 Times in 10 Posts
BeeJayEff is on a distinguished road
Re: Limitations

Quote:
Originally Posted by plog View Post
Only if you tell us what post(s) it was used incorrectly in.
2gb in pbaldy's, 2GB in jdraw's, for example. What is your 1mb - presumably either Mb or MB - but which ?
BeeJayEff is offline   Reply With Quote
Old 09-20-2018, 05:41 AM   #11
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,992
Thanks: 10
Thanked 2,160 Times in 2,115 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Limitations

Didn't know there was a difference until now:

https://volo.net/faq/whats-the-diffe...-and-megabytes
plog is offline   Reply With Quote
Old 09-20-2018, 05:50 AM   #12
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,603
Thanks: 58
Thanked 1,885 Times in 1,835 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Limitations

You can link to tables in multiple database files, each of which can be as large as 2GB
https://dba.stackexchange.com/questi...ize-limitation
That's why I said 2GB.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 09-20-2018, 02:31 PM   #13
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,574
Thanks: 62
Thanked 1,199 Times in 1,099 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: Limitations

If you really ARE storing 5 Mbytes of data per single asset, that is a LOT. I once implemented an oil-and-gas pipeline using two disks of 5 MByte each, and one included the O/S, the application, and some user tools. The second disk was pure user data. That's a LOT of data. I'm with the others who question whether there is a normalization issue. If a lot of this stuff is "boilerplate" text then you should not be storing it more than once.
__________________
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-20-2018, 10:27 PM   #14
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,448
Thanks: 13
Thanked 1,422 Times in 1,355 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Limitations

Quote:
or is the a solution to convert to Java
Last time I checked, Java wasn't a databae engine.

Just FYI, you are using Access as the FE. Access is a Rapid Application Development (RAD) tool which is used to create an application interface. Jet (.mdb) and ACE (.accdb) are database engines. THEY are responsible for managing your data. many people just call everything Access but there is a real difference. Access, the RAD tool, can access data in ANY ODBC compliant database and so it isn't dependent on Jet/ACE as its data store. Java can access data in Jet/ACE or SQL Server and probably any other ODBC database. Access and Jet/ACE have a symbiotic relationship. Access uses Jet/ACE to store its application objects (forms, reports, macros, queries, code) and that is why your application is stored in an .mdb or .accdb but can store data in ANY ODBC compliant database. Jet/ACE are completely independent from Access and Access does not need to even be installed on a computer that is using ODBC to access Jet/ACE. However, Access does provide a nice GUI for Jet/ACE just as SSMS provides a nice GUI for SQL Server.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 09-20-2018, 11:52 PM   #15
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 1,922
Thanks: 2
Thanked 412 Times in 405 Posts
Cronk will become famous soon enough
Re: Limitations

Coming back to 5MB per record, that has to mean memo fields.

Conservatively
5MB = 2500 book pages at 200 words per page with 10 letters per word

(including spaces and other punctuation). That's a heck of a manual for each asset.

Cronk is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
DLookup limitations? xracingdude Tables 2 03-04-2008 09:35 AM
Limitations of ADO? JapanFreak Modules & VBA 5 08-26-2007 10:51 PM
Access Limitations!!?? davebhoy General 0 10-31-2006 11:24 AM
Combo Limitations? andysgirl8800 Forms 14 01-24-2006 05:48 AM
Limitations orbic1 General 15 04-07-2004 09:28 AM




All times are GMT -8. The time now is 11:55 AM.


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 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World