[Revised Jan 04]
This function should only be used if advised to do so by the Help Desk.
[Programmer notes at base]
Technicians notes;
There are four levels of recovery from database problems, so it is very unlikely that a user will lose data. The first level is to run a 'compact' of the database. If this fails then run the 'Repair database' utility. If this fails then normally the DB would be 'restored' off backup. Only if all these fail, a 'Rebuild database' function should be run. This is (almost) bulletproof and will recover even badly corrupted data.
Message 'Unrecognised Database format'
Normally this occurs only when the user has interrupted a compact. The file 'PhcyProA.MDB' is automatically created as a safety copy before 'compact' starts, and deleted when it finishes, so if it exists then the 'compact' was not completed for some reason. If this file exists, rename the file LOTS.MDB to LOTSX.MDB to remove it safely (you may want it back), and rename the LOTSA.MDB to LOTS.MDB. (If the computer will not let you do this reboot the machine and try again to free the locks on this file. If this does not work you will need to restore from backup.) This restores the DB to its to 'pre-compact' state.
Check all recent scripts are present before continuing to use the program. The user will need to do a compact again at some stage.
Compacting database.
First, try the 'compact' utility.
From the 'RxOne' menu select Tools, Utilities, Database, Compact Database.
- if this fails;
1. If the 'compact' just brings up "Compacting database" and disappears again without compacting, the corruption can either be in the database LOTS.MDB or in the safety copy LOTSA.MDB (note the 'A') that Compact makes before starting work.
2. If the file LOTSA.MDB exists (note the 'A'), rename it as LOTSB.MDB to remove it without destroying it (you may need it later). The existence of the LOTSA file is proof that the compact was not able to finish as it is automatically deleted at the end of compact.
3 Check that there is sufficient free space on the drive. there must be at least as much as the size of the Database free. Eg, if the DB is 160m, then you just have at least 160m free space before 'compacting'.
Try the 'compact' again.
After clearing the LOTSA file, try another compact.
- If this does not work, then
Run the 'Repair Database' utility.
From the 'RxOne' menu select Tools, Utilities, Database, Repair Database.
(see below) before trying anything more drastic.
If that fails,
'Restore' off the latest backup. Apart from the user backups, there will be zipped copies of the DB on the hard drive of all units on the system, both openly displayed and in the recycle bin. The system has 'roll forward recovery' and will normally be able to recover all data including that entered after the last backup was made.
Only if all else fails,
'Run the 'Rebuild database' utility.
The rebuild is a utility written by the original 'RxOne' programmers, that performs a very thorough (and hence time consuming so do overnight) pass through the Database attempting to copy each piece of readable data to make up a new and clean copy of the database to use in future.
From the 'RxOne' menu select Tools, Utilities, Database, Rebuild Database.
This utility can extract good data embedded in corrupted data.
Related topics
-----------------------------------------------------------------
Programmer notes.
[Revised March 2000]
Database Rebuild Utility
Filename = \PhcyPro\dbconv.exe
This Module can be used for Major database corruption problems when all else has failed. On most IT systems such data would be abandoned, but with pharmacy data personal medical records are at stake, and perhaps pharmacy payments so extreme efforts must be made. Restore off a previous backup if at all possible. If not, this utility can be used but should be treated very carefully. It has been able to rescue data on sites that had both bad corruption and no backups.
If a system is seriously corrupted and all else fails, I recommend you obtain a backup of the database to try re-building yourself as with a few tricks you may be able to get the rebuild to work.
The rebuild utility has been written and re-written many times as we encounter new issues which the rebuild requires modification to handle them. Thus there are now a number of command line parameters which enable you to solve various problems. See below for command line parameters.
The Rebuild Utility handles the database structure generically, thus will potentially handle any database structure given to it. This enables us to make changes to our database structure without affecting this utility.
Programmer Information
NOTE: I recommend you copy the database to a tmp directory for a personal backup, however if the program crashes out at any time you can quickly revert the program by re-starting the database Rebuild utility, and select Revert back to old database from the File menu. This works most times but it is possible to be left with a locked database which a re-boot may fix.
Firstly several queries are run on the database to clear out duplicate key information which can occur in a corrupted database.
{This section has been known to crash the program. If this occurs revert the database and re-run the program adding SKIP_SQL on the command line to skip over this section.
ie. c:\dbConv /SKIP_SQL}
Next: The database is now moved to c:\lotsback\lots.mdb providing us with a backup as well as the complete old database to work from.
A new database is created in \lotsdb\lots.mdb from Code containing no structure at all.
Opens the Old database \lotsback\lots.mdb
Loops through the Old database attempting to copy each table structure and data contents into the new database using the following SQL:
SELECT <OldTableName>.* INTO OldTableName IN c:\PhcyPro\lotsdb\lots.mdb FROM <OldTableName
If there is no corruption in this table it will complete the entire database in approx 5-10 Minutes.
If an error occurs with this statement a second phase is entered in order to solve this problem
{At this point the program has been known to crash fatal exception, thus you are unable to continue. To resolve this simply revert the database and restart the rebuild placing the <tabel name> at the time of the crash onto the command line.
Eg if the program states it was attempting to convert the Stock table displayed on the form while working, simply revert and restart rebuild c:\PhcyPro\dbconv.exe STOCK.
This process may need to be repeated several times ending up with multiple table names on the command line depending on how many tables are corrupted. The maximum I have encountered is 3.}
Next: the problem table is created in the new database along with its properties and indexes.
We now loop through the data records in the old database and try adding them into the new table in the new database. They are added in increments of 1000 until the error occurs again. When the problem records are encountered, the group of 1000 records are added one at a time. Some records may be lost depending on the corruption damage, however this method gets through the database fairly fast eliminating the corruption.
Normally this process will complete in about 30mins to 1 Hour. This depends on how much corruption and what tables are affected obviously the sales and script tables have a large record count thus will take the longest to complete.
{You can use the command line parameter /BYRECORD to work through the entire database record by record if the problem still cannot be overcome, however this will take many hours to complete maybe longer than over night.}
Finally, at completion, a list is given at the bottom of the screen containing any record count differences between the two tables Only the tables wth different recordcounts are listed. The recordcount shown here is NOT accurate however it is an indication of which tables have been affected. To get a true accurate count use access itself.
When finished, you will be prompted to do a compact. Please do this when prompted as it will clean up the database reducing its size significantly.
Be sure to check all recent information is present in the tables listed at completion particularly Script, Sales, Stock.
Make a backup of the new database so that the user does not restore back to the old corrupted database
COMMAND Line parameter Summary
/REVERT - This will revert the database back to the previous db before you used this utility
/NO_QUESTION - This will run this utility without asking the user any questions - used mostly for one off updates when necessary
/USE_UPDATE_DB - This will use the db in the '\PhcyPro\update' directory for the table, field and indexes that are required. The default is the old database which is placed in LOTSBACK by this utility
/BIGBRO - This will rebuild the BigBro database -All other functions will behave the same -just accesses the bigbro db in \bigbro5\db\bigbro.mdb
/SKIP_SQL - This will Skip the DBExecute statements at the biginning and go straight into the db rebuild"
<TableName> - List table Name(s) can be multiple, which will be built record by record avoiding an error which crashes the system the normal way