I am currently designing a windows form using vb.net. The internet states that 2 gb is the limit for a .accdb file. However i am required to handle data a lot larger then 2 gb. what is the best way to implement this? Is there anyway i could regularly store data to some other access db and empty my main database? (But would this create troubles in migrating data from accdb to the windows form when demanded by the user?)
Edit: I read somewhere that splitting could help. But i dont see how?- it only creates a copy of the database on your local machine in the network.
You can use Linked table of Microsoft SQL Server 2012 Express edition which has 10 GB limit, the maximum relational database size is 10GB.
You can use MySQL Linked table , 2 TB limitation
It's not easy to give a generic answer without further details.
My first recommendation would be to change DBMS and use SQLite which supports roughly 140 TB Limit
If you must use Access then you will need a master database containing pointers to the real location of the data.
E.G. MasterDB -> LocationTable -> (id, database_location)
So if you need a resource you will have to query the master with the id to get its actual location and then connect to the secondary and fetch the data.
Or you could have a mapping model where a certain range of IDs are in a certain database and you can keep the logic in code and access the db once.
Use SQL Server Express. It's free.
Or, if you don't want to use that, you'll need to split your data into different Access databases, and link to what you need. Do a Google search on this and you'll have everything you need to get going.
I agree with the other posts about switching to a more robust database system, but if you really do have to stay in Access, then yes, it can be done with linked tables.
You can have a master database with queries that use linked tables in multiple databases, each of which can be up to 2 GB. If a particular table needs to have more than the Access limit, then put part of the data in one database and part in another. A UNION query will allow you to return both tables as a single dataset.
Reads and updates are one thing, but there is the not-so-trivial task of managing growth if you need to do inserts. You'll need to know when a database file is about to grow beyond 2 GB and create a new one whose tables must then be linked to your master database.
It won't be pretty.