MS Access as Handicapping Database
 

Hello,

I understand there're quite a few people here who uses database for handicapping software or data mining.

I am on Windows 10 and have MS Office 2013 suite so MS Access would be my preferred choice of database. Both OS and MS Office are 64 bits.

So far I have been using MS Excel for my purposes, although it's not a proper database, but then until now I had limited scope as well.

So before switching to MS Access I wanted to have some feedback from the experienced users and know how they feel about it.

Let's suppose you plan to have 2-3 million races (maybe it's impractical but wanted to set the highest limit possible) stored in your database spanning several years, my main concerns are:

1. Database size
2. Performance
3. Data integrity
4. Database maintenance
5. Database rebuilt, in case of data corruption
6. Response time -- especially when running SQL queries.

Based on your experience, is MS Access a good overall choice? I would appreciate the opinions both for and against.

Thanks in advance.

I'm a huge fan of access but my primary db did outgrow it. I think there's a 2GB limit on the size of the db so when I reached that point I migrated to SQL server on the back end. I still use Access as my front-end where many of my queries are stored though. My primary DB is now up to almost 12GB. SQL on the back end will maintain itself better, Access doesn't do much housekeeping on its own.

For research purposes I pull samples into Excel and use solver quite a bit. Access as a file utility to slice and dice csv files is actually way underrated. You can do virtually anything to files in a folder with import specs, macros, update queries etc.

As far as queries go a lot of that (speed) comes down to your tables and what indexing you've setup. Keep in mind this is all a steep learning curve unless this is your day job. I'm in IT by day, not a DBA by trade though I was offered a DBA position at my current employer but I decided to stay within servers and storage instead. I've messed around with all of this stuff for 30+ years, books, youtube etc, that's the only way I've been able to keep somewhat of a handle on it.

Bottom line I would say yes Access is a good choice and if you do outgrow it you will have options.

I've been using ACCESS since 2014. I store Formulator PPs, result charts, an enormous amount of proprietary data, and an almost endless list of queries I use to help me handicap and study the data.

Eventually I started bumping up to the limits of its size capacity, but admittedly my initial database design was not very efficient. I was learning the product at the time. I'd design it differently if I stared over. You may do better.

What I do to get around the issue is pull out some of the data every 2 years and back it up to another database. For example, right now I have data for 2022 an 2021 in my main database, a backup with 2019 and 20020, a backup with 2017 and 2018 etc...

If I want to do long term studies (and I do) I can selectively add what I need from the backups, study it, then take it back out.

I was a computer programmer. I like the product. It's a bit clumsy at doing some things. So sometimes I export data to Excel, manipulate it in Excel, and then import it back to the database.

Several people here helped me a lot to get started. I can't remember everyone, but if I can find the original thread I'll post it.

Here's the thread for when I got started. A lot of very sharp people helped me get going.

http://www.paceadvantage.com/forum/s...light=database

MJC922 and Class have the gist of it. You'll find the 2GB limit will become an issue sooner than you think. Access is more of an office tool than a database manager but using it as a front end for other DBs is pretty slick.

If this is not a commercial install you can download developer versions of MS SQL Server or Oracle to use for free. Lots of other choices out there too; MySql, Interbase, Postgresql etc.

Spending some time on tool selection and database design up front will pay dividends.

This, especially if the OP plans on doing serious research.

MySQL is free and quite powerful.


You will need to learn how to design and build a database if you want good performance when it gets huge. At smaller sizes, anything will be ok. You will need to learn how to perform SQL tasks like queries and merges, which is pretty easy. I think there is a for Dummies book on SQL. For the database knowledge I would recommend you go to https://www.mysql.com/ which has a developer zone with forums and all kinds of goodies to get your going.

OK....let me start off with that Access is stupid

You yourself is the driving force ..You tell it what to do.....!!!!!!

You knowing Solver and also regression analysis is immense...

Its your starting point to valuable and reliable data research...

Once you do know your direction , the queries will be super fast...

