0

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.

Curiosity101
  • 51
  • 8
  • 3
    Simply use a different RDBMS that support a larger size. There are many available for free – Matt Wilko Jul 19 '16 at 12:35
  • 2
    You can link objects in multiple Access databases. Or you could consider a more capable platform such as SQL Server/SQLite/MySQL ... – Alex K. Jul 19 '16 at 12:36
  • could you give a rough estimate as to how many entries in a accdb file would constitue 2 gb if there are 10 fields? – Curiosity101 Jul 19 '16 at 12:41
  • Tagging off of Matt Wilko's comment, try SQL Server Express. https://www.microsoft.com/en-us/cloud-platform/sql-server-editions-express – Jeremy Jul 19 '16 at 12:43
  • To echo what others have already said, it sounds like you very well might be using the wrong tool for the job, if you're hitting filesize limitations of the tool that you're using. 2GB is a pretty substantial amount of data for access, so I'd be more than willing to bet that you'd end up with performance issues within the accessdb anyways. As for what would constitute 2gb with 10 fields? It depends a lot on what type of data it is, how fragmented the database is, and may very well depend on the data itself, there's no easy catch all answer to predict this. That being said, it's quite a bit – user2366842 Jul 19 '16 at 12:54
  • To emphasize: Your VB form could be just fine, it is Access that can't handle 2GB of data... [Access 2010 specifications](https://support.office.com/en-au/article/Access-2007-specifications-2eedf198-6b27-4dc5-ae07-3e1fba6d6c96) – marlan Jul 19 '16 at 14:15
  • Will the select query from the windows form work real slow and affect the execution speeds of the form because the accdb has say more than 1000 rows and it keeps growing? – Curiosity101 Jul 20 '16 at 03:51

4 Answers4

1

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

Sham Yemul
  • 463
  • 7
  • 30
0

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.

Community
  • 1
  • 1
Stefano d'Antonio
  • 5,874
  • 3
  • 32
  • 45
0

Use SQL Server Express. It's free.

https://www.microsoft.com/en-us/cloud-platform/sql-server-editions-express

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.

ASH
  • 20,759
  • 19
  • 87
  • 200
-1

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.

IndyChuck
  • 42
  • 5
  • 2
    **Not recommended idea**, [please read this](http://stackoverflow.com/questions/11257326/how-to-increase-ms-access-2007-database-size/11270194#11270194) – marlan Jul 19 '16 at 14:10