Access

[Tom]

I am collecting data in Microsaft Access and have a lot of aces so far. My question for you database guys is how big can an Access DBAse get?
I suspect it can handle a lot of records, but how many?
Is there a dangerous size to no go beyond?
Thanks
Tom

[Que]

A MS Access database can get up to 2GB, but a table can only get up to 1GB. I don't think there's a limit on the number of records, just database size. I currently have over 1.7M records in my main db and it hasn't been a problem...yet. However, I have spent almost all day converting my trainer database to MSDE. I will recommend that you compact your MS Access database often, because lots of deletions and updates will bloat your db. Not to mention backing up your db on a regular basis, because sooner or later it will get corrupted. Finally, once your db gets over 800MB--then think about converting to MSDE or SQL server. (A MSDE db can get to 2GB in size, while a MS SQL Server db is limited by disk space.)

Regards, Que.

[Tom]

Thanks, Que

I guess I'm not even close to getting "big" yet.
I lke this DB stuff-I have already been finding out some things about trainers that I never would have uncovered
with my good ole pencil and legal pads!
Tom

[GR1@HTR]

Tom,

Don't think you will have to worry about filling up the dB. The last dB I had had over 80K races and took up maybe .5 Gigs. dB is a real fun thing isn't it.

[Larry Hamilton]

While the comments above are accurate, a more trying feature was not mentioned. If you create a data base that is wide (columns--number of fields) and deep (rows--records) you are impacting directly on the operation of any queries you write against the data, ESPECIALLY if you cross db's.

The solution is keep your data in as small a set of fields that answers your queries and is also wide enough to establish a relationship with all the other dbs. You must plan this at the inception of your data base because when your db has hundreds of thousands of lines, it is very difficult to manipulate.

If you plan to accumulate data on a few tracks, there is probably no need to get concerned. On the other hand, if like me you accumulate data daily from 10 or more tracks, within a year you will have problems with it's functionality.

So, what I am saying is keep your data bases in as small of piles as is practical and learn to merge and parse them.

[Aussieplayer]

Larry H.,

Are you saying that it's better to have a lot of tables with a couple fields in each, rather than have one table with lots of fields?
I'm looking at 4 tables at the moment with about 20 fields/columns.
This is because the db will not be relational at all.

On the other hand, I'll probably be entering a max of 100 reccords per week into it (as I'll have a separate db for different states - we race a bit differently).
So, looking at 5000 records * 20 fields = 100 000 "cells" at the end of the year.

Is that okay?

Regards
Aussieplayer

[Larry Hamilton]

5,000 records, 20 fields wide can be processed in less than a blink

Stick it all in one db, the size of which you speak doesnt require special handling.

I have one db with 1.6 million records in it. Damn thing cant be split up (its works of the horse) with MANY duplicates--I need to fix that one day. This db takes several minutes just to open the table. To open the query is an all day job. If the query contained two dbs linked and one of them is 1.6 million records, it could take a week to open.

==============================
To be relational, you have to find unique keys into each piece.

As a rule of thumb, if you are connecting pieces of a db these 4 fields will act as unique keys: Track, date, race number and horse name (i dont use post as a key because it can change from the pp to the results.

[Tim]

Tom,

I've used Access since Ver. 1 when I converted from an Advanced Revealation database. IMHO, Access is probably the best desktop database available. (I hope that statement doesn't start an argument)

The issue isn't how big an Access database can get, (using multiple mdb's with linked tables handles that question) but at what point does size impact on performance and stability. With large Access MDB's you have to be aware of the many 'gotcha's'. Anytime an Access MDB gets to be 500 meg or larger things begin to go wrong.

Such as, when doing a repair and compact on a MDB the available disk space on the drive that contains your temp directory has to be greater than the size of the MDB. That's because Access creates a copy of the mdb before compacting. Needless to say, that's a lesson I learned the hard way.

As a rule, you want to stay away from multi-part primary keys in an Access database. There is a moderate to severe performance penalty with large tables. You are better off using something like a numeric autonumber field as a primary key with an internal unique index on Date-Race-Track-Horse-Type.

I use "Type" to hold: 'D' = DRF, 'C' = chart, 'W' = workout, and 'S' = scratch. When Type = 'W' I set the race number to 99.( a horse can only work once in a day) That will insure a unique detail event record. PP lines, scratches and workouts - It's valuable for trainer and form cycle stuff.

Que said it --- "and it hasn't been a problem...yet". My Access system is populated with over 70,000 charts and all the wrap around data that goes with them and works just fine. Now if I could only pick winners..................

Tim

[Larry Hamilton]

Assuming I read your post correctly, and that is a long shot for me, you are going to take one input and put it in 4 places(tables) and none of the 4 are going to be related.

You are aware, of course, you can create a macro that calls up the input with a query, and directs part or all of the input in 4 directions (tables) or 4 spread sheets. And with a little bit of magic with Visual basic you can run macros in excel from access. Welcome to a game that is almost as much fun as handicapping--data bases.