EX:

Say you have a 100 Factors ranked 1 thru 10 on each for every track surf dist...

Tell Access to find me top factors for AQU 6fur dirt claiming races...

Guess what ,Access looks at you and says WHAT???

Do this in Excel Solver and it will spit out something like First Fraction then Class then Pedigree then 2nd Fraction....down the line

Now you can use Access to plug in that info one at a time to develop the play

Input AQU 6fur dirt claiming then First Fraction ...it will give you the rois for ranks 1-10...say you see Rank 1 and 2 are significant , then you add that and then go to CLASS, there you se 1-4 will enhance the play...
and so on...but beware the more you add the less number of plays you get
But I'm sure you know that already....
Once you get the results that your satisfied with , create the query and your set for each race day...

Access is good but not the magic bullet people think...

So now you have to decide if taking the time to learn it is worth it ?

as You can do most of this in Excel as you've been doing...

Mike

Thank you all for the valuable feedback. I appreciate every response.

Besides the obvious concern about 2GB limitation of Access and everything associated with it my overall impression after reading the comments here and going through several online opinions and vides is that I'll be better off with MS SQL Server instead.

Since it would be for my personal use so I see no reason not to take advantage of full-fledged SQL database and rather spend time and efforts to acquire necessary knowledge and skills for SQL Server instead of worrying about problems and challenges I might run into in the future regarding Access.

So, I went ahead and installed SQL Server 2019 for developers along with SSMS and got the first feel of it this afternoon. I think it was beautiful. I was already familiar with basic database and tables tasks so it was almost a no-brainer. Through my little experience with Desktop BI, pandas, and VBA the basic SQL feature like JOIN, MERGE, GROUPBY, nested queries, stored procedures and etc. also looked awful familiar.

Since mine is all-new design and development I can better spend my time designing better database and tables.

As for the front end MS Access is still a possibility but my new development is in Python so I'll continue shopping for the front end. If anyone has any comments about that, I would appreciate it.

I thank everyone again who contributed.

I think Python uses odbc to communicate with MS SQL like most non MS languages. Nothing bad about that but it is an extra layer of software. Since I suspect that once your actual database is developed and the day to day updating and maintenance is figured out what you will mostly be doing is queries. So, creating a tool in Python to do the routine stuff and using some other tool or multiple tools for the queries may make sense. Access forms may be just the ticket but lots of other options are available.

Sorry to hijack this a little, but when I was doing a test on a computer generated Class rating using Access I used my intuition for the weights for a number of factors and then tweaked it using trial and error testing backwards and forwards over a couple of years.

When I tried to use Excel to do a regression and improve my weights further, it could never get as good a result as I did using intuition, let alone the tweaks I made later.

I can't explain that, but it always fell a little short.

it might seem that way but testing it shows the truth...

Your truth, MikeGerogeJoe. You have no idea how good a handicapper @class is so you can't know his "intuition". Coincidentally I was thinking about this last week. Intuition is not a good word because it has negative connotations much like the words "voodoo" or "witchcraft". Handicapping Insight is probably a better descriptor as there are people out there that can handicap pretty well with the Form or looking at output from some software program independent of how the horses are "ranked". Their brains process things in a way that they are not aware of but it works for them in a positive way. My late handicapping friend amazed me many times with his insight.

I'm not saying that research and statistical analysis is a bad thing; far from it. But if a person becomes wedded to it s/he loses the forest for the trees.

I did test it.

I used intuition (handicapping insights?) to weight each factor based on decades of handicapping experience, put those weights into the database, ran thousands of races against my database, and it calculated the win%, in the money%, average finish position, and ROI of the top choice.

I ran an Excel regression on the same factors, used the weighting provided by Excel regression, and tested it again.

My weights were slightly better.

Then I started manually tweaking the weights to see if I could improve it further. I improved on my weights a bit further and locked it in to test going forward.

that's impressive Class....

I wouldn't have never gone past what excel gave me

thxs for responding

mike