2 GIG

[hurrikane]

I'm in the process of moving everything to MySql and creating a relational db instead of the big flat table used now.
I'm wondering if anyone here has made a similar move and have you run into any problems.

the 2 GIG. Oh yeah. Part of what prompted this is my db size keeps creepinig up to the 2 gig limit. This creates a ton of problems.

That's the one part. The other part is my son making fun of me for having one big flat table. That would be the biggest reason really. :mad:

[Donnie]

Kane---
Have you tried using the table analyzer utility in Access? Back up your current db (as if you HAVEN'T been doing that already!) Tools to Analyze to Table. Personally hae never dabbled in MySql...suppose to be pretty simple! Would be interested to hear how it goes!

[Rick]

All I can say is good luck.

I just took a look at the HX4 export. Trying to break it down into what I called RACE/HORSE/RESULTS I found that I could only find 22 of the 142 fields that I would/could separate out into a separate table. These were items that were repeated for each horse in the race.

As bad as our flat approach is, or appears to be, it's simple and works.

You may need to find something that doesn't have the 2 GB limit but it doesn't look like it will save that much space trying to make the info RELATIONAL.

I may run a small test to see how much is gained by doing it this way in Access, but it doesn't look like it will be all that much.

I am sure I could make export macros and Import Specification files to do the job, but the gain doesn't appear to be worth the effort.

Sometimes KISS is better. :)

[hurrikane]

Actually Rick, i have started braking things down and it seems to be helping a lot.

the first thing I did was break out the trk to a track table to stop the query on text.
the biggest change was gettin rid of the 4-pk per race and built a race table with specific race info that is repeated for every race for every horse..
Race, date, track-id and rest of the info for a race...d/s, surf, purse, etc, etc. Everthing specific to a race. then made a pk to the race file and the all-hx4 revised. one number for the race pk to the race file.
i call this the new 'horse in race file'(not actually spelled out that way'
This eliminated all the dupe date.
reduced my db by about half not repeating all of this data for every race. plus queries are much much faster.

many other early steps though so not sure of the future..but i got my son off my back.


mysql is the big thing. fortunately it seems to act a lot like oracle which I am pretty familiar with...

[C_Martin]

I want all the data I can have to look at so I had in one table HX4, HX5, and HX7 tables from Oct of 2000. I just about went nuts trying to figure out what to do because I knew I had reached the 2 gig limit. Of course I also had queries that were making tables from those tables and so on.
My fix was to make 3 different mdb. one for each HX* file. Then I made a new mdb and linked the the tables for all the HX* and copied the queries from my old mdb and now I am back in good shape and should have about 2 more yrs before I reach the limit again.
and my main mdb is less tha 200mb.
Hope this is some help as you have really helped me in the past just by reading your post on this forum.

[hurrikane]

that's a great idea C- i've been doing things that way for a while. The main reason i started was to avoid corruption. i was running things one day and my db corrupted. had to rebuild. By keeping your tables in a seperate mdb if your query/form db corrupts the data integrity is saved since you don't really work in the same mdb. it's a great idea and if everyone isnt' working that way now they should change over immediaty

you have to make an appointment( i use a reminder in out to go into the tables mdb and do a compact and repair function to keep things efficient but it is difinately the way to go.
thanks for help C-

[Donnie]

In Access it's called the database splitter----Tools to Database Utilities to Database Splitter.

Automatically moves all your data into one db, all of you queries and reports and forms into another, and then links your tables (data) into the db where all the "action" occurs. As in C's routine, he uses db's for each "brand" of table.

As ALWAYS, be sure to back up any of you db's before trying this!!