MS Access...Can it handle 3 billion rows?

[biocentrism]

Hello



I am planning on building a database which would have upwards of 3 billion rows and about 10x as many records.

Can Access handle this size database or am I wasting my time? I only know Access database and would like to use it if possible. Having to learn something like SQL Server would be a struggle for me. But if I am headed down a dead-end I would like to know. If anyone has managed an Access database of this size please let me know how it performed.

I guess an alternative would be to break the table up into 26 tables, one for each letter of the alphabet.

Thank you.

[JoeM]

Access databases have a 2 GB limit. I am guessing 3 billion records might exceed that size.
The nice thing about if you put the data in something like SQL or MySQL, you can still use Access as a front-end to access that data (you just don't store the data in Access).

[ItsMe]

Aside from the size limit of the Access file, another consideration is the data type used for Keys. Long Integer is only good for values of 2 billion or so.

One way around this in Access would be to start your key values at -2 billion or use a different data type. I would look to SQL server and use BIGINT for the Keys, at least use BIGINT for tables that would approach 1 billion rows.

Because I do not have actual experience with Access and tables that large, my best guess is you will have performance issues. Using 64 bit Access might offer some help in that regard, but SQL server on actual server hardware would be my choice.

[biocentrism]

Thank you everyone for your feedback!

[knarfreppep]

Thank you everyone for your feedback!

I make no comment on the advisability or otherwise of building a billion-record database in Access.

But I would point out that if one had, for example, a back-end database of 2GB containing, say, 10 tables each of about the same size, one could put each table in its own back-end database for a potential size of 2GB * 10 = 20GB.