MS Access Database size limitation

[VegetaOSX]

We have a lot of data which needs to be stored in a Database and people who are interested in analyzing the data will connect from Excel and create Pivot Tables. But the data occupies like 6GB+ and growing.

We have a huge problem, MS Access only has maximum of 2GB. What can I do to solve this problem?
We need to maintain the historical data in chronological order and we want to have it in a single database.


I have checked SQLite, but...it has no GUI and ease of use as MS.Access. I regularly need to write, modify 800K+ rows each time. How could I do that?
Will SQLite allow multiple connection from Excel to the database? Will it easily hold 6GB of Data? How do I input 800K+ data each time?



Thank you.

[alansidman]

A possible solution may be to put tables in multiple Access Files and link the tables to one file in which you can run your queries and create relationships. Then link the Excel operators to the file which contains the queries.

[xlnitwit]

If you have 6GB+ of data, you need a proper database server, not Access, in my opinion. You may wish to look at something like SQL Server Express (handles up to 10GB if my memory doesn't fail me) which you can then upgrade to a full SQL serve edition when necessary, or look into something like mySQL.

[The_Doc_Man]

Don't forget that Access allows you to connect to an SQL back end but retain the full power of the front end. If you are worried about the interface, just look into upsizing the back end only.

[mel0nc0lly]

Remember you can LINK to tables in other databases, so you might want to split out your database into single table access databases, then use one (empty) database and link all the other databases and tables into it.

See MS Support doc:
here

Also, try compact and repair - this often reduces size by an amazing amount