Help dealing with very large database

[baseball]

I have about 100 years of play-by-play data for major league baseball games that I want to put into Access 2003 (Win 7, 64-bit). This is far more data than Access can handle. In fact, the three tables I now have for 2000 through 2013 have 2,758,933 records, each with 97 fields. Having separate databases to handle all this is a pain because players' careers have spanned into four different decades.

Would splitting the database into front and back ends be a reasonable way of handling this, especially if I reduce the number of years in each table? Or do I have to move on to MySQL (given that I'm highly dependent on Access's Wizards for writing queries, this is not something I look forward to).

Thanks

 

[Joe4]

Splitting the database into an Access front-end and Access back-end probably won't make much of a difference, since all the data will be stored in the backend.
I suggest trying to use MySQL as the back-end to store all the data, and use Access as the front-end. Then you will still be using Access to write all your queries, but have the extra memory you need to hold your data.

 

[baseball]

Thank you. Now all I have to do is figure out MySQL.

 

[SydneyGeek]

You could also use SQL Server Express. Access connects to it quite easily, it's free, and I think you get about 10 GB capacity.

Denis

 

[baseball]

Thanks. I was unaware of SQL Server Express. I'll hjave to take a look.

 

[Andre Uys]

+1 on MS SQL Express. I found this article very helpfull when I switched to SQL Server as the backend Optimizing Microsoft Office Access Applications Linked to SQL Server

 

[baseball]

Thank you. I will look at that article.