I have spent years using Access for adding fields, updating, linking tables etc (all the usual stuff using query design grids) and I would like to be able to do all that with the latest dataset I have but this one has over 15million records.

Clearly Access won't handle this so I'm asking for some help in deciding what I do.
Firstly, I don't mind learning and I recognise that a steep learning curve is in front of me as I suspect I will need to learn a language
I want a database that is relatively inexpensive or free but if throwing a couple more hundred quid at it will make a big difference I would rather do that.
I want it to be able to handle 15m records reasonably or very well
I want it to be intuitive - I am not a programmer (yet) but have good instincts with data

This is your opportunity to direct a database novice in the right direction. Estimates of how long I will need to get up to speed to be able to make progress with my data would also be welcome.

Any help gratefully received.

Edgeguy

I don't have experience with databases of that size but I found this article that will hopefully be of help http://www.sitepoint.com/article/hig...base-solutions

Consider oracle, I think it is more geared toward databases of 10s to 100s of thousands rather than millions but it should be an improvement over access. I know oracle 9i could access files of 1TB in size and I think the latest version is 11g so that number should be higher.

Noooo, Oracle is more than capable of handling 10s of millions of records and can be superb to work with and develop apps for, Just the price is a little steep to say the least
But if you just want "playing" time to learn with, you can Download Oracle 10g and use the free Developer Licence.

MySQL might get a bit flakey with that number of rows but it's stablemate PostGre would be perfectly Ok. Both free under the right licensing use

MS SQL Server, shouldn't have any problems and the Access stuff should translate straight over. Licensing is pricey though.

Originally Posted by Edgeguy

Clearly Access won't handle this so I'm asking for some help in deciding what I do.

Why not? I don't use Access very much, but the three people in this forum who post a lot and know their stuff swear by it. Adam, Chris, and Forrest. They've all talked about big Access files, so I've changed my mind from thinking Access was a Tonka Truck to a real database.

How big is each record?

Originally Posted by chrishirst

MySQL might get a bit flakey with that number of rows but it's stablemate PostGre would be perfectly Ok. Both free under the right licensing use

MySQL shouldn't have a problem. The only one would be like for any other DBMS: the server setup and how the database is set up and queried.

BTW, the company I work uses MySQL that handles a lot more data than 15 million rows.

Originally Posted by joder

BTW, the company I work uses MySQL that handles a lot more data than 15 million rows.

How wide is each row? I know enough about databases (some) to know I could make a table in Access and get it 50 million rows if they're narrow enough.

Create Table JustATest ( RecordID int Primary Key, RecordValue Bit )

Then use some VB to loop however many records and spit random values. If that ID is a foreign key pointing to some other table and only giving it a new property in a relational schematic way, I bet even wimpy Access could handle it?

Only I think Access uses its own wonky data types. Int might be numeric or something like that, and I think bit is yes/no? Basically, they set it up to be easy to explain to your boss.

the company I work uses MySQL that handles a lot more data than 15 million rows.

I'm sure you have. Maybe I should have qualified my answer a little. With a schema that is well designed for MySql, a billion row database would not be a problem.
But converting an Access based schema directly to MySql (column type changes not withstanding) and you can start to see slowdowns and glitches with 1/2 million row tables.

You could even use Access for million+ row tables, provided you could keep below the 4Gb file size limit.

You can continue using Access as a front-end and use MSDE or SQL Express as the back-end. MSDE could have a single database upto 2 GB, and SQL Express upto 4 GB. Both are free to use.

In SQL 2005, the standard edition that doesn't do the table and index partitioning like the enterprise version does, I've had 250 million records in a single table. They started out having a lot of data per record, so I did a lot of research into how it gets used and broke the field set up into four tables. With unique clustered indexes, joins were almost no work. And most queries could be run against two tables at most, often one.

In Access, the way it's typically run, I've got a table with 1.3 million records - IIS logs - and with indexes where they should be, it performs a lot better than I would ever expect.

I've seen databases for MySQL, Oracle, SQL Server, Access. Some that were accessed quickly, others that were horribly slow, and speeds in-between. With 20 records up to 10's of millions. The key is the database design and administration. If a database is designed poorly or not administered correctly, any of them will be slow. Conversely, any can be reasonably fast.