Help with very large database

[baseball]

Using Access 2007 on stand-alone Windows box

I have play-by-play data for major league baseball games going back into the 1950s. This is too much data to get into one database (for example, the 2000-2006 period has 1,355,268 records, each with 97 fields).

I import the data to Access tables from comma-delimited text files but I have ended up with six different databases because of Access size limitations. I know that I can split the tables from a database but then I would have six different front ends. Is there some way I can split all the tables out and then access them from one front end?

If you have a player, such as pitcher Jamie Moyer who began his career in 1986 and is still playing, I'd have to query six different tables in three different databases to cover his entire career. This is unwieldy but I just don't know enough about Access to simplify it (and I use Access's wizards to write queries as I don't know SQL).

BTW, these are not structured databases because the nature of the data does not lend itself to that approach.

Any help would be appreciated.

 

[Derek Brown]

A few thoughts.
Have you tried "Compact and Repair" on your Database? (Warning: make a copy of it first). I do this monthly on a few databasea and the size reduction can be more than 50%. Access 'bloats' very easily in general use so it may be necessary to compact and repair on a regular basis.
An alternative to importing the data would be to have the data in a number of 'linked' tables. The link can be to another database, an Excel workbook, CSV file etc. Depending on what you want from them you may have to consider using a UNION query (not available via the wizard). Not sure whether this would actually make a difference to the size, though.
If you are planning an upgrade to Excel 2010 and think that you can manage this using a pivot table, then Power Pivot (a free Microsoft add-in) may be worth checking out - you can have millions (yes, millions) of rows of data in a PowerPivot pivot table even though Excel has only 1.1 millions rows in a worksheet.

 

[baseball]

The sizes I'm dealing with are after compacting and repairing, running from approximately 1.45 GB to 2.095 GB in size. I simply have huge amonts of data.

I may need to look up PowerPivot. I wasn't thinking about upgradiong my Office suite but I did look into Access 2010, only to learn that the max database size had not been increased and it still uses a single CPU with 1GB memory.

If I start linking tables then I need to master SQL and that's not something I care to pursue on my own. I did wonder after asking my question on this board what would happen if I built a database and import table data, then split it, then import another table, split that, etc. Would this permit me to access all my tables from the same front end and overcome the database size limitation of Access?

 

[boblarson]

My suggestion - go to SQL Server Express 2008. It can hold up to 10 Gb of data and linking to a SQL Server database is not all that difficult.

 

[baseball]

Could you briefly outline the approach? I'm asssuming that I can use Access as the front end but do I import the tables into SQL Server from existing Access databases? And all of the things I see for setting up SQL Server assumes that I'm going to be using the files over a network rather than a stand-alone computer. Or, at least point me in the right direction because I really don't understand thes things.

 

[boblarson]

Microsoft has something called the SQL Server Migration Assistant (SSMA) which you can download and use to move the tables from Access to SQL Server. I would use it instead of the built in Upsizing wizard.

And, you can install SQL Server Express 2008 on your own computer so you don't need to put it on a network location if you are the only one using it. If others are, it would be best to have it on a network server but you can potentially use it on a local machine and open the firewall to let other users access it (although you might need some IT help to get permissions set properly for that).

Here's a link to download SSMA.

And a link to how to configure SQL Server Express 2008

 

[baseball]

Okay, thanks, I'll take a lot at those things and see if I can figure it out. It's my home computer so there won't be any network involved.

 

[boblarson]

And a link to how to configure SQL Server Express 2008

Oops forgot to include the link:

http://blog.mclaughlinsoftware.com/2009/03/30/sql-server-2008-express/

 

[ruge75]

I may need to look up PowerPivot. I wasn't thinking about upgradiong my Office suite but I did look into Access 2010, only to learn that the max database size had not been increased and it still uses a single CPU with 1GB memory.

If you need to manage many millions of data rows and you don't want to upgrade to Excel 2010 I suggest you to try Vizubi. It's an Excel Business Intelligence plugin that use a column oriented database and it's also in memory. But one of the big difference between Vizubi and PowerPivot is that Vizubi is certified also with Excel 2003 and Excel 2007, not only for Excel 2010.
You can test Vizubi for free without any features limitations.