You may be able to significantly reduce the file sizes for your database depending on how your application uses your databases. This process is called "compressing" the database. But you may not see any change in the file sizes at all. Consider these two situations: 1) If you do not delete records from your database, there will not be any change in the .dbf or .dbt file size after compressing it. You will see a reduction in the size of the SmartIndex .adx file because all indexes not needed to support the current .APR file's joins will be eliminated. Approach will rebuild them when needed. 2) If you do delete records from your database, the records are only marked as deleted when you do that. They are not actually removed from the .dbf file. Therefore the .dbf is still the same size after you delete records. And any memo or PicturePlus fields are still in the .dbt, but the pointers to them are removed from the deleted record in the .dbf. To actually remove deleted records from the database you need to "compress" the database. This removes the records in the .dbf, the memo and PicturePlus fields in the .dbt, and unnecessary indexes in the .adx file. In v96 and newer releases, you do this in Approach by going to the File menu, User Setup option, Approach Preferences option, and then select the Database tab. Then choose the database file name that you want to compress and click on the "Compress" button. This flags that you want it compressed, but Approach does not actually compress it until you press "OK". If you want to compress several databases files, you need to select and click the "Compress" button for each one and then click "OK". You need to be the only user of a database when you do this. See article 'Compressing databases with a macro or script' in this FAQ to automate this procedure. Once a database is compressed, deleted records cannot be recovered (see article 'Recovering deleted records' in this FAQ). For this reason, it is highly recommended that you create a backup copy of your files before doing this. Are there other reasons for compressing a database? Yes! A compressed database is more efficient in its indexing which speeds up your Finds. And if you don't ever compress a database then it is more likely to develop index errors or corruption. Some report they never compress their databases and they run fine that way for years, however!
Why doesn't the .dbf file size decrease when I delete records?
This is because when you delete a record it is not actually erased from the .dbf file, it is just marked as being deleted and further access to that record is denied. To actually erase a record from the .dbf you need to delete it and then compress the .dbf. (Also see articles 'Reducing ("Compressing") the size of a database file', 'Compressing databases with a macro or script' and 'Recovering deleted records' in this FAQ)
MGood practices to reduce that chances of encountering problems
Last updated: 23 February 2007 by XpertSS.com I. BACK UP YOUR FILES
Make backups of all of your application's files (.APR, .DBF, .ADX and .DBT and other types of database files you may use) regularly. How often you do this depends on how much work you want to do in reentering data that is not backed up. For some applications, once a week may be sufficient. For other applications, daily may be OK. For "critical" applications, you may want to consider a "mirror" backup method that some server software offers where files are continually backed up as changes are made. Make sure your backup is "good". No one should be using your application during backup operations because the files that are "in use" may not be backed up, depending on your backup method or software. Check the backup to see if it will restore OK by restoring it to another computer or location and opening the APR files. Make sure you have multiple "generations" of backups in case your most recent backup contains a problem you cannot fix. For example, for a daily backup situation, you could have backups for each day of the week and reuse them over the period of a week. At least one backup should be stored off-site just in case your office suffers a disaster and you need to continue operations elsewhere. Online storage facilities are also available that offer security and ease of access to backups. Specific backup and online storage solutions will not be offered in this FAQ. II. CLEAN UP YOUR HARD DRIVES Most Windows applications, like Approach, create temporary work files in your designated temporary file folder. These files are usually of type .TMP or .~MP with a random name. When an application is closed, it should delete all of its temporary files, but this does not happen when the application does not close normally, when Windows itself crashes, or other interruptions occur. The problem is that a folder on your hard drive cannot contain an unlimited number of files. If this limit is reached, things do not work very well. Another problem specific to Approach is that those temporary files may be misinterpreted as current data files, causing various operational errors. So you should regularly go into your systems (all user computers will have these) with all applications closed, find those files and delete them all. Note that holding down the Shift key when you press the Delete key bypasses the recycle bin! o In older versions of Windows, these temporary files were usually in C:\TEMP or C:\WINDOWS\TEMP. o In newer versions of Windows, these temporary files are stored in C:\Documents and Settings\username\Local Settings\Temp Regular use of Scandisk and Defrag utilities will also make sure your hard drives are in good condition. There are software packages that will clean up temporary files and perhaps even keep your "registry" clean, but those will not be offered in this FAQ.
III. SAVE YOUR WORK OFTEN WHEN DOING DESIGN WORK When switching between design and browse frequently, get into the habit of periodically saving your work, then closing and reopening your application. This is especially important when modifying field definitions or joins, and when working with macros and scripts. Doing this will ensure that if something does go wrong you will lose the minimal amount of your work, and it also give Approach a chance to release system resources and refresh its links, indexes and graphics and start afresh. Develop a practice of saving your APR file(s) such that you have at least 2-3 copies of the file saved with different names. I use something like Ordersdev001.apr, Orderdev002.apr, etc. for an APR that in production is named simply Orders.apr. During each restart of development per the above paragraph, copy the just closed APR file into another folder and rename the one you are working on. Keeping a log book of changes you are making is also a good practice, and you can note the APR names at the points in which you create them. If you are a bit forgetful about periodically saving your work, then you may want to consider adding the following line to the current script you are working on so that your changes will be automatically saved each time you run the script (don't forget to remove it once you finished developing that script): Current.Window.SaveChanges The only caution about the above is that sometimes Approach appears to delete all of its scripts during testing them. And if you script does not run to completion, this statement will not be run. Therefore it is recommended that you not do this and instead merely use the script editor's File, Save Scripts after each change in them. This has the added advantage of recompiling your scripts and telling you if there are errors in them before you try another run.
IV. MINIMIZE PROBLEMS IF YOUR INDEXES ARE CORRUPTED NEED TO BE DELETED When naming fields, long field names are easy to read, but if you ever need to delete your index files (.ADX) you will have a lot of work to do to update all of those names. Deleting the index reverts your field names to the dBase standard which is: o Maximum of 10 characters
o Underscore, alphabetic and numeric characters only (no spaces)
o Must start with an alphabetic character Therefore if you ever need to re-map the fields names after deleting your .ADX file, they will all match except for "time" type fields. Approach modifies the stored field name for this type so it can distinguish them from "text" fields because there is no "time" field type in dBase. For example, you have a time field named ORDERTIME which will be modified to ORDERTITM9 where the "TM9" tells Approach it is a "time" field. This is also a good reason to not use a field name that ends with "TM9". Instead of deleting index files, it is recommended that you keep a set of those .ADX files that are current to the field definitions in use. Then if your .ADX is corrupted, you merely replace the bad one with your copy and update any serial numbered fields next number value. This will preserve your long field names too!
V. BATTERY BACKUP FOR YOUR USER COMPUTERS AND/OR SERVER It is a good idea to have battery backup devices on the power for all of your computers (and monitors) that use Approach as well as your server, if any. This is good for your computer because it is protected from power surges and you will have some time to shut down the computers normally. The main problem with power failures is that any write operation in progress to the hard drive may end badly -- writing out only part of a file or writing over other files! VI. MISCELLANEOUS Never delete a macro or global script. Just rename ones you no longer need to something like zobs1, zobs2, etc and reuse them for new macros or scripts. Deleting them in most releases of Approach will modify the macro/script used in MESSAGE and RUN commands! Don't type field or database names into formulas or text blocks. It is far more reliable to select them from the field name lists provided in field properties or in the Text Object, Insert, Field Value dialog. Apart from the possibility of making spelling mistakes, typing in field names can cause Approach odd behavior. For example, in a text block, a field may suddenly switch to a time format! (which may cause you to behave oddly...!) If Approach starts "acting strange" when in Design mode, you may be experiencing a resource shortage. You should then exit all other Windows applications to free up memory and resources to stabilize things while you save your work. You can use the Windows Explorer to copy the APR file you are working on to another folder before you save the current version, just in case the current version is damaged in some way. Then restart the computer before continuing your development work.
DON'T delete macros, named find/sorts or scripts! Deleting things is a primary cause of .apr file corruptions. Instead, just rename them to "unused1", "unused2" and recycle them when you need a new one. Sue Sloan writes: In A96 and later, three corruptions occur in the apr when you delete a macro. If you aren't doing these things then you won't notice adverse effects: 1. All Run commands in macros that run a LotusScript sub are corrupted. The first one runs a blank and all the others run a different sub. Each time a macro is deleted they are shuffled round again. 2. All Message commands that specify two buttons, where the buttons run macros other that **STOP** and **CONTINUE**, are corrupted. They all run
different macros after a macro is deleted. 3. The first time a macro is deleted in an apr all existing and future custom menus are affected. Any macro created subsequent to the first deletion won't run from a custom menu. This is irreversible. This was partially fixed by Lotus in the newest releases of Approach (v9.5), but not 100%, so we are still recommending that you do not delete macros or scripts.
Backing-up your data using a macro
You have two options for doing this. One is a create a macro that exports all of the data that you want to backup to wherever you want it backed up to. However, this won't backup your .apr's. To backup everything including your .apr's create a DOS batch file like the one below (a batch file is a plain text document which has been saved with a .bat file extension instead of a .txt, and which contains DOS commands): cd c:\backup
copy c:\mydata\*.dbf c:\backup
copy c:\mydata\*.adx c:\backup
copy c:\mydata\*.dbt c:\backup ... you will of course have to customise this to the hard drive and file paths of your data and backup location. The run this batch file using the OPEN command in a macro.
.apx files are used by Approach when working with Paradox database files. They contain Auto Increment Numbers and OEM or ANSI settings.
Repairing corrupt index (.adx) files
(This widely affirmed procedure was submitted to the Approach Users Mailing list by Jerry Sikes, Unisource Converting, and has been update from later suggestions that he has made.) 1: Get exclusive control of database files. This means that no-one other than yourself has the database open in any application.
2: Create a directory named "Safe", or "Backup" or whatever is meaningful to you.
3: Open each critical database, one at a time, as a new application with Approach. A default form and worksheet will be created.
4: Run a find that gives you only 1 record in the found set. (Use Hide if necessary) This will create a minimal index and 1-record database when you do the next step.
5: Export the "found set" to the safe directory, using the same database name. (Hint when Approach exports to this new directory, it creates a new one record index)
6: Close the new application without saving. (Optional...I do not normally save these since Approach can recreate this at will)
7: Repeat for all targeted tables. The new safe copy indexes store the Long Field Name format that Approach uses, and the starting serial number for fields where that option is used. If you need to "uncorrupt" an index:
1: Get exclusive control of that database's files again.
2: Copy the safe adx file back to the working directory.
3: Reopen your application that uses the database and Approach should immediately launch into a "Smart Index Creation" mode. If not, do a "compress" of the database to force the reindexing.
4: If you are using an old safe copy of the .adx file for a database with auto serial numbers, then you WILL need to manually reset the next serial numbers to the correct values. It is important that you maintain a good revision control method. If you add fields to your working table, update the "safe copy" by exporting the 1 record again. Using an out-of-date index with an updated database structure could damage your data! NOTE: Some people recommend that you compress your databases often as a corruption prevention method. XpertSS.com has not found this to be necessary or advisable unless you delete many records from your database and need to reclaim the space they are using in the files. It is more important to discover and fix the problems that caused the index corruption in the first place. Some things that are helpful are: * Having backup UPS battery devices on your server and all workstations to prevent a power failure from interrupting data transfer over the network or writing incorrectly to your hard drive. * Making sure that your validation rules prevent the "one" side of a join from accidentally becoming a "many" by validating join fields on the "one" side as both filled in and unique. If you use more than one field in a join, add a real field to that database with a modification formula that combines the join fields into one string and validates it as unique. * Be very careful when importing records into databases. The Import Data process ignores your field validation rules and will let you import duplicate values into unique-validated fields. It also invalidates the database index which forces a reindex cycle that will impact user access to that database until the import has finished (locking messages, SmartIndexing messages, etc).
TIP: No amount of index backups will help if your database (.dbf and .dbt) becomes corrupted. Making regular backups of the entire set of files on a regular basis and storing them either in a fire-proof safe or off-site is highly recommended. And please, test your backup files to make sure they are OK and will restore when needed! Current technology makes backing up to CD/RW very simple and inexpensive, without resorting to compacted files on unreliable media like tape drives.
Deleting and recreating index files (.adx)
In order to maintain the best ratio of speed and stability, you should consider either deleting the .adx files (indexes) on a regular basis (say every week or two) or compressing the databases. Alternatively, you can use the .adx repair procedure (see 'Repairing corrupt index (.adx) files') which preserves some of the information in the .adx, but may be a more involved depending on the design of your database.
Why is database/index maintenance recommended? 1) The SmartIndex files for dBase databases in Approach are built up over time to support your joins and finds. This can result in bloated index files -- sometimes they can be larger than the database (.dbf) file they are supporting!
- When an .apr file is opened, Approach will make sure the indexes required for the joined fields in that application are current.
- Subsequent "finds" add indexes as needed to support them, and they are not deleted even if the find is never to be done again. 2) You suspect that an .adx file is corrupted. For example, a find gives you invalid results or a repeating panel that should show records does not show them. Deleting the .adx file will get rid of the fault, but perhaps only temporarily if you have an illegal many-to-many join that is causing the problem. 3) Deleting records from your database does not delete them from the .dbf and .dbt files. The space is merely marked as "deleted" and excluded from subsequent display. The only way to reclaim the space is to compress the database. 4) Unnecessarily large database files take more time to transfer over a network, and more time to execute finds and sorts. 5) If another application (say Paradox, or Access, or something) adds or updates records in your database, the Approach index will not be correct. NOTE: It is always a very good idea to do a full back up before doing any maintenance on your databases or indexes!
How do I compress a database? Compressing your databases is preferable to deleting the index files because it preserves your long field names and serial numbered field settings. To compress a database, you use the File menu, User Setup, Approach Preferences sequence to display the Approach Preferences dialog. On the Database tab, select a database name, click the Compress button, and repeat for each database you want to compress. Finish with the OK button, which actually starts the compression process. (Also see articles 'Reducing ("Compressing") the size of a database file' and 'Compressing databases with a macro or script' in this FAQ)
How do I delete an index (.adx file)? Before you delete the index files, there are some possible complications from doing this that you should be aware of:
1) If your Approach database field names do not adhere to the dBaseIV standards, you will find your field names changed to fit that standard.
- The default dBase IV format is a fieldname written in all capital letters, with a maximum fieldname length of 10 characters as a combination of 10 letters, digits, and underscores.
- The first character must be a letter.
- Punctuation marks, blank spaces, and other special characters are not permitted.
- Field names that are similar, such as BILLAMOUNTDUE and BILLAMOUNTPAID will become BILLAMOUN1 and BILLAMOUN2, which can make mapping these names to the originals in your .apr file difficult. 2) The .adx contains what the next number will be when automatically incrementing a serial numbered field. So, if you delete an .adx file for a .dbf that has a numeric field containing an auto - incrementing serial number, then the serial number will be reset to 1. You will therefore need to manually reassign the correct next record number in the field definition options. To delete an .adx file, you should know how to navigate your hard drive or network drive using File Manager or the Windows Explorer. Go to the folder or directory where your database files are stored and you will see sets of files for each database. The set of files will all have the same name with a different type. For dBase files there is always a type .dbf where your records are kept, optionally a type .dbt where memo and PicturePlus fields are kept, and the .adx file. You can simply select and delete the .adx file for the database you are performing maintenance on. Approach will automatically rebuild the indexes when you reopen the application that uses it. Conclusion: If you only use dBase IV format fieldnames and if you do not use any auto-incrementing serial numbers and if you do not manipulate the .dbf file with another application, then you should not suffer any side effects when deleting a .adx.