Approach Users Mailing List FAQ (text version) Last updated: 3 Mar 2007 This FAQ contains a summary of answers to Frequently Asked Questions on the Approach Users Mailing List, as well as other material that may be helpful to a wide range of Approach users. This text (.txt) version of the FAQ is available for download at: http://www.netspace.net.au/~jabrown/approach/textfaq.htm The Rich Text Format (.rtf) version of the FAQ is available for download at: http://www.netspace.net.au/~jabrown/approach/textfaq.htm The web version of the FAQ is at: http://www.netspace.net.au/~jabrown/approach/faq.htm Disclaimer: Approach User Support FAQ (text version) and associated documents (Web site, Web FAQ, newsletters and mailing list documents) are provided as is without any expressed or implied warranties. The editor and contributors assume no responsibility for errors or omissions, or for damages resulting from the use of the information contained herein. ** ** Special thanks to www.XpertSS.Com for their many contributions to this FAQ ** ** [From this point on line wrapping is in the hands of your document viewing application] Contents: Notices The Approach User Support web site How to get the latest edition of the FAQ How to receive a newsletter containing FAQ updates How to get the Archive of LotusScripts Contributions, comments, corrections, or feedback German language FAQ and newsgroup Lotus support now at http://www.ibm.com/support/ Our collective wisdom so far... The basics ("Stuff you really should know before you read other stuff") - Getting started with joined databases! - Help me design my database! - Designing and problem solving techniques - Looping macros - Record numbering, including auto-incrementing serial numbers - Using Boolean fields - Reinstalling Approach General information - What is Approach? (Is it truly relational?) - How does Approach compare to other DB's? - IBM's Lotus Approach Support web site - Books - Example and commercial Approach applications - Other information sources about Approach - Other information sources about LotusScript - Can I Develop Stand-alone databases (Run-time) using Approach? - Can Approach handle big databases? - Moving or copying databases (.apr/.vew .dbf .dbt files) - Help for MS-Access uses moving to Approach - Getting started with LotusScript Approach/.dbf specifications and overcoming limitations - The latest version and upcoming developments - Versions of Approach for MS-Windows / NT [UPDATED] 14 Dec 2006 - Versions of Approach for OS/2 - dBase IV limitations - Limitations of Approach - Maximum of 50 joins limitation - .oyz index files - Files and File Types - Maximum of 20 charts per .apr Optimizing databases and minimising the chance of problems - Optimizing the speed of a database - Compressing databases with a macro or script [UPDATED] 3 Mar 2007 - Reducing ("Compressing") the size of a database file - Why doesn't the .dbf file size decrease when I delete records? - Good practices to reduce that chances of encountering problems [UPDATED] 3 Mar 2007 - DON'T delete macros, named find/sorts or scripts... - Backing-up your data using a macro Corrupt index (.adx) files - .apx files - Repairing corrupt index (.adx) files - Deleting and recreating index files (.adx) Errors, lockups, crashes, and other problems - General Protection Faults (GPF's) - Repairing corrupt .apr/.vew files (e.g. errors when changing views) - Out of Memory error message - "Record is locked- retry -cancel" error message - "Someone locked this record" errors - "Unable to open ___.dbf file, 5009" errors - "Macro error: Import table has changed" error - Internal Error: Cannot load string 0 - "Could not load infobox dynalink" error - InfoBox, Tools Palette, etc... won't appear - "Page Fault... at 0137:0049eb64" in Approach 96 - Approach 3.02 crashing in design mode and when switching views - "An error has occured in your program" errors - Database being used exclusively error - "Internal Error - Cannot load string - 1035." error message - "GPF in APPREXPR.DLL at 0009:0D2C." error message - Vanishing scripts - "Could not find database [path]\name.mdb" error - "couldn't open database because it is open in another language..." - Globals Sub "Initialize" doesn't always get executed Printing - Printing formatting problems - Printer problems (including GPFs, HP Printers) - Header / Footer problems in v2.1 - MS-Windows 95 printing blank pages (v2 & v3) - Printing field definitions - Printer fonts problems - Pages sizes in v9.6 Dates and times - Calculating elapsed time - Calculating ages from birth dates - Calculating number of weekdays - Finding or calculating dates - 21st Century Dates - Two digit year assumptions (eg 01/01/29) - Date format with 4 digit year (e.g. 1997) - Importing Dates - Dates in report titles - Find the previous weeks records - Y2K / Millennium bug compliance and leap years - Time and Date Schedule (uses SQL and multiple records) Formatting and displaying fields and buttons - Field Formatting (including not / rounding numbers) - Displaying Negative Values In Red (Changing fonts colours using LotusScript) - Leading zeros (eg. 00054628) - Trailing zeros (eg. 1.400) - Display problems (including problems switching to a particular form) - Removing unwanted characters from phone numbers - Radio Buttons Field Label - Make empty fields disappear (... rather than just turn invisible) - Converting numbers to words 34 to thirty-four? - Sliding fields together on mailing labels - Including quotes, symbols and international characters in strings - Phone numbers - How to make a field invisible on a form - Conditionally display a pushbutton - Stopping Approach from using Marlett as the default font Reports and form letters - Deleting the body in summary reports - Tips on layout of Reports / Altering report width - Total page counts on Reports - Form letters (and multi-page reports) - Stopping v96 (and beyond) from entering design mode when switching to a report - Repeating panels in Reports - Creating HTML and text file reports Data entry - Limiting the options in a drop down list using an Alias table - Making keyboard entries into a formula or macro - Limiting the number of characters entered into a text field - Conditional form navigation ( tabs ) - Recovering deleted records Finds - Automatically finding a 'drop down' list entry - Preserving the found set when switching forms - Finds on multipage forms - Finding records which do not contain *text* - Tricky finds - Including Find criteria on a report - How to pre-fill fields in a form-based find Sorting - Setting default sort order for a database or view - Saving records in their sort order - Sorting memo fields or fields that reference a memo field Calculated fields - IF limitations in v3.0* - Using values from the previous record - Tricky calculations - Generating a random unused PIN number - The Combine() function in calculated and memo fields - Using calculated fields in joins and exports - Calculations on numbers in a text field (eg imperial measurements such as 3'6") Summary fields - Summary fields - Demystifying Summary Functions - Problems with summary panels - Using a summary field to count records which meet certain criteria Repeating panels - Problems with repeating panels - Finds on repeating panels fields - Using an alias to limit the records in a repeating panel Working with other data formats and applications - Exporting numbers - Approach 97 incorrectly exporting numbers via macros - Working with Paradox database files - Accessing Access files (*.mdb) - Accessing .dbf's updated by Access / Paradox - Using index files other than .adx (eg.mdx) - Export of Crosstabs to 1-2-3 - Using Approach and IBM DB2 - SQL and Approach - Using SQL from LotusScript - Using SQL in Approach with non-SQL data sources - Sending email directly from a form - Using the contents of a variable field as an export filename - Exporting summary fields - Using a macro or Script to open documents in other applications - Problems getting TeamMail to work - Export in the Quickbooks export format - The Approach ODBC SQL server driver - ODBC Connection to Oracle database tables - Transferring data to the Clipboard - Accessing information in an Approach database on the web (WWW) - How to lauch your default browser to view a URL contained in a text field Network installation of Approach - Running Approach on a network - Finding the current Network ID - Dealing with different paths to the same data from different computers - Accessing .dbf files using Novell client 4.83 on Windows 2000 Upgrading Approach and/or your operating system - Do you need to upgrade? - Conversion of old .apr's to Approach 96 - Approach and Windows NT - Upgrading ... - Free Approach 3.02 Upgrade - Approach v97 and Windows 98/vME - Running different versions of Approach concurrently - Running applications developed in Approach for MS-Windows on OS/2 versions - Installing the Approach 97 apfixpak - Approach and Windows 2000 - Approach on a Linux server - Dialing phone numbers from within Approach - Approach and Windows XP Building applications and working with .apr's - Automatically opening files - Automatically opening a particular view - Automatic macro on opening/closing View file - Keeping users out of design environment - Database security - Changing security passwords - Stopping people accidentally entering data - Stopping users from deleting records - Stopping Approach from prompting to save an .apr - Running a Macro in a different .apr files - Moving a view from one .apr to another - Starting Approach without displaying the startup banner - User selection of database tables within an .apr - Distributing .dbf database files to users - Scripts are not imported with an .apr Example LotusScripts - Getting the path of a database file - Screen updating during script execution - ConvertNumberToText - CurrencyToWords function - Conditional Automatic Entry - Create a delimited text file from two fields - Disable the action bar, smart icons, status bar and view tabs - Serialized invoice number on billing - Draw rounded rectangles to size (for envelope windows) - Using keydown event to automatically enter or modify a date - Launch multiple Approach applications at start-up - Syntax use of currentview.body.objectname.text - Script Launch at Record Change Event - Check Existing Data and ask for details for new record - Lotus Notes SQL script - Read in the Approach User Section in win.ini - Handling Approach Object Errors - Error trapping and data manipulation script - Creating a dot notation text file - Progress bar - Flashing text - Import a text file (or .dbf) into a DBF - Linked List class (beats 64K limit on arrays) - Fill Field No.1 - Fill Field No.2 - Named Find Event - Named Find Event with selection sort - Named Find Event with a different sort routine - Find the current network ID No.1 - Find the current network ID No.2 - User selection of database tables within an .apr - Pushing selections from one list box to another - Accessing items in a list box - SQL delete - Activating a macro in another APR - Converting Approach form to MS Word Document - Creating a new row in a repeating panel - Copying calculated fields to the clipboard - A button activated script that launches URL contained in a text field - Loading a document into some other application - A "Translate" function in LotusScript - Another "Translate" function in LotusScript - Passing the contents of a field to Google.com - Splitting a text field apart at Tabs - Using Windows 95/98 API to launch another application - Go back to the last thing that the user was using - Go back to the last thing that the user was using (A different response) - "Freezing" the screen while executing a LotusScript - Controlling the window size when opening Approach Unclassified articles - Generating and reading barcodes - Zip codes and Post codes - Recovering deleted records - Turning off screen updates when running a macro - Advise when using memo fields... - Creating a 'Help' file - Storing pictures - Remote dialup access to Approach - Multi-line messagebox - Tips on using the Script Editor - Printing out .apr file properties - Setting the auto-incrementing serial number - Quick access to objects in the script editor - Automatically saving your work How the Approach Users Mailing List works The Approach Users Mailing List The Topic Off topic and personal messages The List Owner Subscribing, unsubscribing, digests and other options Question and Answer Posts comp.databases Lotus Mail storms Official Stuff Contributors Disclaimer Editor ####> Notices ##>: The Approach User Support web site The is the official web site associated with the Approach Users Mailing List and the other related services. Check it out at: http://www.netspace.net.au/~jabrown/approach/ ##>: How to get the latest edition of the FAQ The latest version of the FAQ is available at the Approach User Support web site: http://www.netspace.net.au/~jabrown/approach/ ##>: How to receive a newsletter containing FAQ updates If you are not subscribed to the Approach Users Mailing List and you would like to automatically receive a notice when the FAQ is updated then subscribed to the AUMLNEWS list on the following web page: http://mailman.anu.edu.au/mailman/listinfo/AUMLNEWS The AUMLNEWS only carries notices of FAQ updates and occasional messages about important events such as the release of a new version of Approach. ##>: How to get the Archive of LotusScripts The Archive of LotusScripts is now incorporated in the FAQ available at: http://www.netspace.net.au/~jabrown/approach/ ##>: Contributions, comments, corrections, or feedback If you have any comments, corrections, or feedback, please email me (John Brown) at approach-owner@mailman.anu.edu.au However, new FAQ articles and scripts should be set to the mailing list first so that people have a chance to comment on it before it goes into the FAQ. ##>: German language FAQ and newsgroup Many of the articles in the FAQ are available in the German language at: http://smartsuitefaq.vol4u.de There is also active German language newsgroup which contains discussion about all SmartSuite applications, including Approach. You can find it by pointing your newsreader at: de.comp.office-pakete.lotus-smartsuite ##>: Lotus support now at http://www.ibm.com/support/ Our frustrations with http://www.support.lotus.com have finally come to an end! The site has been shut down, and now the official place to get support for Lotus Approach on line is at http://www-3.ibm.com/software/lotus/support/approach/support.html, which at this stage looks as if it is going to take your frustration to new and dizzying hights! I haven't got around to removing/updating my links from the old site yet. I'll try to get around to it soon. In the mean time, if anyone stumbles across an old link and knows where it should be redirected to, feel free to let me know at approach-owner@mailman.anu.edu.au. Thanks! ####> Our collective wisdom so far... ###> The basics ("Stuff you really should know before you read other stuff") #> Getting started with joined databases! Understanding relational (or "Joined") databases is a big area, but is vital to fully utilizing a program such as Approach. It can be a frustrating time with many concepts to learn, as well as all the ins and outs of Approach. DO NOT START by launching into some major project that you expect to be fully functional tomorrow. Start small, using some test data, and test each function before you go onto the next. Read all you can, and spend time carefully planning what databases you need and how they should be joined. Once you have the basic structure of your database set up you can begin entering or importing you actual data. But as I said, start by do some reading. Some books are recommended earlier in the FAQ, but don't forget the Approach manual either. It briefly takes you though the process of setting up joined databases. Looking at the example databases that come with Approach will also help you understand how the different data in different databases is linked together to provide many facilities that are not possible in flat databases. #> Help me design my database! (joins, repeating panels, what data in which database etc...) Apart from simple 'card file' type databases, every piece of major database software you can think works with relational databases; Approach, Paradox, Access etc... In order to use any of these packages efficiently, including Approach, you first need to understand what a relational database is, and how you would divide your data into one. If you don't understand this then you are going to have difficulty using many features of Approach, or any other database software. Questions concerning database design are quite common on the Approach Users Mailing List. However, strictly speaking most of these questions and answers have little or nothing to do with Approach. What the people asking these question lack not so much an understanding of how Approach works, but rather an understanding of what a relational database is and how they work. And as it turns out, I am not going to attempt to explain this! Instead, what I suggest you do is this: Allocate some time and perhaps money, to do some reading or perhaps a short course in relational databases. Some books are suggested above. The Approach manual includes some information, but most people seem to need a more in-depth look at it before they really grasp what is happening. The better this ground work the easier and quicker you will be able to build an efficient database. I won't take all that long, and the benefits are huge. Also have a careful took at the example databases supplied with Approach. Go into design and check out the structure of the database; the design of the various views; and how each aspect works. Its a good idea to also become familiar with all the features of Approach so that you can be aware of what is it ~ and isn't ~ capable of. In particular, getting to know macros and how to loop them is is very handy (see articles 'Looping macros' and 'Conditional form navigation ( tabs )' in this FAQ) As you are doing these things keep some notes about how you might structure your database. When you are ready begin establishing your database one bit at a time with a small amount of test data. When it is fully functioning, or at least developed enough to do all the major functions, import your data, and begin using it! #> Designing and problem solving techniques Try this: Rather than working with you main database, try setting up and testing out things or trying to solve problems on smaller practice databases. This way you minimize the possibility of you loosing valuable data, or disabling your main database when you really need to use it. For example, if your having trouble with an import or export, set up a very simple flat (i.e. no joins) database and view form. Enter some junk data into a couple of records and try exporting it as a text file. If this works, try importing it back in again. When you can do this, expand this test database so that it has two databases that are joined, and the data from both database is displayed on the one form. Then export some data. Add a repeating panel. Export some more. Import some back in. At every stage do only one thing at a time and test it. This way you will easily identify at which point you are getting into trouble. Once you have managed all of this on your test database you should be able to solve the problems on you main database. #> Looping macros (Including sequentially numbering a set of records and making global changes to a set of records) This FAQ demonstrates macros implemented in Approach V3, 96 or 97 or any of the "Millennium Edition 9.x" releases. Macros were implemented differently in prior versions of Approach, so if you used version 1 or 2, you will need to spend some time in the help files / manual of Approach to accomplish a similar result. Broadly speaking, a looping macro (which may consist of several individual macros working together) does the following: * performs a particular operation or operations * moves to the next record * runs the macro again from the top This sequence of steps means that the operation is performed on every record in the current found set. Conditions can be added to the operations so that they are only performed under certain conditions. For example, if you wanted to add a 6 to all of the phone numbers starting with a 5, then your macro would include the statement: SET db.phonenumber to If(Left(db.phonenumber,1)='5', Combine('6', db.phonenumber),db.phonenumber) (where "phonenumber" is a text field) Often it is necessary to have a macro that precedes the looping macro to set up the conditions that the loop needs for its correct operation. (I'll call this a Control macro). This may do things such as: * Go to the required view * Find the set of records to be processed * Position to the first record in the set * Set variable fields to initial values * Then "run" the looping macro * Give the user a message that the operation has completed. The last operation of the looping macro always runs the looping macro again (NOT THE CONTROL MACRO) This is demonstrated below, but first... To carry information from one record to the next you need to use variable fields. For instance, if you want to sequentially number a set of records you need to know what number the previous record was in order to set the number of the current record. The following example demonstrates both the use of a Control Macro and a Variable to sequentially number a set of records: 1) Create a numeric field 'VarIDNumber' to hold the sequential number, and a variable field 'VarID' to use in calculating the sequential numbers. 2) Create a looping macro 'SequentialLoop'. This macro should do the following: * SET VarIDNumber to VarID * SET VarID to VarID + 1 * ENTER (to save the updated record) * RECORDS Next (note this command will exit this macro when it runs out of records in the found set) * RUN SequentialLoop For this macro to work properly it needs the Control Macro created in Step 3) 3) Create a macro 'Sequential numbering' to be the Control Macro. It should do the following: * VIEW switch to the required view (optional) * FIND the required found set (optional) * SET VarID to the required number for the first record * RUN the looping macro 'SequentialLoop' and return to the next statement * MESSAGE "The records have been numbered successfully." (optional) To execute the looping macro, run the 'Sequential Numbering' macro. Note: In V2.1 you need to use three macros (it does exactly the same things as shown above, but it just takes 3 macros to do it. In v2.1 it is easy to end up with loop macros containing several macros): Macro 1: Set the variable field to 1 Run Macro 2 Macro 2: Set the numeric field to variable field Menu Options: Next Record Run Macro 3 Macro 3: Set the variable field to variable field + 1 Run Macro 2 Also see 'Record numbering' in this FAQ. #> Record numbering, including auto-incrementing serial numbers It is often useful to have a unique identifier for each record in a database -- like a customer number or order number, for example. Or to use in joins where other unique fields are not available. For example, you would not want to use a telephone number in a join because the number could change over time. So it would appear useful to just use the record numbers Approach assigns as you add records. You know, those numbers you see in the status bar at the bottom of the Approach screen? But this is not the case. Why? * The record numbers are not part of the database and are not permanently assigned to particular records. * When you sort the records, the record that is #1 changes to the one that is "relatively" the first one in the current database. * When a record is deleted that record's number is not also deleted. * The record numbers are not part of your data, and you do not have access to the record numbers in calculated fields or finds etc. Not even from a script! If you want each record to have a fixed reference number that never changes as long as the record continues to exist, then you need to create a numeric 'serial number' field which automatically increments each time a new record is created. To do this, just create a numeric field using Field Definition. Then select the desired starting number and increment value in 'Options'. If you have existing records with no number in this field, you will want to add a unique number to them using the technique described elsewhere in this FAQ in "Looping Macros". Unfortunately, the auto-incrementing serial numbering options on numeric fields are not accessible from LotusScript, which means that you cannot read or change the next serial number from a script. In other words, if you add a record using a ResultSet in LotusScript, the serial number will not be entered or incremented. In this situation you have two options: 1) you can write your script so it doesn't use a ResultSet and instead adds and updates the record using a view. 2) you can create and use your own auto-incrementing field instead (details of how to do this can be found at http://www.xpertss.com, under 'Maintain auto-serial numbers when creating records with a ResultSet object' in the 'FAQ's - Tips and Techniques' folder). #> Using Boolean fields Boolean fields can contain a true or false value, or can be "empty" (ie contain a "null" value). Therefore if you want all records to have either a true or false value in the Boolean field then you need to specify a default value in Field Definition, and update any existing records using a macro or script, or FillField. Alternatively, when you need to search for false items, search for all of the items with do not contain a true value. This search will return all records in which the Boolean field contains either a false or null value. You can't use a Boolean field in a join, but a way of getting around this is to simply use a numeric field that either contains a '1' or a '0', so use that instead. #> Reinstalling Approach If you are reinstalling Approach to recover from a "one of the library files damaged" error or an unexplained GPF, you may need to uninstall Approach first. When reinstalling Approach it does not replace a file unless it really thinks it needs to. So if a file is corrupt but the installation program does not detect that it is corrupt, then the file may not be replaced - and your problem will remain unless you uninstall it first. ###> General information #> What is Approach? (Is it truly relational?) This article assumes you are familiar with relational database terminology. If you are not, then all you need to know is that Approach is a database application! Approach is a RDBMS (Relational Database Management System) or 'client-side file server' application. It does not have a native database format but operates as a front end to many different data formats (dBase IV by default). It will also operate as a fully functioning front end/client to Client/Server database servers such as Oracle, including the ability to take advantage of Stored Procedures, Triggers, and the like. Approach is more relationally compliant than many other desktop RDBMS's in that it partially supports cascaded updates and deletes, as well as referential integrity constraints. #> How does Approach compare to other DB's? The following is an excellent article comparing six major database packages, including Approach and Access. http://www8.zdnet.com/products/content/cshp/1804/288515.html Articles about how Approach ranks against other databases, as well as handy hints, and how to optimize your productivity with Approach and SmartSuite, are available by searching the following web sites: http://www.pcworld.com http://www.reviewsource.com http://www.pccomputing.com The following is a collection of comments made by different subscribers comparing Approach and Access: Approach is oriented toward the non-programmer both in ease-of-use areas and in terminology. You can get a lot done with just Approach's design tools and a few simple macros, whereas in Access you must get into "basic" programming fairly quickly. Defining fields is an example of the programmerese in Access - it uses technical terms instead of the more simple "numeric 10.2" or "date fixed" terms in Approach. I also like the idea that my data is stored in commonly used formats like dBaseIV as that opens up the data to use with dBase shareware and reporting packages, if I need them. Access stores all your data and views in one file - you lose it and you have "lost the farm". --- As far as Approach vs Access, both have advantages and disadvantages. I find Approach easier to get started with. It also handles data in a safer manner. Your data tables remain separate from your front-end file (the .apr file) which makes them more secure, easier to save, and available for other programs. (The last could also be a disadvantage under some circumstances.) Access has much better third-party support - you'll find 20 or 30 Access titles in Borders or Barnes & Noble, and (maybe) one on Approach. And IBM (the owner of Lotus) does not have the same commitment to desk-top computing and software that Microsoft has. Because your tables are not part of your various .apr files, you can start all over again with new .apr files, and safely destroy the old ones when you want to. You may have noticed that you create calculated fields in Approach - for display in forms and worksheets, and summaries in reports. The calculated fields are in the .apr files, so you might want to write down any neat ones you have developed for use in new .apr files. You can create and use multiple data-entry forms with any single table or any group of tables in one .apr file, and turn around and use some of the same tables, with other tables, in a separate .apr file. For example, the "customer" table can be in the sales-contact database, and also in the Accounts Receivable database, in such a way that the sales people would not see the money records, and the bookkeepers would not see the future sale projections. (Revenue and projections being in separate, related tables.) You can copy views within a .apr file. The only way I have found to copy a view between .apr files is to import the entire source file, and delete the parts I don't want. In doing this I also have to reset the table joins and connections. --- When I evaluated Approach v Access the advice I got was that Approach was easier to learn and much faster to develop in. You can develop a larger app in Access. Approach has limits like 50 joined tables in an apr whereas I think (from memory) Access can handle 100. Anyhow, most Approach users would say that once you get above 20 joins in an apr you should split the app over multiple apr's for performance reasons. For our Company's circumstances Approach seemed the way to go and has turned out a big success. --- I use both Approach and Access. I tend to agree with those that feel that Approach offers a more friendly user interface. I have not come across any tool yet that can compare to Approach in terms of quickly designing and rolling out an app. On the other hand, I have found that I am beginning to push the limits of what I can do with Approach. I was forced recently to develop a billing application in Access, because I just could not get Approach to do what was needed without gobbling up all available memory or generating gpf's. I qualify this by pointing out that I do my Approach development in v3.02 of Approach (16 bit). I cannot speak to the relative merits of Approach 97. Overall, though, I still prefer to use Approach whenever possible. On your second question, there is no "correct" number of .apr files to underlying tables. I find that I use multiple APRs with particular tables, each tailored to the needs of a particular group of users. This flexibility is, IMHO, one of Approach's best features. --- Approach vs Delphi 4?: Comparing the two products is like comparing apples and oranges. Delphi is a very good product if you like Pascal. It's not the easiest product to learn. Each time you perfect a level of understanding it, they come up with something new and better. If you write application for sale then it's one of the good products beside Topspeed. If you write database management programs, you'll find Approach to be one of the best tools... For you and me Approach is better because it takes us maybe two days to knock out a database, in Delphi or Topspeed think of two weeks to two months or more. #> IBM's Lotus Approach Support web site IBM's Lotus Approach Support web site can be found at http://www-3.ibm.com/software/lotus/support/approach/support.html. It includes a downloads FAQ, 'Technotes', Product Information and 'White Papers'. 'Technotes' are technical documents which address particular problems people have had with Approach. It is a good idea to search here first if Approach is giving you an error message. 'White Papers' are "Detailed investigations into how products work, often including coding tips and techniques." #> Books The following books have been recommended in posts to the Approach Users Mailing List: * The manual that come with Approach. Always a good place to start! * Approach 97 for Windows for Dummies by Deborah S. Ray, Eric J. Ray / Paperback / Published 1997. This appears to be the only book that is specifically about v97. However, you may want to check out some of the v96 books first, as they cover most of what you will need to know and the 'dummies' style isn't for everybody. * Mastering Lotus Smart Suite 97 for Windows 95 by Sandra E. Eddy, includes 'reasonable' coverage of v97 apparently. * Teach Yourself Lotus SmartSuite in 24 Hours by Faithe Wempen and published by published by SAMS. Described by one person as a "Wonderful book, .... broken down into 24 lessons." * Using Lotus Approach 96 Special Edition (Que Books) (Described as being particularly good for New Users) NOTE: If you are looking for a v97 book, the v96 books are still worth getting as the differences are minor and when you stumble across a difference you should be able to get the extra information you need from the Approach HELP files. * Using Lotus Approach 3 by Plotkin (Que Books) * Mastering Lotus Approach 96 for Windows 95 by James Powell (Sybex / Lotus Books, ISBN: 0-7821-1773-2) (This is the most highly recommended book, however, some subscribers have commented that IF YOU ALREADY OWN THE EARLIER VERSION (for v3.0*, see above) then check it out carefully before you by this one. They expressed strong disappointment that it has only been minimally from the earlier version, and major aspects of Approach 96 are not covered adequately. It therefore may not be worth the expense.) * Mastering Lotus Approach by James Powell (Sybex / Lotus Books) (This is the most highly recommended book for v3.0*) * Building Databases with Approach 3 by Elaine Marmel ("...seems to be an extended, well documented, tutorial on building a database that is usable in a business environment.") * Approach 3.0 for Dummies * Practical Approach version 3.0 by Siegel (MIS Press) * Introduction to Databases" by James T. Townsend (recommend for business uses who are learning and updated designing databases. It is about databases in general rather than how to use Approach.) #> Example and commercial Approach applications See the 'Archive of Example Database Applications' at the Approach User Support web site: http://www.netspace.net.au/~jabrown/approach/examples.htm This includes a few of downloadable databases, as well as links to other such sites. #> Other information sources about Approach * A good way to learn about database structure and what Approach is capable of is to study the example databases that come with Approach. * The Approach User Support links page includes a load of Approach related web sites: http://www.netspace.net.au/~jabrown/approach/links.htm * The comp.databases newsgroup contains discussion about many different database applications including Approach. However, I haven't seem any Approach discussion on it for a long time. * de.comp.office-pakete.lotus-smartsuite is an active German language newsgroup which contains discussion about all SmartSuite applications, including Approach. Also see the German language version of this FAQ at: http://smartsuitefaq.vol4u.de The following non-internet resources are the above web page, but are mentioned here for the benefit of those who don't have web access: * Lotus fax - back service: A blast from the past with was discontinued on November 6, 1998. * Lotus Forums on Compuserve: - GO LOTUSB for volunteer support on Approach - always good advice. - GO PCAPP - the PC Applications forum has a general database section. - GO BORLAND - for help and information on dBASE tips and utilities. - Search Compuserve for forums about DATABASE and you will get a long list of helpful contacts for Paradox, Access, Foxpro, DB2, and other database formats you may be using. * The Approach BBS: Now a mere memory in the history of online services #> Other information sources about LotusScript * A lot of the sources of information about Approach shown above will also include information about LotusScript * Don't forget to have a good look through the examples that come with Approach in templates, saved smartmasters etc... * See the Approach User Support Links page for some helpful web sites: http://www.netspace.net.au/~jabrown/approach/links.htm * LotusScript for Dummies (ISBN: 1-56884-638-X) I D G Books * Chapter 8 of "Developing SmartSuite Applications Using LotusScript". This book is on the SmartSuite CD in .pdf format which you read using Adobe Acrobat which is also on the CD, or you can download it from the PSC SmartSuite Developers site in the Resources section ( http://suitedev.psclistens.com/lotus/smtsuite/smtstdev.nsf/?Open) or by searching for the title at http://www.lotus.com. * "60 minute guide" for Notes LotusScript. This book assumes that you an understanding of programming background. * The LotusScript Language Reference, a comprehensive summary of the LotusScript language, presented in A-Z format. The LotusScript Language Reference is available as Help in all Lotus products that support LotusScript. * The LotusScript Programmer's Guide, a general introduction to LotusScript that describes it's basic building blocks and how to put them together to create applications. It is on the SmartSuite CD-ROM in .pdf format, and is also available for download (alone with other documents) from Lotus' SmartSuite Products pages (accessible via the Approach User Support Links page mentioned above). * The 'LotusScript Expression Assistant' is a programming tool available by searching for the title at http://www.lotus.com, which can apparently help you learn Lotusscript language faster, and is useful to rethink ideas and which arguments can/must be used. #> Can I Develop Stand-alone databases (Run-time) using Approach? No, there is no way of creating fully stand-alone database applications using Approach. Any user of your application will need an appropriate version of Approach installed in order to use it. MS Access and some other database systems have either a "compiler" to create a runnable program from your design or they have a "run time" module that replaces the need for users to install the full product it was developed with. Some charge extra for this feature and some do not. But, there are currently no run time modules or compilers for Approach and none are under development. Lotus/IBM does not see any demand for this type of capability at all from their large corporate customers. According to Lotus, Approach was designed to be a 'desktop database' or 'front end' to data the user wants to manipulate. The idea is you can take an existing database (dBase, Access, Excel, Foxpro, DB2, Oracle, Paradox, SQL, 123 ...) and create your own little application, leaving the original database intact for others. Or you can develop your own simple database from scratch. You can also develop a more complex application with macros and buttons that give your application the appearance of a full-blown application, and you can distribute it to other users, but everyone who uses it needs their own copy of Approach. Using the "Customizing and Automating Approach" chapter 9 in the user's manual you can secure the system such that your users can't harm the database that you build for them. Pay particular attention to the Preferences, SmartIcons, and Custom Menus sections. You can design your database using custom pull-down menus and various buttons on forms so that all the native controls in Approach can be turned off or hidden from the user. And you can further protect both the data and your application's design by using Team Computing features covered in chapter 10. Also review the procedures for defining basic field settings in the various views you give the user to help the user enter data correctly and protect fields they should not change. FYI, this manual is available in Adobe .PDF format from the Free Downloads area on XpertSS.com. Lotus is often amazed by the complex business applications that are being deployed with Approach! If the cost of multiple user licenses for Approach users is a problem, purchasing site licenses and using IBM' volume purchasing scheme will greatly reduce the costs for businesses. And there are several discount software web sites that often discount older versions of the SmartSuite, making it very affordable. Approach 96 and newer versions also include 'LotusScript' which gives you much the same capabilities of Access Basic. You need some programming skills to write your own scripts, but you can often copy scripts and modify them for your own use. This site and http://www.XpertSS.com contain many examples for your education and use. #> Can Approach handle big databases? Well, it probably depends on what you call big, the design of your database, and what hardware you have. People have reported databases of 3 or 4 million records, although at that size running summary reports and finds may take a while! Nobody has ever reported reaching a maximum number of records limit in Approach. In theory it should be able to handle the maximum number of records you can put in a dBase IV database, which is 4294967296 (4 gig = maximum 32 bit number). In order to maintain the best ratio of speed and stability you may want to consider deleting the .adx files (indexes) on a regular basis (say every week or two). There are some possible complications with this so carefully read "Deleting and recreating index files" first. It has been suggested that the number of joins has a far greater impact on the speed and stability of large databases rather than simply the number of records. One subscriber stated that they found a database having "80,000 records and approximately 20 tables joined" to be unusable slow, and suggested that a database of "100,000 records if there are more than 4 or 5 joins" to be a maximum usable size. Meanwhile somebody else replied saying that they had "databases exceeding an average of 400,000 records up to 2.2 million records and joins exceeding 10 databases with Approach 96 having no problems handling the data. "We have been doing multi field queries in an acceptable time frame and once the smart indexes are created, finds on fields are just as fast on a (database) with 2.2 million records as a find on a field with 400,000 records. Our standard hardware is a P5 166 with 64 meg of ram. On a P6-200 with 64MB RAM, doing a complex search on 4 fields takes 15 sec." #> Moving or copying databases (.apr/.vew .dbf .dbt files) The APR file includes the paths to the files as well as their names. Therefore unless you reproduce the directory structure exactly at the destination, the only reliable way to move or copy at database is using the FILE / SAVE AS menu item from within Approach. If you try and move them using a file manager program then the database may not be able to locate some of its components and OLE links may be lost. However, for some strange reason, Approach returns a "Too many files open at once" error message if you try to use SAVE AS on a database with more than 25 joins. If you are using Win 3.1, this happens regardless of how many files your have specified in your config.sys. #> Help for MS-Access uses moving to Approach If you have been using Access for while, then the first thing need to understand is that good quality professional help is available, and you should make a full recovery in no time at all ;-) Sue Sloan has written a booklet oriented toward telling an Access user how to use Approach. A link to that booklet is in the Free Downloads area on http://www.XpertSS.com. Do a search with the word "access" in the title. #> Getting started with LotusScript Getting started with LotusScript is a bit like driving a car - you do actually have to spend time learning how to do it! The best place to start is the resources listed in article 'Other information sources about LotusScript' The look through the example LotusScripts for ideas, especially if some of them come close to doing what you want a script to do. It is often easier to start playing with someone else's script and getting it going than starting from scratch with your own script (until you become an expert of course!). Some good example scripts to start with are the following, because the authors have included pretty explicit instructions about how to get them going: 'A button activated script that launches URL contained in a text field' 'Passing the contents of a field to Google.com' ###> Approach/.dbf specifications and overcoming limitations #> The latest version and upcoming developments As of October 15, 2003, customers can find information on the lifecycle of IBM Software products, including Lotus software products, on the IBM Software Support Lifecycle Web site at the following address: http://www.ibm.com/software/info/supportlifecycle/ No stand-alone versions of Approach are supported currently. But these three SmartSuite versions are supported. Their policy is to "Provide support for all products for a minimum of three years, beginning at the date the product is available for purchase (general availability date)". * Lotus SmartSuite for OS/2 Warp4 R1.7 1.7 (Released 22 Oct 2002 ) * Lotus SmartSuite Millennium Edition 9.7 -- Win98,NT,2000 9.7 (Released 13 Nov 2001) * Lotus SmartSuite Millennium Edition 9.8 -- Win95,98,ME,NT,2000,XP (Released 22 Oct 2002) Not listed there yet is the 2003 new SmartSuite version 9.8.1 which is available only through the IBM Passport Advantage program. Released in October 2003, it will be supported for a minimum of 3 years through October 2006. For "fix list" and support information about the last versions of Approach for Windows and OS/2 check the product information at IBM's Approach support website: http://www-3.ibm.com/software/lotus/support/approach/support.html Beta versions of versions underdevelopment are no longer available for SmartSuite. Lotus stopped doing beta tests with the 97 version. After an outbreak of insecurity on the list back in 2002 about whether IBM was going to keep supporting and developing Approach, Sue Sloan wrote: Lotus/IBM is committed to the SmartSuite and Approach and they provide full support for it -- the same as any other IBM product. They have tech support staff and a full development team for Approach. I expect to see the 9.8 release this Fall and further updates about every 12-14 months. I have mentioned many times before on the various Approach forums that IBM/Lotus makes a lot of money from the SmartSuite product, but they do not sell it 'retail'. 99% of their customers are larger companies that buy many licenses when they decide on a 'suite', so it is sold thru the IBM sales force directly, and very successfully. At one time recently, Lotus mentioned in a speech that there are 20 million licenses for the SS out there! Finally, IBM and Lotus are not at all likely to kill the SmartSuite and buy 300,000 copies of MS Office or StarOfice for their own employees to use. As long as there is a need for the types of programs in SS within IBM, there will be an SS product. It may not get enhancements that we all want, like a run-time version of Approach, but it will be enhanced and supported. As of the Fall of 2003, Lotus management has reconfirmed their committment to the SmartSuite to Sue Sloan. Noting that their focus is not on adding new features but rather on product stability and compatibility with new versions of Windows and various updated file formats. #> Versions of Approach for MS-Windows / NT [UPDATED] 14 Dec 2006 The versions recommended are: * Windows 3.* - v3.02 * Windows 95/98 - v97 (NOT 97a) with the latest apfixpak applied, though you won't go wrong with v9.7 * Windows ME - "ME is a Dog! Avoid at all costs", so just buy a new operating system. It is a bad version of Windows 98, so if you are stuck with it, go with the W98 recommendation. * Windows 2000/NT - v9.7 or V9.8.x ***See below for details * Windows XP Home - Similar to the "ME" version, avoid it for business use. * Windows XP - V9.8.x ***See below for details Compatibility: All versions can simultaneously access the same database files (.dbt/.dbf or paradox or other formats), but do not share an index file between v3.02 and any other version because the "SmartIndex" format is not compatible. SmartIndexes are only built for dBase files, which is the default type when creating a new database with Approach. Do not convert a V2.* ".vew" or v3.02 ".apr" file to any newer version than v97 directly. This is discussed further in the article "Upgrading ...". elsewhere in the FAQ. Once converted to v97 and saved in that format, you will no longer be able to use the ".apr" file in v3.02. Once in v97 format, your ".apr" file can be opened and used with all newer versions of Approach and it will remain useable in v97 unless you use a new feature, like the "dialog editor" that was introduced in v9.5. You will get a warning about the conversion so you can avoid this problem. The version of LotusScript was updated also in the v9.5, so your scripts may not run correctly in earlier versions unless you "recompile" them. New ".apr" applications created in v9.5 and newer versions are saved in the new format automatically which makes them no longer work in earlier versions. Version history: v1.* = the first version released by Approach Software of Mountain View. (circa 1992) v2.0 = the last version before it was bought by Lotus. (circa 1993) v2.1 = the first version released by Lotus. View files have .vew file extensions. Developed for MS-Windows 3.1 (16 bit). Despite the new version number it was basically unchanged from v2.0 but contained many fixes for problems in the original release. v3.00 = a substantial new version released in 1994. Completely new macros. View files have .apr file extension. Developed for MS-Windows 3.1 (16 bit). Problems are encountered (even with v3.01 and v3.02) if it is run on Windows NT v4 (see 'Approach and Windows NT') v3.01 = a free bug fix of version 3.00. If you are having any problems with v3.00 or 3.01 then get 3.02 before you bother to post. Everybody is going to tell you to get it anyway! v3.02 = a free bug fix of version 3.01. If you are having any problems with v3.00 or 3.01 then get 3.02 before you bother to post. Everybody is going to tell you to get it anyway! This version will work on all newer versions of Windows except XP. v96 = Sometimes erroneously referred to as v4. A substantial update developed for MS-Windows 95 (32 bit). Introduction of LotusScript, the Find Assistant, etc. It was a terribly buggy release -- avoid it! v97 = Developed for MS-Windows 95 and NT 4.0 but apparently runs fine on NT 3.51 (32 bit) Included in Lotus SmartSuite 97 or stand-alone. This version was given to owners of v96 for free due to the bugs in v96. The first version with bug fix updates available on the web -- the "C" patch (apfixpak.exe) is essential! Features include stored finds (very handy) and "Find/Replace" facility. This runs fine on Windows 95 but slows down considerably in design mode on Windows 98 and later. v97a = v97 with the first apfixpak "A" applied. Apply the latest apfixpak and the version will revert to v97 vME = "Millennium Edition". Release Mid 1998. Mainly a compatibility update for Windows 2000 and a bug fix update with new 'Internet-FastSite' features. It runs faster on Windows 98 compared to Windows 95. Release 9 or 9.0 = refers to vME. Release 9 was buggy and was very slow in design mode due to putting too many things into the Windows registry (a Microsoft recommendation!). Release 9.1 = a free bug fix release of vME in December 1998 which was a service release primarily for corporate customers and was not generally available. It relieved the registry problem found in the 9.0 release. Release 9.5 = July 1999. A major update version released both as a stand-alone product and then as part of SmartSuite Release 9.5 around July, 1999. This version finally overcame the slow down in design mode problem mention since v97 on Windows 98 and later. It had enhanced 'Web functionality and Notes/Domino/DB2 interoperability'. Approach received a true "dialog editor", and the normalize feature of Notes Reporter let you use Approach to do reports on Notes databases better. It uses the v97 .apr file format by default, but if you use the Notes Normazlier or Dialog Editor or create a new .apr file it will convert the file to the newer v9.5 .apr format. Lots of bugs lead to the release of 'A', 'B' and 'C' bug fix patches for the SmartSuite version. Only one patch for the stand-alone product was made available. (Approach v9.5 was the latest stand-alone version) Release 9.6 = November 2000. Certified for Windows 2000. Included a few bug fixes. Only available as part of Smartsuite 9.6. Release 9.6.1 = June 2001 bug fix for v9.6. Release 9.7 = Nov 2001. Certified for Windows XP. Finally fixed printing of crosstabs. Only available as part of Smartsuite 9.7. This release finally fixed the crashing of APR files created in W95 running now in W2000. Release 9.8 = October 2002. In addition to Windows XP compatibility updates and new filters for Microsoft Office file formats, many bugs were fixed including the problems with "expanding" fields in reports -- new reports will work correctly even if you run them in an older version, but old reports remain unfixed. Certified for Windows XP. Only available as part of Smartsuite 9.8. Release 9.8.1 = October 2003. Released only to IBM Passport Advantage customers who purchased SmartSuite v9.8 or a maintenance agreement for SmartSuite v9.8 under this program. Falsely listed as a "fixpack" on the Lotus support web site, it is a full release but contains only compatibility updates and a few bug fixes. Release 9.8.2 = October 2004. Another update labeled a "release". Release 9.8.3 = October 2005. Another update labeled a "release", but with major bugs in the Approach component making it non-functional. A "fixpack" for these problems was posted in December 2005. Release 9.8.4 = October 2006. Another update labeled a "release". Along with the SmartSuite 9.8.1 release, it was announced by Lotus/IBM that future releases will be available only through the IBM Passport Advantage program. While "retail" versions will be announced and they will be for sale on the IBM web site, they are purchased without any support and will not receive fixpacks. This announcement was not well received on the online forums where owners of SmartSuite 9.8 that were obtained outside of the Passport Advantage program felt betrayed by this change in policy. Note that small companies and individuals do not need to apply to join the Passport Advantage program -- there is an "Express" option that lets you buy the product thru the program directly and get support for only that one product. The announcements and "fix lists" for the SmartSuite 9.8.x releases are available on XpertSS.com at http://www.xpertss.com/cgi-bin/WebX?13@@.ee6b313 where you will need to log in or register on the site to view it. #> Versions of Approach for OS/2 "Approach for OS/2 Warp 4" was launched in 1998 as part of SmartSuite for OS/2 for Warp 4. It didn't have a version number. It was presumably equivalent to Approach for MS-Windows vME. Approach for OS/2 files are fully compatible with Approach 97 for Windows files (also used by Approach for MS-Windows vME and releases 9.*) and are not converted. Older files will be converted in the same way that v97 would convert them. Later versions: * v1.1 released December 1998. A minor update including some 'Euro Support', Y2K enhancements such as sliding windows, and bug fixes (of course!) * v1.1.1 released July, 1999. A free bug fix update for v1.1. You can download it from http://www-3.ibm.com/software/lotus/support/approach/support.html * v1.5 released December 1999. A minor release update containing more up to date import filters and updated LotusScript(v4.0) * v1.6 released early 2001. It is an upgrade not a bug fix. * v1.7 released Jan 2002. Another costly upgrade. * v 1.7.1 released late 2002. An unannounced bug fix mentioned in some technotes, but only available to those with a Smartsuite support contract. This includes those that have purchased Smartsuite maintenance from Passport Advantage or those that have purchased eCS Upgrade Protection. #> dBase IV limitations Approach can use a number of different database file structures, but uses dBaseIV by default. dBaseIV has the following limitations that you might need to be aware of: * maximum file size = 2 gigabytes (per type .dbf or .dbt file) * maximum records per database = 1,000,000,000 (1 billion) * maximum of 30 open dBaseIV databases on a single computer * maximum number of fields per record = 255 * maximum size of a record = 4000 characters (= all the field-lengths added together) - Text field size equals the size defined in characters - Date field uses 8 characters stored as text YYYYMMDD - Time field uses 8 characters stored as text HHMMSSHH - Boolean field uses 1 character (T, F or blank is stored) - Memo or PicturePlus field uses 10 characters stored as a "record number" in the .DBT file - Numeric field uses characters equal to the size definition plus 1. (Numeric 10.2 stores in 13 characters, 5.3 stores in 9 characters. * maximum length of a text field = 254 characters * maximum number of characters in a dBaseIV memo field is 30,000 characters in releases prior to V9.8, which was fixed in that release to allow up to 65,536 (64KB). Some common error messages when using dBaseIV files: * -1003 when adding a field makes the record too long * -1035 when the .DBT file has reached the 2 gigabyte limit and no more records can be added Information about field names: Approach Field Definition allows up to 32 characters for field names, and it allows alphabetic letters, whole numbers, spaces, commas, periods, and arithmetic signs. If you delete the SmartIndex file (.ADX): * Your field names are truncated to the dBaseIV standard of 10 characters, all capitals, with no blanks or special characters other than the underscore. * Non-allowed characters and blanks will be replaced with the underscore. * A "Time" field name will always end with the characters "TM9" so that Approach will know it is a time field. * PicturePlus field names always end with the characters "PC9" so that Approach will know it is a PicturePlus field. * dBaseIV Field names must be unique, so if you have field names that are the same in the first 10 characters, you will see a series of similar field names which may be difficult to work with. For example, "Customer Name", "Customer Number", "Customer Notice" will become "CUSTOMER_N", "CUSTOMER_2" and "CUSTOMER_3". Information about sharing dBaseIV files: Approach does not create or maintain the native dBaseIV index file for a database. This is important if you plan to also use the database in an application that depends on the presence of this index. Approach PicturePlus fields cannot be viewed in other dBASE applications. #> Limitations of Approach (also see 'dBase IV limitations' in this FAQ) VIEWS: * maximum number of views (ie forms, worksheets, crosstabs, reports,...) per .apr = 100 * maximum of 5 pages per form type view for v96 and later; 1 page per form for v2.1 & v3.0* * maximum of 20 charts per .apr (see 'Maximum of 20 charts per .apr' article) * maximum of 20 crosstabs per .apr * Maximum number of summary panels in a report = 8 * maximum rows displayed in a repeating panel = 30 FORMULAS: * maximum number of characters in a calc field formula or find condition formula is 255 * maximum of 30 imbedded IF's in v3.02 (see 'IF limitations in v3.0*' article) JOINS: * maximum number of joins you can create between unique databases (that will be read-write) in one .APR file is 30. Any database joins you create after that will be read-only, up to a limit of 50 joins. * maximum of 450 characters in all the joined fields per .apr (but no limit to the number of actual joins). * circular joins are not allowed, but you can create an "alias" of a database and join it as appropriate. An "alias" is a "virtual copy" of the selected database. These joins count toward the other limits. * field types not allowed in a join are: Boolean, Memo, PicturePlus, Variable (these types are not displayed in the join dialog) * calculated fields cannot be used in a join unless they reference only fields in one database and constants, and if they are not summary type fields. MISCELLANEOUS: * maximum number of characters in a variable type field of type text is 254 * maximum number of fields used in a sort = 255 * maximum of 340 characters in the text area of a MESSAGE command in a macro. Note that you can type more than 340 characters there, but if you save the .apr file it will crash on opening. * maximum of 160 characters in a text string when using the SET command in a macro. This limit applies to all versions of Approach prior to v9.8 * maximum size of a string variable in LotusScript in v9.1 and earlier = 32,000 characters. In V9.5 and later it is limited only by your computer's available memory. USING APPROACH: * maximum number of concurrent users of a .apr file = 16 * Maximum number of dBase database tables open at once = 29 (Approach produces and error on opening the 30th) #> Maximum of 50 joins limitation There are no plans by Lotus/IBM to increase the number of joins allowed in a single Approach application file, which is currently 50. But I have rarely found that to be a problem in my applications. Here is my "approach" to a complex application with many potential databases being involved (per Sue Sloan): 1) I separate a complex application into multiple Approach files based on the "functions" of the application. For example, I might have a Customer.apr for customer record maintenance, an Invoices.apr for invoicing activities, a PO.apr for purchase orders and a Reports.apr for reporting. This functional split is logical in that most business processes can be divided up this way. And this keeps things simple for maintenance purposes because I can work on one .apr while the users continue to use the others, if necessary. 2) I try to stay under 15 unique databases joined per .apr so that I can still use "SaveAs" to save the application .apr file and databases to a new location. I do not worry so much about the total number of joins (that includes aliases), but I do try to avoid going over 30. 3) I provide navigation between the .apr files with a central menu.apr and macro buttons. I use the OPEN command in a macro to switch between them. If the .apr is already open, the user will be switched to the open file at the place they left it. The user can also switch between .apr files using the "Window" menu at the top of the Approach window. 4) I use local "control" databases (one per user on their workstation) to pass values between the .apr files when necessary. I limit these to one record each by having a field that is defaulted to 'X' and validated as unique and filled in. 5) To avoid having to log in separately to each .apr file, I either use my own login method or have them log in using Team Security to the menu.apr. I record who is logged in using the "control" database record so I can mark "last updated by" fields in the databases. 6) I consolidate databases that are solely for lookup purposes into fewer databases. In them I use a "type" field to filter them for building drop-down lists. I can set a variable-type field to use as the filter for each different type in the database. Or I can access them from LotusScript even if they are not joined in the .apr and build the lists that way. This is not an exhaustive list of ways to make the 50-join limit irrelevant, but the ideas here may help others with similar projects. #> .oyz index files If you find a file with a .OYZ extension/type in the folder or directory where your databases are stored (.DBF extension/type) this indicates that the .ADX (SmartIndex) file for that database is corrupt. Approach has created the .OYZ file to allow access to the database but the results may not be reliable. In fact, since the .OYZ reverts the affected field names to their dBase default length and format, your scripts may not work at all! And you may be asked to "map fields" when opening your .APR file due to the field name changes. To get rid of this problem, you need to delete the .oyz AND then either repair or delete/rebuild the matching .adx file. IMPORTANT: BEFORE YOU DO ANYTHING, make sure you read the following two articles in the FAQ: 'Deleting and recreating index files (.adx)' which outlines this procedure and implications in more detail, and gives more details about .adx index files. 'Repairing corrupt index (.adx) files' which outlines a widely affirmed method of rebuilding a .adx without losing any information. However, there is no guarantee that this procedure will work in every situation. #> Files and File Types File types are: .dbf = a dBase database file containing fixed length fields .dbt = a supplementary dBase database file containing memo fields .adx = ontains Smartindexes and information file created by Approach See FAQ articles 'Repairing corrupt index (.adx) files' and 'Deleting and recreating index files (.adx)' .oyz = a replacement index and information file created by Approach if the .adx becomes corrupt. See article '.oyz index files' .apx = an information file created by Approach when using Paradox database files. See FAQ article '.apx files' .mdx = an index file created by other database applications. See FAQ article 'Using index files other than .adx (eg. mdx)' File named like "Save49c6.apr" or "Savede40.apr" are temporary files Approach creates when saving an apr. Approach normally deletes them when the save is complete. If Approach crashes during a save they can be left behind but it's rare to see them. They aren't .apr files that can be opened by Approach. Attempting to open them will produce an "Internal Error 12345678" error message. You can delete them quite safely, but remember that there presence may indicate that one of your real .apr's was corrupted when your computer hung or crashed part way through a file save. #> Maximum of 20 charts per .apr The following was submitted by CRAVD01@aol.com and describes a method of getting around the maximum of 20 charts per .apr Create a subdirectory called MENU. Under MENU create a database called MENU_1. The database consists of only one field named JOB. Close MENU_1. Create a database called CHARTS_1. The database consists of only one field named JOB. Close CHARTS_1. Open a third database called CHARTS_2. It also contains only one field named JOB. Close CHARTS_2. Now, open MENU_1 and join to CHARTS_1 and to CHARTS_2 on JOB. Then join to your main database file and then to your other database files. Save the Approach file and close. Now, re-open CHARTS_1 and create a worksheet, etc. which will generate the first set of 20 charts from the data in your main database. Now, re-open CHARTS_2 and create a worksheet which will generate 20 more charts from the data in your main database. Now, re-open MENU_1 and create macros to open CHARTS_1 and its associated charts and CHARTS_2 and its associated charts. If you design the MENU_1 menu correctly, the user will not realize that you are jumping from CHARTS_1 to CHARTS_2 to go to various charts. You can continue to add CHARTS_3, CHARTS_4, etc. for an endless number of charts. The following was submitted by CRAVD01@aol.com, and describes a method for getting around the The trick is to be sure that when you go from MENU_1 to CHARTS_1 that you create a form with menu choices that will guide the user to the correct chart. Make the button macros choose which file to go to. If MENU_1, CHARTS_1, CHARTS_2 are in the same subdirectory, when you create macro buttons, select the OPEN command and edit the location of the CHARTS_1.apr as follows: change C:\MENU\CHARTS_1.apr to CHARTS_1.apr. This allows Approach to operate correctly even if you change drives where the programs are saved. If you leave the C:\MENU\.... in place, if you save to D:\ drive and then try to use the macro buttons, the system will tell you that it cannot find the C:\MENU\.... file. ###> Optimizing databases and minimising the chance of problems #> Optimizing the speed of a database Having the latest version of Approach on a suitably fast system with plenty of RAM (many suggest 64 meg), fast disk access, a large permanent swap file (ie. if you are using MS-Windows 3.1*. If you are using MS-Windows 95+ either make sure you have a lot of spare disk space (say 100meg or more) or that your virtual memory is set to 2.5 times the size of your RAM. Also on MS-Windows 95+: setting your file system to "server" instead of "desktop" can help disk access times), and fast disk caching will of course increase the performance of any large database. Defraging your hard disk regularly will help the performance of all of your applications, including Approach database. Also, make sure you regularly compress the databases. You may also what to carefully consider your database design. A badly designed database will perform poorly in any application. For instance, the more joins you have, and the more joined databases shown on any one form, the slower your database will tend to be. One way to speed up a bit database is to split complex .APRs into 2 or 3 smaller less complex ones where each .apr opens fewer tables. Also make sure you don't have a lot of unused forms, reports, macros and scripts cluttering up your database. Databases work slower with 'full record locking' than with 'optimistic record locking'. Minimize your use of color and graphics as these drastically effect the time it takes to update the screen. In order to maintain the best ratio of speed and stability you may want to consider deleting the .adx files (indexes) on a regular basis (say every week or two). There are some possible complications with this so carefully read "Deleting and recreating index files" first. Saving your database using the FILE/SAVE AS menu command using the copy data option achieves much the same thing, although it means that everything get a new file name. Delete excessive windows .tmp files. If this is a regular problem you may want to include the following statement in your autoexec.bat file: "del c:\windows\temp\apr*.tmp" If you are using Approach on a LAN or WAN, also see 'Running Approach on a network' in this FAQ. Wherever possible, use "numeric" data type as compared to "text". Suppose you have a part number that is literally all numbers, containing no alphabet characters, and the number string is 10 digits long. This field could be set up as a "text" field of 10 characters in length, or a "numeric" field of 12.3 (12 digits to the left of the decimal point and 3 to the right of the decimal point) or 10.0 (10 digits to the left of the decimal point and none to the right of the decimal point) characters in length. All of these options will index and sort fine, but by setting up the field as a "numeric" field of 10.0 characters, the index .adx file will be smaller and Sorts and Finds will be faster. Whenever possible, join database files on numeric fields. If numeric field joins are not possible, join database files on text fields with short lengths. Why? Two reasons. The total number of joins in an application is limited by the total of the lengths of all of the joined fields. The maximum limit for the total of the lengths of all of the joined fields is 450 characters. [Where did this number 450 come from? Answer: personal communication from an Approach developer.] Joining fields with shorter lengths will allow more joined database files to be included in the application. The second reason is that fields that are joined are always indexed. The .adx index files get large and inefficient when the joined, indexed, text field is long (over 10-20 characters). This will slow down searches and screen updates. [I am not sure that these details are correct.] Joined fields in different database files must match in terms of data type (such as, numeric or text) and must match in terms of length (maximum number of characters) for optimal and reliable performance. If the length of the joined fields is different, then the joins themselves can become unreliable and can become cross-linked so that the Unjoin function on the join map does not work. Big gains in the speed of Finds can obtained by using SQL queries. (see article 'Using SQL in Approach'). Conversely, doing finds, joins, or sorts on calculated fields may substancially reduce performance. I have no idea why, but some people have reported significant gainsin speed by setting the default sort on the database files so the last record added appears first. In other words reverse the order completely. Conclusions: Design Approach applications for speed by: 1) joining as few databases as possible in an application, 2) using 'optimistic record locking', 3) compressing .dbf files regularly, 4) deleting apr*.tmp files, 5) joining on numeric fields, 6) index on fields with short fieldnames, 7) joining on fields with the same data type and length, 8) consider using SQL queries on big Finds, and avoid doing finds, joins or sorts on calculated fields. Stuart Tucker substantially contributed to the articles 'Optimizing the speed of a database' and 'Deleting and recreating index files (.adx)'. #> Compressing databases with a macro or script [UPDATED] 3 Mar 2007 First, read article 'Reducing ("Compressing") the size of a database file' in this FAQ. Since compression of database files (Dbase or .dbf type) may need to be done regularly it is a good idea to automate the procedure. You cannot do this with macros in Approach v2.01 and v3.0*, however you can achieve the same result with these versions by using "Recorder" that comes with MS-Windows (look in the main or accessories folders). The key strokes from v3.0* are: ALT T P TAB 5 which takes you to the compression and where you can select the files. (This paragraph was not reviewed by XpertSS.com because those older versions are no longer installed in our offices.) The rest of this articles applies to v96 and later: Here is a couple of examples: If you want to use a macro to compress a particular database then put the following into a macro: Up to v9.1: SendKeys ~%{F}UA{TAB 4}{RIGHT 4}{TAB}dbasename~{TAB 2}~ v9.5 and later versions: SendKeys ~%{F}UA{TAB 4}{RIGHT 3}{TAB}dbasename~{TAB 2}~ ... where dbasename is the name of the database, or at least the minimum number of characters of the name needed to uniquely identify it. The following script will compress one table (i.e. the first table in the list of databases): 'Up to v9.1: Sub CompressTables Sendkeys "~%{F}UA{TAB 4}{RIGHT 4}{TAB 4}~{TAB 2}~",True End Sub 'v9.5 and later versions: Sub CompressTables Sendkeys "~%{F}UA{TAB 4}{RIGHT 3}{TAB 4}~{TAB 2}~",True End Sub For every additional database the you want to compress, insert the one repetition of the following code into the script immediately BEFORE the {TAB 2)~: {TAB 7}{DOWN}{TAB 2}~ So, to compress four databases in v9.5 and later versions of Approach the script would look like: Sub CompressTables Sendkeys "~%{F}UA{TAB 4}{RIGHT 3}{TAB 4}~{TAB 7}{DOWN}{TAB 2}~{TAB 7}{DOWN}{TAB 2}~{TAB 7}{DOWN}{TAB 2}~{TAB 7}{DOWN}{TAB 2}~{TAB 2}~",True End Sub You can find out the number and order of the tables in your APR using FILE/APPROACH/FILE PROPERTIES menu item. If all this is a bit much, then you might want to consider purchasing DBFPack from http://www.XpertSS.com which does it all for you. #> Reducing ("Compressing") the size of a database file 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) #> Good practices to reduce that chances of encountering problems [UPDATED] 3 Mar 2007 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... 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. ###> Corrupt index (.adx) files #> .apx files .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. ###> Errors, lockups, crashes, and other problems #> General Protection Faults (GPF's) If you are running v3.00 then upgrade to v3.02. It's free, and it will save somebody posting to the list telling you to upgrade because v3.00 is full of unwanted little surprises! See 'Free Approach 3.02 Upgrade' If it might be something to do with printing then see 'Printer problems (including GPFs, HP Printers)' If it might be something to do with the display then see 'Display problems (including problems switching to a particular form)' Search the IBM's Lotus Approach support web site (http://www-3.ibm.com/software/lotus/support/approach/support.html) for the program involved in the error (eg krnl386.exe, or krnl386.exe module 0001:0ec9) and see what comes up. NOTE: search the Knowledge Base, which is a different thing than just doing a site search - they are different things. Despite what Lotus says, creating reports with more than 8 repeating panels will cause GPFs (still occurs in July 1997 update of Approach 97). So if you have more than 8 that may be your problem. A lot of GPFs are cleared up by repairing or deleting .adx files BUT make sure you see 'Repairing corrupt index (.adx) files' and 'Deleting and recreating index files (.adx)' first, otherwise you may find you create a few other problems as well... Some GPFs may be associated with corrupt .apr/.vew files (eg if it only happens when changing to that particular view). See 'Repairing corrupt .apr/.vew files'. If you get the GPF in Design mode, then try switching 'Show Data' off, especially if you have a lot of calculated fields. If this doesn't work you could try reinstalling Approach, especially if the fault has appeared just after installing some other new software which may have overwritten some .DLL's in the windows/system directory. If at first this doesn't work, try unistalling Approach first, and then reinstalling it from scratch. Otherwise try buying a new computer and then installing Approach ... ;-) just kidding! It might also be worth testing Approach while there is absolutely nothing else running, and if it works introduce other applications and utilities that you normally use to see if the conflict is particular to one of them. GPFs can sometimes be caused by having duplicate copies a DLL's in the Approach, WINDOWS or WINDOWS/SYSTEM directories. Fontmod.dll is a known culprit. If you have a duplicate .dll then Approach's version should be copied into the Windows directory. Other copies should be removed. However, be careful, different version of Approach or other applications may require different versions of that .dll. It is probably best to keep old copies in a temporary directory that is not in the search path (see the autoexec.bat file) until you are sure you don't need then. #> Repairing corrupt .apr/.vew files (e.g. errors when changing views) If your application suddenly becomes unstable while you are working in Design Mode on it, you may see crashes simply changing views, strange views with objects from several views mixed together on them, error messages, etc. At that point, you should NOT save the Approach file yet, if you are even able to do so. Consider the following advice from experience with many unfortunate situations I have encountered over the years. :>( If you have not made many changes since you last saved the Approach file, just close the APR file without saving it and open the prior copy to see if it is OK. Then recreate the changes. But if you have not saved your work and it would take considerable effort to recreate it, or the prior saved copy is similarly damaged, you may want to try to salvage the APR file. Here is how to proceed: * First, make sure you have a backup copy of the .APR file from the last time you saved it. You can use the Windows Explorer to copy that file to another folder for safekeeping and then try saving your current .APR file. Then reboot the computer. This may be all you need to do to relieve any shortages of memory or other system resources. * Open the APR file and see if it works correctly now. If not, try using the File menu, Save As option to another directory to make a new (hopefully error free) version of the .apr and database files. * If you still have problems, and you can get into design mode, try deleting bits of the last changes you made to see if the problem disappears. It would probably be best to start with things such as text blocks. Sometimes deleting entire views, like form letters in particular, will help. If you cannot get to a view without a crash, try turning off "Show Data" under the "View" menu while in design mode before switching to the view. If you reach a stable point, save the APR file and redo your changes from that point. Review your named Find/Sorts for missing conditions which can happen if you use "Query by box". Delete these and create new ones, and fix the macros that are impacted. * If you still haven't managed to fix it, try creating a new .apr with all the exact same databases and joins, then import the old APR into it. Note that scripts are not imported so you would need to copy those over manually. If all else fails, you probably have to simply recreate everything from scratch (using your data (eg .dbf) files of course). But you should never get to this point if you follow good development practices. In particular, you should never save an Approach file over itself without having a backup of it prior to that save. And it is a good practice to use SaveAs often giving the APR file a different name such as myapr_dev01, myapr_dev02, etc. Finally, consider the possibility that the problem or error you are seeing is caused by a corrupt database index. This can cause missing records in repeating panels, Finds that do not find the right records, crashes switching to a view of that database, etc. Try your current APR file with a backup copy of your databases to see if the problem goes away! If so, there are other FAQs on this site that can help you with database and index corruption if you suspect that is the problem. #> Out of Memory error message First, careful read through the article 'Optimizing the speed of a database' for tips on how to get you database running as sleek as possible. Also, remove anything that might be unnecessarily using memory, such as unused forms, worksheets, reports, macros that are no longer needed. Check the manuals for your operating system to make sure your system configuration is making optimum of your memory. For instance, if you are using MS-Windows 3.1 and DOS, check that your EMM386 (probably should be set to NOEMS), and Upper Memory Blocks (UMB) are properly configured and utilized. #> "Record is locked- retry -cancel" error message Sometimes a shared Approach database repeatedly returns a "record is locked- retry -cancel" error message, even though the database has been set to optimistic record locking. This can occur when the link to the network drive where the data and view files are held is down. In this situation you need to exit Approach, reestablish the link to the network drive (usually by rebooting your workstation), and reopening the database in Approach. It will also occur if a user does a search that causes a new index to be created. Everyone else will receive a "locked" message until the indexing is completed. And, of course, if you have a lot of users and they often attempt to work on the same records then they are going to frequently get record locking errors. It can also occur for a variety of other more obscure reasons. Some things that have been suggested to keep these at bay are: * delete excessive apr*.tmp files created by Approach. If this is a regular problem you may want to include the following statement in your autoexec.bat file: "del c:\windows\temp\apr*.tmp" * keep the users system clocks within a minute or so of each other. Locking can only work properly if all PCs are synchronized. For example: 10:00:00 User A locks a record 10:00:01 User B locks another record 9:59:59 User C (whose time is not synchronized) tries to access User A's record, which is not yet locked because it isn't 10:00:00 yet. Ooops. Get the network administrator to set up the network to synchronize the PCs clocks. * try compressing the database, and if all else has failed try deleting and recreating the .adx files. See articles 'Deleting and recreating index files (.adx)' and 'Repairing corrupt index (.adx) files'. * put a blank record as the first record in the database. This record must be devoid of any join relations. ( don't know what this does, but apparently it can help!) #> "Someone locked this record" errors This error generally means that either someone is updating a record, or if a smartindex is being created. If your a single user and this error suddenly starts appearing, there may be something wrong with the database. Try compressing it, if that does work, try deleting the index files (.adx), but make sure you read articles 'Repairing corrupt index (.adx) files' and 'Deleting and recreating index files (.adx)' first. Otherwise, it could be because the clocks in your server and workstation are not synchronized. This can cause Approach to rebuild the smartindexes more often than really necessary, making a clash more likely. Check this before doing anything else. In the end, the faster that a process can run the less likely a clash will occur. So you might want to consider speeding up your network. Processes such as finds, sorts, and reports can be initially sped up by running a comprehensive find on the database tables before users get access to them. This forces Approach to index the database files which speeds up processing. Do this immediately after creating or compressing database files. Other things you can consider are: * making the databases read-only to the users. * create and use a separate copy of the database to run reports #> "Unable to open ___.dbf file, 5009" errors Approach displays this error message upon opening an .APR file if: * The user has mapped the application's database folder to a different drive letter on their network server than the one mapped by the application developer. * The drive letter used by the developer is not mapped to anything on the user's computer. Example 1: A developer puts databases on H:\mydata and shares that folder on the network. User A opens the Windows Explorer and maps the shared "mydata" folder as "R". The user has no F drive assigned. On opening the .APR file, the -5009 error message occurs. Example 2: A developer is working on the server and puts databases into C:\mydata and shares that folder on the network. User A opens the Windows Explorer and maps the shared "mydata" folder as "G". On opening the .APR file, Approach looks for the folder on C and gives the user an opportunity to substitute databases. If the user is allowed to do this substitution and save the .APR file, the developer on the server will get a -5009 error the next time the .APR file is opened if no G drive exists on the server. It is important to an Approach developer whose application will be shared over a network to plan for this situation and insist that the users of the application all map the shared drive to the same letter. Since many computers today have CD drives and perhaps other drives, it is recommended that the shared drive letter be above "F" to avoid conflicts. If you can open the .APR file, go to the FILE / APPROACH FILE PROPERTIES menu and you will see a list of all the files associated with the application and the complete path to them. For dBase databases, only each .dbf file is listed, but the .adx and .dbt files must be in the same folder as their .dbf file. #> "Macro error: Import table has changed" error You have a macro with an Import command that has the incoming file and field mapping predefined in the macro. Suddenly you get a message "Macro error: Import table has changed". In general, the error is telling you that there is no longer a match between the file to import and the target file being imported into. What are the possible reasons for this problem? 1) You changed the definition of a real field or added/deleted a field in the database you are importing into. (This does not include variable or calculated type fields, which are not in any database) 2) You changed the content/format of the file you are importing. 3) You have a corrupt database index (.adx file) on the database you are importing into. (People have reported getting this error and discovering that all their field names have reverted to upper case and have been truncated.) If your situation is caused by a change in the database or incoming file format/definition, you can correct the macro by editing the settings in the Import command. If your situation is caused by a corrupt index file, there are two other articles in the FAQ that can help you: * 'Deleting and recreating index files (.adx)' which outlines this procedure and implications in more detail, and gives more details about .adx index files. * 'Repairing corrupt index (.adx) files' which outlines a widely affirmed method of rebuilding a .adx without loosing any information. However, there is no guarantee that this procedure will work in every situation. #> Internal Error: Cannot load string 0 This error is generated when more than 16 users try to access the same .apr at the same time in v3.0* (v95...?). In order to get around this, duplicate the .apr, and divided your users across the new .apr/s. All the .apr/s can use the same database files. #> "Could not load infobox dynalink" error This may be caused by not having done a network install of Approach on the desktop you are using to link it to the Approach executable on the network. To fix this, reinstall Approach on the desktop, making sure that you do a network install. (also see 'Running Approach on a network' in this FAQ) #> InfoBox, Tools Palette, etc... won't appear This may be because the InfoBox has been moved off the edge of the screen. Carefully have a lot around to see if you can find it. If you can't find it then you may need to manually edit the location of the InfoBox. In v3.0* the infobox location is stored in the APPROACH.INI file in your Windows directory. Set the location to 10,10 then restart Windows. In v96 and later the location is stored in the Windows Registry. This must be edited very carefully. If this does work the you probably need to reinstall Approach. #> "Page Fault... at 0137:0049eb64" in Approach 96 To avoid getting "APPROACH caused an invalid page fault in module APPROACH.EXE at 0137:0049eb64" errors when entering new records in Approach 96, turn off the "In Field" option. #> Approach 3.02 crashing in design mode and when switching views A different times people have reported that this is due to the fontmod.dll file in the Windows System directory being replaced by a new installation of some other program (e.g. Quickbooks). To fix this just replace the new copy of the fontmod.dll file in the Windows System directory with Approach's version found in the Approach directory. #> "An error has occured in your program" errors If you get the following error: "An error has occurred in your program. To keep working anyway, click Ignore and save your work in a new file. To quit this program, click Close. You will lose information you entered since your last Save." ... just follow the instructions. Apparently if you hit Ignore enough times ("A couple of hundred" according to one person?!) Approach generally gets the message and continues on. Just save your work, exit approach, do a cold boot (i.e. turn your computer off and turn it back on), then recommence your work. Often that is all that is required. #> Database being used exclusively error One possibility is to go to the FILE/OPEN dialogue box, select the dBase (.dbf) file format, then click on the SETUP button. You can then set the options regarding database sharing on the network. Make sure you have 'share' or 'vshare' installed. Your autoexec.bat should have a line that looks like: C:\DOS\SHARE /F:8192 OR, the [386Enh] section of your system.ini should contain: device=vshare.386 or device=*vshare If you have a file named APR.V30 in your Approach directory, rename it to something else. It tells Approach that it is a stand-alone version and that it should open all databases exclusively regardless of other settings. If you have a "sdBaseFileSharingMethod=Approach." in your approach.ini file, try replacing it with with "idBaseOptApproach=1". You do have to have a line in your approach.ini that reads "sdBaseFileSharingMethod=DBASE4" or "sdBaseFileSharingMethod=DBASE3" or some other file format. Make sure that all users have this parameter set the same. Of course if all else fails, you can always revert to deleting every mention of Approach on your computer/s and re-install from scratch... #> "Internal Error - Cannot load string - 1035." error message Make sure you are running 3.02 and not 3.0 as this error is related to one of the main bugs in 3.0 to do with the Spell Checker If you are using 3.02 you possibly have a corrupt .dbt file which contains your mem/picture plus fields. If you can, delete the offending .dbt file and restore from back up. Otherwise, their are other solutions available by searching the lotus support knowledgebase at http://www-3.ibm.com/software/lotus/support/approach/support.html #> "GPF in APPREXPR.DLL at 0009:0D2C." error message This is an Approach v3.0* error message indicating a corrupt index (.adx file). Try compressing the database first, otherwise you will need to delete the .adx files. You should probably also check to see if you have any .oyz files and get rid of those as well. NOTE: read articles 'Deleting and recreating index files (.adx)' and 'Repairing corrupt index (.adx) files' before deleting the .adx or .oyz files! For compressing, see article 'Reducing ("Compressing") the size of a database file' #> Vanishing scripts This article was submitted by Paul Bent (Northwind IT Systems): The script editor may become unstable while you're developing and testing code, particularly if its been open when you've been testing forms as dialog and/or stepping through with break points. I've seen very strange things happen including disappearing scripts. This is my bomb proof method for not losing work!! I do these three things every time I make code changes and BEFORE I test them. 1. Press F2 to test compile global code. 2. Press Shift + F2 to test compile object event code. In A9.5, if Shift + F2 does not display a no errors message then close Approach immediately, don't try and save changes. After Approach has closed an Approach message box often appears!! - "Error 160, Unknown class of product object" - OK it and reboot! You see this one quite often if you run macros from scripts and vica versa. 3. Save As to an incremental file name (never save the apr over itself), close and reopen the apr. I run a full unattended backup of my server each night. In the morning I delete all the incremental versions except the last. #> "Could not find database [path]\name.mdb" error An error message "Could not find database [path]\name.mdb" is sometimes encountered when opening 32 bit ODBC data sources. This can happen with the second and subsequent mdb's you try to open. The solution is to select file type ODBC Data Sources each time, followed by the MS Access Driver folder. This will initiate a new "select mdb" procedure and bypass the bug that causes the "could not find..." error. #> "couldn't open database because it is open in another language..." This is a network problem that occurs if different computers accessing your database files on the network have different languages set in the "Regional Settings" section of the Control Panel (accessed from Settings on the Start Menu). If the language is set correctly on all the computers, try changing it so something else, exiting Regional Settings, then, going back in and resetting it back to what you want. Also check the currency and date settings are all the same while you are there, because these can also cause problems. #> Globals Sub "Initialize" doesn't always get executed The has been some disagreement on the list about this. Some people swear it works perfectly all the time, others seemingly have frequent problems. Whatever, here is a summary of what has been said: Paul Bent writes: Initialize should execute when the apr is opened. Say you want to call a logon procedure, check the registry, call API functions to get system parameters (eg short date format) initialize variables etc etc. Not much use if this only happens when the user first clicks a button (say); they could have been working in the apr for any length of time already. It isn't a good idea to put Initialize code in DocumentOpened or OpenWindow events or it will execute every time these events fire instead of just once when the apr opens. To avoid this you need to hard code the apr name in the event script which will cause it to fail if the apr is renamed. However, putting a single comment character in OpenWindow works around the Initialize problem thank goodness! Conversely, David Legge writes: The initialize sub will only run when (just before) the first bit of code gets invoked. I suppose there is no need to run it if no other code ever gets run! If you want a code to run when a doc is opened, (the script equivalent of the Open macro) then place it in the openwindow event of the Documentwindow object in your file. (BTW If you have anything in Sub initialize it will also then run - because the Openwindow event is triggered) The Sub Initialize is also run if you have edited code in the IDE (again only when the next piece of your code is invoked) So placing "documentopen" initialisation in Sub Initialize can confuse you when editing your code. You will find Documentwindow is the last item in your list of views in the Object: list in the IDE. You have to expand yourfile object above the Approach objects to see it. Unlike Paul, I think Initialize is now working correctly! ###> Printing #> Printing formatting problems Also see 'Printer Problems', 'MS-Windows 95 printing blank pages (v2 & v3)', and 'Header / Footer problems in v2.1' in this FAQ If you are missing edges of pages, first check the printer setup in the printers section of the MS-Windows Control Panel. Also, in design check that all of your page boundaries are set correctly. Approach will not use the resident fonts in the printer. #> Printer problems (including GPFs, HP Printers) A simple thing to try is to select the default printer in the printer setup (evening if you are currently using it), and then back to whatever set up you want. This ensures that the current set up is correct. Then, download the latest drivers for your printer. Search your disk drives and remove all old versions of the printer driver before installing the new one. If you are using Approach on a network make sure all the drivers have been removed from the workstations and install the new driver on the server for network printers. If this doesn't fix the problems, search the Lotus technotes for tips and solutions concerning your printer. If that doesn't help, contact the technical support people for your printer to see what they have to offer. Searching for their web site is a good place to start as they probably have FAQs and the latest drivers there. Note: You may get differently results from different versions of Approach at the same time, so for consistency its best to stick to using one version. And just in case your wondering, for everybody that says that Approach doesn't work well with HP printers there is somebody else that says they use a variety of HP printers and never have a problem... Go figure! However, if you are having problems here are some work arounds that have been suggested: * if you are using MS-Windows 3.1 try updating to MS-Windows 3.11 (or later), and make sure you are using the latest drivers. This seems to solve some problems. Similarly with MS-DOS, if you are not using MS-DOS 6.22 then perhaps you should try it (with the latest drivers of course). * if you design a report for a 300dpi printer and then later switch to a 600 dpi you will probably get clipped text. One suggestion is to use a macro to set the printer to 300dpi and then print the report. Otherwise you could simply recreate the report for the 600dpi printer. * you could also try using current drivers for older models. Some suggestions are: if you are using a HP4, try using a HP3 driver; if you are using a HP Laserjet 2100M try using a HP Series II driver from the Windows CD. You never know, it just might work!? #> Header / Footer problems in v2.1 People have experience problems where the top or bottom half of the footer or header line is cut off in v2.1. The easiest way to get rid of this type of problem is to recreate the report from scratch - this is likely to be quicker and easier than spending to much time playing with your current one. 1) set the printer setup to the correct values 2) create a very simple report using all the default values. Just specify a couple of fields from the database the report is based on. 3) immediately insert and set up the footer and header as required. Do a couple of test prints to make sure it is right. 4) finally, set up the report contents the way you want it - insert further fields, do formatting etc... #> MS-Windows 95 printing blank pages (v2 & v3) To stop Approach from printing out a blank page after every printed page in Windows 95, make the following change to your WIN.INI file: Under the [Compatibility] heading change 'Approach=0x0004' ... by putting a ';' in front of it so that it reads ';Approach=0x0004'. Then save and reboot. A side effect of this is that it effects the printing of field definitions and macros. Lotus recommends using either a Win 95 PostScript driver, or an older Win 3.x driver. Look for Document#132676 on the Lotus web site. http://www.lotus.com NOTE: The problem will recur if you reinstall Approach or over write the WIN.INI file with an older version. If this happens then repeat the process #> Printing field definitions Just choose the print option on the Field Definitions screen. However, this won't work if you have the "Approach=0x0004" line commented out in your win.ini file (to prevent v3.02 on Windows 95 printing an extra blank page). To get it to work you will need to remove the semicolon and then reboot for the change to take effect. #> Printer fonts problems Approach will not use the resident fonts in your printer. Having more than 200 fonts installed on your computer can cause invalid font substitutions in Approach. #> Pages sizes in v9.6 v9.6 does not pick up the paper size from the printer. All views change to Letter size unless another size is selected in Page Setup and the file is saved in v9.6. Furthermore, new and duplicated views are default to Letter size. ###> Dates and times #> Calculating elapsed time Time is stored as a number starting at midnight, incrementing in 100'ths of a second. So as long as your time does not start on one day and end on another, you can simply subtract the timeout from the timein and divide by 360000 to get minutes. (100 * 60 * 60) = 360000 The following formulas have been suggested to calculated elapsed time over the 00:00 boundary: Either: ((Trunc(FinishDate) + (Trunc(FinishTime) / 8640000)) - (Trunc(StartDate) + (Trunc(StartTime) / 8640000))) * 24 Or: ((24 * (Date_expire) + time_exp))-((24 * (Date_ent) + time_ent)) #> Calculating ages from birth dates The following formula produces a line of text e.g. "Age: 32 yrs 6 mths". If the PEOPLE.BIRTHDATE is empty them it produces an empty text string i.e.."" If(Isblank(PEOPLE.BIRTHDATE), '', If((Month(Today()) - Month(PEOPLE.BIRTHDATE)) < 0, Combine('Age: ', (Year(Today()) - Year(PEOPLE.BIRTHDATE) - 1), ' yrs ', (12 + Month(Today()) - Month(PEOPLE.BIRTHDATE)), ' mths'), Combine('Age: ', (Year(Today()) - Year(PEOPLE.BIRTHDATE)), ' yrs ', (Month(Today()) - Month(PEOPLE.BIRTHDATE)), ' mths'))) The next formula just returns the ages in years and increments on the exact birthday: If(Month(Today()) < Month(PEOPLE.BIRTHDATE) or Month(Today()) = Month(PEOPLE.BIRTHDATE) and Day(Today()) < Day(PEOPLE.BIRTHDATE), Year(Today()) - Year(PEOPLE.BIRTHDATE) - 1, Year(Today()) - Year(DOB)) The last forumla gives you the persons exact age in days. You can then roughly calculate how many years and months, but since months and years are different lengths the answer is an approximation, give or take a few days: If(isblank(PEOPLE.BIRTHDATE),'',Trunc(((tod