 |
 |
 | |
 | |
 | |
 |
|
|
| |
| |
| |
|
Last updated: 27 Mar 2004 by XpertSS.com
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.