Access lost my query

[baseball]

Access 2007 running on Win 7 x64

query written with Create/Query Design tool and contains the following fields:

gameID (text)
vis (text)
bat (0 or 1)
outs (0, 1, or 2)
vscore (an integer)
hscore (an integer)
delta: ([hscore]-[vscore]) (number that is greater than, less than, or equal to 0)
batter_event_flag (set equal to "T")

This is play-by-play data for baseball games. Each record contains 96 fields and a season contains about 200,000 records, so I have divided the season tables into five year periods. I was trying to get a count of the batter events for each of the out states (0,1,2) for each of the score states (-1,0,1). I did this for all of the states with Table Retro00 and recorded each of the counts, then I went to Design View for the query and added table Retro05, changing all the sources to reflect that. Still worked fine. Lastly, I added table Retro10 (and deleting Retro05), at which point Access told me that it could not find the query and I was getting messages like "Reserved error (-3001); there is no message for this error." Access will not let me delete the query.

I have no idea why things stopped working or why I cannot simply delete this query and start over. I have 50.8 GB free on my hard drive, so it shouldn't be a resource problem. The Table Retro10 works with other queries.

Ideas?

 

[baseball]

Move to SQL Server question (was: Access lost my query)

As an update to this, it may be that the size of my database (1.99 GB) is causing problems and it looks as though the easiest solution would be to use the Upsizing Wizard to move my data to an SQL Server database with Access as the front end. This is a single-user, non-networked database, so is there anything I should know before I attempt this move?

 

[Joe4]

Re: Move to SQL Server question (was: Access lost my query)

Yes, Access has a 2 GB limit on its database sizes. Doing regular "Compact and Repairs" will help you keep the size down (especially if you do a lot of deleting of data).
However, if your data gets too large, storing it in something like SQL is the way to go. You can still use Access as the front-end, by linking the SQL tables to Access.

 

[baseball]

Re: Move to SQL Server question (was: Access lost my query)

Thanks. After I did a Compact and Repair, I was able to access the unresponsive query but I still need to switch to SQL Server as I have play-by-play data going back into the 1920s. Lots of data.

 

[Joe4]

Re: Move to SQL Server question (was: Access lost my query)

but I still need to switch to SQL Server as I have play-by-play data going back into the 1920s. Lots of data.

Sounds like it, but sounds like fun (I love Sports stats!).