Limitations (1 Viewer)

racdata · Sep 20, 2018

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.

pbaldy · Sep 20, 2018

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.

Cronk · Sep 20, 2018

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.

racdata · Sep 20, 2018

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.

jdraw · Sep 20, 2018

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

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.

plog · Sep 20, 2018

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.

BeeJayEff · Sep 20, 2018

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

Minty · Sep 20, 2018

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.

plog · Sep 20, 2018

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.

BeeJayEff · Sep 20, 2018

plog said:
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 ?

plog · Sep 20, 2018

Didn't know there was a difference until now:

https://volo.net/faq/whats-the-difference-between-megabits-and-megabytes

jdraw · Sep 20, 2018

You can link to tables in multiple database files, each of which can be as large as 2GB
https://dba.stackexchange.com/questions/167731/bypass-ms-access-2gb-file-size-limitation
That's why I said 2GB.

The_Doc_Man · Sep 20, 2018

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.

Pat Hartman · Sep 21, 2018

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.

Cronk · Sep 21, 2018

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.

Limitations (1 Viewer)

racdata

Registered User.

pbaldy

Wino Moderator

Cronk

Registered User.

racdata

Registered User.

jdraw

Super Moderator

plog

Banishment Pending

BeeJayEff

Registered User.

Minty

AWF VIP

plog

Banishment Pending

BeeJayEff

Registered User.

plog

Banishment Pending

jdraw

Super Moderator

The_Doc_Man

Immoderate Moderator

Pat Hartman

Super Moderator

Cronk

Registered User.

Similar threads

Users who are viewing this thread