|
JCapper Message Board JCapper 101
|
| Home |
Register
Log In |
| By | JCapper2.mdb file - what you need to know | |
| jeff 8/25/2012 9:09:02 AM | --quote: ""Jeff,--end quote Hi Marc, First, thanks for the kind words. (Positive feedback is always appreciated.) Before diving into a how to set of instructions for setting up JCapper2.mdb files that span different time periods, I think it might be worthwhile if I provided some relevant background info. Data Window – PlayList File Mode vs. SQL Mode When Data Window queries are run in PlayList file mode the folder where the Data Window is pointed matters. When database queries are run in PlayList file mode the Data Window uses the pl_profile.txt file on the pointed to folder as the data source for its queries. When Data Window queries are run in SQL mode the folder where the Data Window is pointed doesn’t matter. When database queries are run in SQL mode the Data Window uses the StarterHistory table in the JCapper2.mdb file that it is currently connected to (c:\JCapper\Exe\JCapper2.mdb is the default.) as the data source for its queries. Folder Structure - Implications If you stop and consider folder structure and your daily file download process – and in turn consider the build database process – it is highly likely that your process will be like mine. In my process, I use one folder for each calendar year and four quarterly sub folders beneath that – one quarterly sub folder for each calendar quarter. My folder structure for calendar year 2012 looks like this: C:\2012 C:\2012\Q1_2012 C:\2012\Q2_2012 C:\2012\Q3_2012 C:\2012\Q4_2012 Today’s date (as I type this out) is August 24, 2012. When I downloaded files this morning I saved all downloaded files to my C:\2012\Q3_2012 folder. When I download chart files for today’s races I will save them to the same folder. When I run my next build database routine, I will run it on the same folder. I will continue to use that folder until the calendar rolls over into Q4 2012 the morning of Oct 01, 2012. The way things play out when I do this has my data files being saved to a folder that contains race cards that were run during a specific time period on the calendar. The pl_profile.txt file created by my build database routines on that folder end up containing data for the specific time period only. The pl_profile.txt file created by my build database routines on other folders end up containing data for a different specific time period only. Populating the StarterHistory Table in a JCapper2.mdb File There are two methods that can be used to populate the StarterHistory Table in a JCapper2.mdb file. There are: 1. SQL Mode Build Database routines. 2.Data Window Export Populating the StarterHistory Table Using SQL Mode Build Database routines When databases are built using Modes 3, 4, and 5, in addition to creating a pl_profile.txt file on the target folder, the Database Builder writes to (populates) the StarterHistory table in the c:\JCapper\Exe\JCapper2.mdb file. The basics for populating a StarterHistory table during a build database routine are as follows: • Use Mode 3 for your very first sql database build (to build any folder from scratch.) • Use Mode 4 for the very first sql database build on any subsequent folders. • Use Mode 5 for append builds on previously built folders after adding data and results files since the last build. IMPORTANT: During sql build database routines (in modes 3, 4, or 5) the Database Builder writes to (or populates) the StarterHistory table in the following file: c:\JCapper\Exe\JCapper2.mdb. IMPORTANT: Because individual folders contain data from specific time periods only, running a build database routine in modes (3, 4, or 5) on a folder causes the starterhistory table in the c:\JCapper\Exe\JCapper2.mdb file to become populated with data from the time period of the data and results files on that folder. JCapper2.mdb File Size Limit Microsoft imposes a file size limit on all .mdb files of 2.0 gigabytes. That’s large enough for approximately 24 months of all tracks everywhere HDW data for “most” users. However, my recommendation is NOT to try to pushing the envelope. For my own use I maintain a collection of JCapper2.mdb files that are populated with StarterHistory table data in the following manner: 1. One JCapper2.mdb file for each calendar year. 2. One JCapper2.mdb file that contains a rolling 14 or 15 calendar months of data. I find the above to be more than adequate for my needs. I also find that it is highly unlikely I will EVER bump up against the 2.0 gigabyte file size limit. Note: One thing to be aware of when an .mdb file exceeds 2.0 gigabytes in size is that the error messages thrown by the database driver do not relate to operations being run in JCapper. I have seen db drivers throw "Out of memory" and "System Resources Exceeded" and "Out of Disk Space" messages when attempting to read from or write to an .mdb file that has exceeded the 2.0 gigabyte file size limit. If you get these or similar error messages while attempting to work with an .mdb file, the first thing you should check is the size of the .mdb file. (If the file is bigger than 2 gigabytes then you know what the problem is. Hint: Chances are you aren’t out of disk space.) Populating the StarterHistory Table Using Data Window Export Alternately, the StarterHistory table in the JCapper2.mdb file that the Data Window is connected to can be populated using a Data Window Export routine. There are two options: 1. StarterHistory Clear First - Use the Clear First option the very first time you are populating the StarterHistory table or whenever you want to clear all existing data from the table and repopulate it with data from a specific pl_profile.txt file. 2. StarterHistory Append - Use the Append option whenever you want to add data from a specific pl_profile.txt file (on a specific folder) to the StarterHistory table. The basics for populating a StarterHistory table using a Data Window Export routine are as follows: 1. Put the Data Window in PlayList file (or text) mode. 2. To launch the Export, click MENU. Select Exports, and select either StarterHistory Clear First or StarterHistory Append. 3. Answer Y at the prompt to toggle the export ON. 4. With the export toggled ON and the Data Window in PlayList file mode and pointed to the desired folder, run an ALL button query. As part of executing the query, the Data Window will write the query results to the StarterHistory table. Compact and Repair Database Routines Note: It is always a good idea to run a Compact and Repair Database routine on the JCapper2.mdb file after adding a significant number of table records to the file. It is also a good idea to run a Compact and Repair Database routine on the JCapper2.mdb file after deleting a significant number of table records from the file. To Compact and Repair Database routine on the JCapper2.mdb file: 1. In the Data Window click MENU. 2. Select Compact and Repair JCapper2.mdb File. 3. Answer Y at the prompt to continue. 4. Use the dialog box that pops open to select the sedired JCapper2.mdb file. The Active JCapper2.mdb File The full path to the “active” JCapper2.mdb file is: C:\JCapper\Exe\JCapper2.mdb Fresh Blank Copy of Current Version JCapper2.mdb File Each time you do a JCapper Install, the Extractor will place a fresh blank current version copy of the JCapper2.mdb file in your c:\JCapperBuild folder. Now, with much of the relevant background info out of the way, let’s revisit the question and get it answered. Q. I see in your main video on the home page of your website and in a couple other training videos that you have .mdb files for several different time frames. I was just wondering how you did this? A. It’s easy. Any time that you want to, simply rename the JCapper2.mdb file in the C:\JCapper\Exe folder. Name it anything you want. That said, best practice (for me) involves renaming it in such a way that the file name itself describes the data inside of the file. For example, at year end 2011 I renamed my JCapper2.mdb file in the following manner: From: C:\JCapper\Exe\JCapper2.mdb To: C:\JCapper\Exe\JCapper2_YE2011.mdb Note that the file name tells me at a glance what the file contains: StarterHistory table data for calendar year 2011. Also note that the Data Window enables you to connect to different JCapper2.mdb files. Click MENU and click Connect To JCapper2.mdb File. The dialog box that pops up is programmed to find .mdb files where the first 8 characters of the file name are "JCapper2" followed by the ".mdb" file extension. The characters in between can be anything you want them to be. The next thing I did, prior to populating the StarterHistory table with data for the (new) 2012 calendar year was copy the fresh blank current version copy of the JCapper2.mdb file in the c:\JCapperBuild folder to the c:\JCapper\Exe folder. Immediately after that I used the JCapper2 Imports Screen to import data for my sql factor setup and sql report layout from my old file into my new file. At that point I was ready to begin populating the StarterHistory table wit data for the new year. Using the above procedure, I can easily create a "library" of JCapper2.mdb files - where each .mdb file contains data from any time period that I want it to. Alternately, I can also do the same thing using Data Window export to populate the StarterHistory tables in my collection of JCapper2.mdb files. -jp .
| |
| jeff 5/27/2014 4:08:03 PM | Recovery Process - Bad JCapper2.mdb file The most likely symptom(s)/indicator(s) that you have a bad JCapper2.mdb file are: various error messages as you attempt to perform basic program operation. The most likely cause of a bad JCapper2.mdb file is: exceeding the 2.0 gigabyte file size limit (see above.) I've seen a lot of strange things happen after a JCapper2.mdb file breaks the 2.0 gigabyte file size limit. Among them are: • Random error messages that do not relate at all to the specific routine(s) the user is attempting to run at the time the error message is rendered. • Missing tables. I've seen a handful of instances where the user sent me the file itself using a service such as WeTransfer. After receipt of the file and upon inspection - I discovered one or more tables had been dropped from the file... rendering it useless of course. I've not researched the intricacies going on inside the db driver leading to lost tables once a bad .mdb file is encountered or once the 2.0 gigabyte .md file size limit is broken. The important thing to know is that the file itself is bad - rendered useless - and that means it's time to recover from that event. Basic recovery strategies: Option #1. - RECOVER USING LAST KNOWN GOOD BACKUP The easiest best recovery path is the simplest: Simply replace your current (bad) JCapper2.mdb file with your last known (good) backup copy of the file. From there it becomes a matter of determining the following and acting accordingly: The date of the latest records found in the tables you are using (starterhistory, wagerhistory, tripnotes, etc.) Example: Your last known good backup is current through Dec 31, 2013 and you are using a quarterly folder structure. The file became bad on Jan 06, 2014. Replacing your bad file with the backup leaves you current as of Dec 31, 2013. Provided you are not about to break the 2.0 gigabyte barrier again, the final steps for your recovery path involve: a. Running a mode 4 db build on your initial Q1 2014 folder. (Alternately you could run a mode 3 build if your intent was to start over and have your starterhistory table contain 2014 data only.) b. Re-keying your tripnotes and wagerhistory entries for the first few days of 2014. Example: Your last known good backup is current through Dec 20, 2013 and you are using a quarterly folder structure. The file became bad on Jan 06, 2014. Replacing your bad file with the backup leaves you current as of Dec 31, 2013. Provided you are not about to break the 2.0 gigabyte barrier again, the final steps for your recovery path involve: a. Using the Data Window to a delete starterhistory table data where [date] >= #10-01-2013 and [date] <= 12-31-2013. example sql expression to delete records for the above specified time span: DELETE * FROM STARTERHISTORY WHERE [DATE] >= #10-01-2013# AND [DATE] <= #12-31-2013# Bullet Points for deleting records from the table using the Data Window: • Note that I have enclosed the date field name inside of square brackets like this: [DATE] Important! • Note that I have enclosed the date text inside of pound signs like this: #10-01-2013# Important! • Note that I am using the DELETE * FROM command instead of the SELECT * FROM command that I would normally use when executing a sql expression. • Note that when you hit the Execute button the Data Window will give you a warning message telling you that you are about to delete records from the table. To delete records from the table, answer Y at the "Are you sure?" prompt and continue on. b. Running a compact and repair on your c:\JCapper\Exe\JCapper2.mdb file. c. Running a mode 4 db build on your initial Q1 2014 folder. (Alternately you could run a mode 3 build if your intent was to start over and have your starterhistory table contain 2014 data only.) d. Re-keying your tripnotes and wagerhistory entries from Dec 21, 2013 onward. At this point you would be completely recovered. Note: I've posted at length about the importance of making regular backups of both the c:\2004\JCapper.mdb and c:\JCapper\Exe\JCapper.mdb files. You do have recent backups of both, right? (And you do realize the more current you backup the less data entry involved in the recovery process itself, right?) More to come... -jp . ~Edited by: jeff on: 5/27/2014 at: 4:08:03 PM~
| |
| jeff 1/8/2014 11:15:48 AM | Option #2 - Recover using Fresh Blank Copy of the JCapper2.mdb file sitting the c:\JCapperBuild folder. Background info: Every time you do a JCapper Install, the Extractor is programmed to place a fresh blank copy of the JCapper2.mdb file (current and compatible with the program version you are installing) in your c:\JCapperBuild folder. The purpose for doing this is to provide you with a recovery path in the event of disaster. Note: The recovery steps below involve using the JCapper2 Imports Module to import table data from a last known good backup. If for some reason you don't have a good backup: • If you had a custom sql factor setup and/or custom sql html report layout you are going to have to re-create it manually. (Otherwise your factor setup and report layout will be the program defaults.) • If you were using the TripNotes Module to record your own tripnotes - you are going to have to re-create those entries manually. (Otherwise that info is lost.) • If you were using the WagerHistory Module to record your own WagerHistory - you are going to have to re-create those entries manually. (Otherwise that info is lost.) • Like most tables found in the replacement JCapper2.mdb file taken from the c:\JCapperBuild folder, the starterhistory table is empty. Not having a (source) backup JCapper2.mdb file to import data out of means that your next best method for repopulating the starterhistory table is through database builds and/or using the Data Window MENU to run StarterHistory Table Exports. If for some reason your backup isn't recent: Then you are going to have to manually key entries to the above (and other) tables from the point in time after the backup you are importing from. RECOVERY INSTRUCTIONS: 1. Navigate to your c:\JCapper\Exe folder and rename the JCapper2.mdb there to something else to get the file out of the way. Hint: Renaming the file to JCapper2_BadFile.mdb gets the job accomplished. 2. Navigate to your c:\JCapperBuild folder. On that folder is a fresh blank current version JCapper2.mdb file put there during your most recent JCapper program install. Copy this file (filename: JCapper2.mdb) to your c:\JCapper\Exe folder. 3. Launch the JCapper2 Import Screen. Hint: The launch button on the Utilities Screen of the WagerHistory Module will serve to get the job done. Note: Steps 4-10 below are all performed while you are in the JCapper2 Import Scree. 4. Click the Set Source Database button. Use the dialog box to select a source JCapper2.mdb file. Hint: Selecting the last known good backup JCapper2.mdb file gets the job done. 5. Click the Set Target Database button. Use the dialog box to select a target JCapper2.mdb file. Hint: Select the JCapper2.mdb file on the c:\JCapper\Exe folder from step 2 above to get the job done. 6. Look at the checkboxes on the JCapper2 Imports Screen. Check the box for each table that you are knowingly using. If you are not sure whether or not you are actively using a given table – chances are you aren't using it and can leave the box unchecked. The following list will probably get the job done for 98% of you: • StarterHistory Table. 7. Double check the folder and file names of your Source Database File and Target Database File. 8. Double check the boxes checked for the tables you want to import out of your source database file into your target database file. 9. Click the GO button – and click ok through the X number of records imported messages as the interface performs the import for each table. 10. After the Import Routines have been completed for all of the tables you checked, x-out of the JCapper2 Import Screen (and WagerHistory Module.) At this point, you now have a working current version JCapper2.mdb file sitting on your c:\JCapper\Exe folder populated with data as it existed at the time of your last known good backup. At this point you should be able to launch the JCapper Main Module as well as the other JCapper modules without getting an error. At this point, you should be able to load data files into the program - get scratches by parsing the XML - run a sql Calc Races - and generate race day reports that reflect your UDMs and custom sql report layout. -jp .
| |
| jeff 5/9/2014 10:18:52 AM | Thread bumped per email request. -jp .
| |
| jeff 7/22/2024 2:25:57 PM | Thread bumped per user request. Relevant Background Info It's IMPORTANT that you avoid getting a corrupted c:\JCapper\Exe\JCapper2.mdb file as a result of breaking the 2.0 gigabyte file size barrier. If you are operating in SQL Mode and you are using Yearly Folders, Imo, this is your best strategy for doing that: 1. Make sure you read and understand the info in the above posts. 2. You need to know how to:
From there, your yearly folder DBBuild strategy involves: 1. Getting a JCapper2.mdb file (with your custom SQL Factor Setup and SQL HTML Report Layout in it) onto your c:\JCapper\Exe folder. 2. Running a DBBiuld in Mode 3 on a Yearly Folder. 3. Renaming your current c:\JCapper\Exe\JCapper2.mdb file to both archive it and get it out of the way. Assume for the sake of argument you just finished building the 2017 yearly folder. (Renaming the file from c:\JCapper\Exe\JCapper2.mdb to c:\JCapper\Exe\JCapper2_2017.mdb works.) Repeat steps 1, 2, and 3 above until you've run DBBuilds for all yearly folders. Going forward from there with fresh data for the current year: Run your first DBBuild on your current yearly folder in Mode 3. Then run subsequent DBBuilds on your current yearly folder in Mode 5 as data and results files for new race cards are added. -jp .
|
| Reply |
|
Copyright © 2018 JCapper Software back to the JCapper Message Board www.JCapper.com |