In association with the Australian Open Access User Group
Last update 19 Dec 2000
Copyright 2000: This document may be copied/mirrored provided it is
not changed in any way.
Notice: Users should not act solely on the basis of the material
contained in this document. Items are presented as possible solutions to
problems but do not constitute advice. In particular, no assurance can
be given that the possible solutions will work in every situation or that
loss of data will not occur. Always back-up data before trying to rectify
a problem.
First produced by Michael Paine , July 1991. Web version prepared on
9 March 1998.
Open Access provides extremely powerful functions for managing information. One of the dilemmas of such power is that it often can seem overwhelming to new users. In particular, the appearance of the screen and the way in which items are selected from menus may seem very different from other popular software. However, the modules in Open Access (database, spreadsheet, word processor and communications) have a common use of menus and function keys so that once you become familiar with one module then the others will fall into place.
One concept which you should appreciate is that of screen forms. Where several items of data or options are available for a function, such as printing a document, a "form" is displayed on the screen. The form contains all data relevant to that option and the user may alter the data. With a form, you press [enter] to accept an entry and move between items and you normally press [do:F10] to accept the entire form and proceed with the function. If there is only one item in a form (eg a filename for "browsing") then [enter] works the same as [do:F10]. (What fool decided to make the [enter] key the form acceptance key in Windows? - generations of typists have used [enter] for "new line"!)
The [help:F1] key will always provide you with advice about the item
displayed on the screen - don't hesitate to use it.
Top of page
In the "bad old days" of DOS programs, each DOS application had to fully organise its own printing. A printer "driver" was required for every type of printer that was used. In this regard one of the great features of Open Access is that once a printer driver is defined then it can be used by all of the functions - Spreadsheet, Database, Word Processor, Notepad etc. In effect Open Access was one of the first "common user interface" packages - offering common screen, keyboard and printing features for several types of applications which run under DOS. "Windows", of course, does a similar job but uses a "graphical user interface" (GUI) - meaning it supports pretty pictures and a mouse but it still has DOS working (and crashing) in the background.
To print from Open Access you must have an appropriate printer driver defined under Utilities/Configuration/Printer_parameters (that is a series of menu selections, starting from the OA Main Options Menu). Fortunately most popular printers are already defined in the list of printers that is automatically installed with Open Access. Most users will want to modify these to achieve custom features, such as different size fonts and margins and landscape or portrait (note that several definitions can be used for the one printer - just select the appropriate definition for the job from the list of printers that OA displays). Customising a printer definition involves tackling "the 64 impossible questions" - see the Utilities Manual for details but some tips are set out below. In addition, I have collected together some popular printer configurations: Down load OA-PRT.ZIP - a collection of extra printer drivers which includes Postscript line draw sequences prepared by Terry Litchfield. This file is a .zip file and the size is about 30 Kbytes. See the Utilities Manual for the method of copying printer definitions from one file to another (it involves using the [copy:F5] key).
Form feed
The default configuration for OA printer definition is that the "form feed sequence" is blank and the program inserts numerous "line feed" characters to get to the end of each page. This can cause page creep on continuous stationery and other problems. The solution is to enter the form feed character (12) in the "form feed sequence" for the printer - this causes the printer execute a form feed (note that the printer must have the correct setting for the paper size - this might need some coding in the "initialisation sequence").
Printing to file
Open Access can print any output "to file". Select FILE from the list of printers and you will be asked to enter a DOS file name for the output. Printing to file is a great way to transfer data to other applications. For example, in Database, a print form can be created which automatically creates a comma-separated-variable file that can be used by Word for Windows in a mail merge. I have even create a print form to automatically create web page HTML from an OA database. See this vehicle crash test list for an example. The PMK file and a sample of the database are in WEB_PAGE.ZIP (see its README.TXT for instructions).
The default FILE definition might need to be modified to achieve the effects you want - such as the form feed issue mentioned above. Also the page width, height and printable length may need to be increased to avoid "page feeds" in the middle of the output. Maximum values are 455 inches for each.
Line drawing
The boxes and lines which are drawn on the screen are not always reproduced by the printer. These lines are part of an IBM extended character set. Some printers require a special command to be able to recognise and print them (with HP printers the command is (27)10U in the "initialisation sequence").
If the printer is unable to print the IBM character set then you may need to specify alternative characters in the "Translatable Character" section of the printer definition. For example the character + | = and - can be used to draw crude lines (see the manual for details).
Printing under Windows 3.x or 95
Like other DOS programs, Open Access cannot use the printer drivers available under the Windows operating system. OA still needs its own printer definition for every printer that will be used - just as though it was working in DOS. Windows can, however, be used to "capture" the output from OA and other DOS programs (see Windows help on "printers capture" - good luck!). This has the advantage that it queues all print jobs and it avoids OA output clashing with other print jobs. It also facilitates use of network printers. If you have problems one tip is to try setting the printer port to LPT1.DOS instead of LPT1 within the Windows configuration. Another is to include the end-of-transmission character (4) in the "finalisation sequence" of the OA printer definition, if jobs do not print straight away. See WIN95 tips for more information about printing under WIN95.
Warning: Many "budget" laser printers on the market only work with
Windows programs - they cannot be used with DOS programs such Open Access.
Top of page
A collection of tips for setting up OA networks is contained in OANET.TXT. This is essential reading for anyone running OA on a network, as is the OA manual. Some extra tips are given below.
Network Speed - Record Locking
OA User Phil Thompson has described some experiments he has conducted with processing time on a Novell network. The problem was that a routine maintenance operation on a database table was taking a long time over the network but was fast on a standalone PC. He found a reference to record locking on an obscure page in the OA manual and tried the tip - it worked! The tip is that if you are processing many records in a view then apply a private lock on the view. This means that OA does not have to lock, process and unlock individual records as it works through the view. A disadvantage with private locks is that other users cannot access the records but if you are doing maintenance then generally you do want others changing those records.
To lock a view in database (network version), retrieve your records
then select LOCK from the Database Operations Menu.
Select PRIVATE LOCK - you can then UPDATE the records.
To lock a view in Programmer/Compiler create & USE the view then
LOCK viewname PRIVATE
Network temporary files
Open Access network creates "&" files for temporary processing.
If unusual things are happening such as locked screens or printers not
working, try exiting to DOS (all OA users) and deleting all files starting
with & in any directories included in the OA Searching Order for every
PC using OA on the network. Xtree is handy for doing this on the local
PC and fileserver. Just set the file specification to &*.* and press
"G" for global display. This displays all "&" files.
Top of page
Relational Databases - Queries
It is often useful to be able to link the information contained in databases. For example, you might have a system which keeps track of purchases by customers but you wouldn't want the customer's address and phone number to be keyed-in and stored with each purchase record. Instead, a customer database is created which has a unique identification number and the purchase database simply records the identification number. In this way, if a customer's details change you don't have to alter every purchase record for that customer.
Open Access has such a relational database facility. Up to 16 database files can be linked by fields containing common data. This is best achieved by defining 'must match' fields when creating a database. The only rule is that the database to be linked must exist before you create the "must-match" fields in the main database.
For example, say you want to create a personnel database which includes a field CLASS for an abbreviation of job classification. The best approach is to first create a database called, say, JOBCLASS with abbreviations, full title of classification and (maybe) salary. Now, when the main personnel database is created, you can define the CLASS field to be a 'must match' with the abbreviations in the JOBCLASS file. You can even display the full title in your main file screen form by defining an External field which is linked to the must-match field. This is displayed but not stored as variable data.
The link between databases is temporarily established when data is retrieved or a print form is used. In both cases a Structured Query Language (SQL) is created to define the links. The SQL for the personnel example above might look like this:
FROM PERS,JOBCLASS Files to be selected
SELECT NAME,CLASS,TITLE,SALARY Fields to be selected
WHERE PERS.CLASS = JOBCLASS.CLASS The link conditions
AND SALARY>30000 An extra condition
ORDER -SALARY The boss comes first (negative order)!
The manual has numerous examples of SQL queries. Some particularly useful ones are:
Screen Forms
Screen forms are a way of looking at the database. A screen form with all fields is defined when a database is first created but any number of screen forms can then be defined. For example, you might want a screen form that allows users to browse a file but not change essential fields. You can set these to 'skip' fields in the screen form (select DESIGN SCREEN_FORM and use the original screen form as a template for the new one).
You can also hide some fields by not including them in a screen form. Another use of a screen form is to automatically change fields when a record is selected from the Browse list. For example you might have a database with a true/false field called FLAG. You can define a screen form which sets flag to 'true' (by defining FLAG as a dependent field set to 'true'). An UPDATE date field could also be set to the sysdate by the same method.
Screen forms can have several pages. You might consider making one a
special help page with advice about completing the fields on the form.
You must, however, have a field on this page or it won't be accessible.
It is a good idea to have a FLAG true/false field in every major database
to assist with complex processing. FLAG could then be made a field on the
help page to make the page accessible.
Top of page
Print Forms
The Open Access print form is primarily set up to quickly produce summary listings of records in a database, sorted and grouped by key fields. Several files can be linked for the purpose of producing a report - the SQL establishes the link conditions (as in the above "personnel" example).
The Design of new Print_form starts with the SQL. Once the SQL is defined the Header of the print form is displayed. The Header appears at the top of every page - it may be blank. Move the cursor to the default title and press [change:F6]. An edit window appears and you can change the text - make sure that text is enclosed in quotes otherwise it will be treated as a variable.
Press [F7] (in OA3/4)to get to the Group_Header. This is data that may appear at the start of each group (assuming the SQL contains an ORDER clause to sort the records and the "levelbreaks" have been set - see below). Next is the Record section. As a default this contains the record field names and field data (note that a message will be displayed if not all of the field information can readily fit on the Record page. Don't worry, missed fields can be added using the [select:F9] key). Use [select:F9] to highlight existing data and text move it around, including to other sections of the screen form.
The Sub-totals section of the Print_form may be used to print summary information at the end of each group. For example you could SUM(SALARY), COUNT(PAYROLL) etc. Note that numeric fields must be used for these functions. Complex statistics can be obtained by assigning temporary variables, for example.:
To create a temporary variable simply start the expression with "variable name": - note the use of double quotes and a colon. Caution: temporary variables seem to be integers so you could lose decimal place information.
Note that you cannot use the statistical functions within equations
Levelbreaks in print forms
The usage of the group header and sub-totals sections is defined by [menu:F2] Levelbreak. Each of the fields specified in the ORDER clause of the SQL may be used to print group headers and sub-totals. For more flexibilty the group header and sub-totals sections may contain logic statements of the form:
LEVELBREAK='CLASS'|'Classification:'+TITLE|''
Note the use of 2 single quotes at the end, otherwise a zero would be printed.
New page on Levelbreak
Levelbreaks can be used to generate a new page when a sorted field changes. Select [menu:F2] Miscellaneous and set "New page after totals" to TRUE. Then select [menu:F2] Levelbreak and make sure that the field that you want the new page to occur with is set to TOTALS or BOTH. Note that every levelbreak item which is set to either TOTALS or BOTH will produce a new page so the sub-totals section should not contain data for other fields (it may however be blank).
I have tried tricking the software by introducing a conditonal page feed but it produces unpredictable results (OA loses track of where it is on the page). For the record, the trick was to set "Translatable characters" in the applicable printer driver to the form feed, carriage return combination. I used (254) translated to (12)(13). Then in the print form sub-totals section include an entity such as: LEVELBREAK='CLASS'|'(254)'|'' (use Alt-F4 [paste:F7] to insert character (254) ). When OA encounters the (254) character is sends the (12)(13) combination to the printer instead.
Using the full page length in a print form
You may sometimes find that there are not enough lines in the record
section to print all that you require for each record. (with OA3/4 there
are, however, 3 "screens" of 20 lines each for the record section. OA2
only had 1 screen). In this case the group header and sub-totals section
can be utilised. Use the Levelbreak feature to print the group header and/or
sub-totals sections for every record and place the extra fields/text in
these sections of the print form. You will probably also need to adjust
some of the settings under [menu:F2] Miscellaneous. If this fails you might
have to resort to the Programmer language to print customized reports.
Top of page
Cross-tabulations
The crosstabulation features of the Database Module are a powerful way to summarize information - don't overlook the use of Table_forms when considering output. The table form produces cross-tabulations of one key field against another - the contents of the resulting cells are based on a numeric field and can be COUNT, SUM, MAX etc. The table form is created using the DESIGN TABLE_FORM selection from the database menu. The cross-tabulation is generated by retrieving records based on the query associated with the table form (QUERY STORED_QUERY) and selecting [menu:F2] TABLE from the Database Operations Menu.
The only major failing of the Table_form seems to be that its output is not readily transferrable to the Spreadsheet Module for further processing and graphics. The best way seems to be to print the crosstabulation to FILE and use the Data_convert Utility to create a spreadsheet. Unfortunately this rarely gets the text/numeric conversions right and some tedious manual corrections are usually necessary.
One trick is to print the table to file and use the file converter ([desk:F8] File_converter/ Text/Columns) to convert the resulting ASCII file directly to database records (which can then be integrated to a spreadsheet - phew!). A stumbling block is that the table is normally printed with numerous vertical and horizontal bars and dashes ---- where a cell is "blank". These odd characters must be deleted before the conversion is run.
To overcome this problem create a new print-to-file definition in your OA printer list. This should be a copy of the existing FILE definition but change the column width to 255 and define translatable characters to convert the horizontal and vertical bars (ASCII 179, 196 & 197) to space (ASCII 32) and the dash (ASCII 45) to 0 (zero).
Now use this new print-to-file definition when you print your database cross-tabulation and it will be ready for instant conversion using the File_converter.
Please refer to the manual for advice about generating cross-tabulations (Database pg 6-191) & converting ASCII files (Utilities pg 9-242).
Note that the *.TDF and *.TDI files can be renamed as DF and IF files respectively and the data can be viewed but some labels will be missing.
OA3/4 Database Improvements
(Written in 1991 but there is still some users of OA2 around)
With Open Access 3 the allowable number of key fields has been increased. Sorting is now possible on non-key fields and time format fields are available.
Field attributes now allow CONTINGENT FIELDS. These have a default value (similar to a dependent field expression) which can be overwritten (ie a cross between dependent fields and duplicate fields).
Along with the trend in other databases, a MEMO FIELD can now be set in a database. There is no ability to retrieve records of the basis of memo field data but, on the plus side, you can easily include the memo text in a print form. Memo fields can also be manipulated by Programmer (very powerful functions are available).
Graphs can now be easily prepared from retrieved records (Database Query). There is a [menu:F2] GRAPH selection in the Database Operations Menu and it works like a cross between the Database Table function and the Spreadsheet Graph function. A Graph function is also available from the Desk Accessories but the function.
Retrieved data can be sent straight to a spreadsheet or word processor document using the [menu:F2] Integrate command of the Database Operations Menu. When this is done you automatically leave the Database module and enter the selected module.
A "Resize" function is now available from the Utilities Menu. This is
primarily used to rebuild a database after records have been deleted (until
then, deleted records are just flagged in the the database). This reduces
the size of the files on disk and speeds up retrievals. Apparently, the
resize function also picks up record errors which are not detected by "Verify"
so it is advisable to use "Resize" instead of "Fix_file". Large files which
are frequently modified should be regularly "re-sized" (and regularly backed-up,
of course).
Top of page
Named rows or columns can now be indexed to create look-up tables. For example you might have years 1980 to 1990 in column A and corresponding income in column B. The Name command can be used to name them YEAR and INCOME respectively and during the naming process INCOME can be indexed with YEAR. A valid expression for another cell might then be INCOME[1985] - this will return the income for 1985. This is much simpler than the +TABLE() function.
Windows can display a text graph which can now be automatically updated when data in the other window is changed dynamic graphs! Spreadsheet data can be integrated directly into the Database or Word Processor modules.
Statistics package
The statistics module is included with OA3/4 (it was optional with OA2).
This module provides some extremely powerful functions for analysing tables
of data in the spreadsheet. The results of functions such as polynominal
regressions are placed in the spreadsheet where they can be graphed. The
Statistics module is accessed by pressing New users often complain about the margins and other features which
are preset when you first load the word processor. Auto-indent, justification
and "background" paragraphs are typical sources of complaint. They can
all be changed to suit your needs. If you do change the settings and want
to use them as a default then SAVE the blank document as "default.doc"
(you will be prompted to overwrite the exisitng "default.doc"). In fact,
this facilty works the same as "style sheets" or "glossaries" in other
word processing software. You could, for example, set up a facsimile coversheet
with headings and linework and save it as "fax.doc". Then you can use it
as a template for quickly producing facsimilies, by nominating it as your
"template file" in the word processor SET_UP menu (OA2 is slightly different
but has the same facility).
Printing documents
When a document is ready to print press [print:F3] to display the print
set-up form. This provides many options associated with printouts, including
top and bottom margins , footers and headers (press [change:F6] to alter
or delete them), mail-merge options and the desired printer. Note that
page size and fonts (size of characters) are determined by the printer
which is selected with this form. This in turn is defined through the Utilities,
Configure, Printers sequence from the Main Options Menu. You can easily
create a custom printer definition by copying ([ctrl-Ins]) an existing
definition from the list and altering it to suit your needs. For example,
you could have a printer definition called EPSON LQ for high quality output
and change a copy (called, say EPSON DRAFT) to produce printout in draft
mode. This requires a knowledge of printer control characters but it is
well worth a little time setting up a variety of printer definitions to
suit a range of tasks.
Mail Merge
As with OA2.1 the print form for the Word Processor allows direct retrieval
of database records during "mail-merge". The big improvement is that stored
SQL queries can now be used (with OA2.1 you had to retype the query each
time). When the Query window is displayed during mail-merge just press
[menu:F2] to load a query which has been previously stored in a text file
(the Database module now also provides for queries stored as text files).
The query text file can be created using Notepad and/or [copy:F5] [paste:F7]
from database screen forms or print forms. In this way it is possible to
mail-merge records in ways which are not possible with conventional word
processors - for example your "query" could retrieve customers with balances
more than $1000 which are 14 days overdue and print "personalised" letters
to each of these customers (I wonder what it would say!).
Multiple open documents
With OA3/4 an "Active File List" can be created by selecting [menu:F2]
FILES during word processing. DOC and TXT files can be added to the list
using [INS]. Once a list has been created you can instantly swap files
by pressing [Alt-F10] or you can pick a file from the Active File List.
Unfortunately there is no provision for saving an Active File List.
Other OA3 improvements
Move and Copy are now achieved using the clipboard (ie [select:F9] then
[copy:F5] or [cut:Alt-F5] and [paste:F7]).
Multiple copies of printouts can be specified on the print setup form
(at last!)
Document conversion .DOC to .DCA
The The Open Access Programmer provides an extremely powerful way of managing
databases. It was obviously written by programmers who wanted to make program
development simple and efficient. Programs can be executed from within
the Programmer Module. A "trace pane" can be arranged to display the line
of the program currently being executed. Parts of a program can be selected
and executed (handy for isolating a bug). The program can be halted and
a list of active variables (the symbol table) displayed or printed. Programs
can be modular, in that other programs can be called up from within a program,
using the DO statement. This is particularly useful when used in conjunction
with the MENU command which presents a list of selections on the screen
and takes action according to the key(s) pressed.
Once the program has been written and debugged it can be built into
a "condensed file" (sounds like a contradiction in terms) which can then
be run from the Application Menu. The major advantage of a condensed file
is that it cannot be altered by users. See the section "Build the application"
of the programmer manual for more information about condensed files. A
further step is to use the compiler to generate faster applications which
can run outside the OA environment - see below.
Using text files with Programmer
Programmer (2.1 up) can also be used to manipulate text files. The INPUT
statement is used to open a text file and the READLINE function is used
to read each line of the file. A program could, for example, read a text
file line by line and convert the string to records in a database. In this
way conversions which are not supported by the OA data conversion function
can be achieved using Programmer. Programmer can also write to text files.
The FILES statement is used to open the file and the "OUT TO" function
is used to write strings to the file, line by line.
Context sensitive help in your applications
With OA3 there is the opportunity to write context-sensitive help text
at the same time that the program is written through use of the The OA Programmer manual has virtually no advice about the help facility
(the Compiler Manual is better). Any text file can be called up in a program
provided that the file name ends with .HLP. Each section of "help" must
start with a unique number of the format $HELP # 66666 and end with $HELP
END. For example your help file MYPROG.HLP might have the following lines:
The program lines to call up this help text (when the [help:F1] key
is pressed) might look like this:
:K_HELP: DO helpcall
See the HELPFILE and HELPNUMB commands in the OA3 manual for more information
about the "help" function.
You can even customise the inbuilt help by editing the relevant .HLP
file with the OA Word processor. For example you could add a few lines
to the "help" for queries to remind you of some of the extra functions
available, such as range checks:
(WHERE update IN("01-06-1990":"01-071990")).
To find the relevant "help" number you simply press the Not enough memory
These dreaded words appear when the "low memory" has been used up. Low
memory is used to store information about variables, views, print masks
etc. In effect it points to the location in high memory where the values
are contained. There is a fundamental (and very annoying) limit of 28K
to the low memory, regardless of RAM and extended RAM available. This means
that "memory conservation" techniques are often required in complex programs.
Variables, views and windows should be "ended" when not in use and the
number of variables should be "pre-allocated" (ie space reserved for the
anticipated number of variables, including the maximum number of database
fields open at any one time, so that the low memory does not become fragmented).
I encountered a very frustrating problem with "not enough memory" until
I realised that text (ie the program) in the Programmer work area actually
takes up low memory - by the kilobyte! This means that low memory problems
will sometimes occur when the program is loaded into the workarea and executed
(Alt-F10) but they might not occur if the workarea is empty (eg [menu:F2]
NEW) and the program is run using [menu:F2] PERFORM. Similarly, the problem
shouldn't occur with a condensed or compiled application.
OA3 Programmer Improvements
In addition to the "help" functions there are several other improvements
to the Programmer. Several new functions are available for VIEWS on "flat"
files (ie views that access only one database such as VIEW data = FROM
cust ). These are:
BROWSE data - to browse the whole file called up by the view, in the
same manner as the Database Browse function. The programmer function returns
with the current pointer set at the last record accessed during Browse
(assuming that record is also in the VIEW).
DB-GET(data), DB-INS(data), DB-DEL(data), DB-NEXT(data), DB-PUT(data)
and DB-PREV(data) work quickly on records in the flat VIEW. They are much
less cumbersome than INSERT, FIND data NEXT etc but they must be part of
an expression (eg b = DB_GET(data).
FIND data WHERE name='fred' now works much more quickly provided that
the file is ORDERed on the NAME field. A binary search is used instead
of a sequential search.
GRAPHS can be displayed during the running of the program. That should
make it more interesting for bored users!
Some Possible OA3 Programmer Bugs
Using Programmer in a Network - Ordering records in views
OA3.01 Programmer had a serious bug in the way it handled time fields
in databases. This was fixed with OA3.02.
OA2 Database files and screen forms are converted to OA3 format when
they are first encountered by OA3. A prompt warns you that the process
is irreversible. OA2 Spreadsheets are converted is a similar manner. There
is no File Utility to reverse the conversion ( everyone in your company
is "encouraged" to upgrade to OA3 - what clever marketing!). The irony
is that both OA2 and OA3 have dBase and Lotus123 conversion utilities so
you could convert an OA3 database to dBase and then import it into OA2.
This is probably better than using DIF conversion because key fields are
retained.
Converting printer drivers
The printer drivers from OA2 appear to work with OA3 but they can't
be edited so don't bother trying to rename your old OA2 INFOE.PRT file
to CONFIG.PRT (the name used by OA3). You can however copy the old drivers
into the OA3 printer file CONFIG.PRT - just press Compiling a program
When the compiler is invoked you are asked for the name of the main
program file and a filename for the compiled code (the latter name will
appear in you Application menu). The Compiler then works through your program
a reports any errors (mainly syntax, unterminated "IF" statements etc).
If, by some miracle, no errors are found a .COD file will be produced.
You then run LINKER from your Applications menu to link the code to you
applications file (APP.SPI). Network users should then make a back-up copy
of the APP.SPI file because, if the network crashes, the original APP.SPI
file could be corrupted. Note that standalone and network versions of the
compiler are available. Programs can be Compiled with either version but
the correct version must be used for setting up the system and linking
applications.
Dynamic views
A feature of Compiler which is not available under Programmer is the
ability to generate VIEW definitions from within the program. For example,
you could prompt the user for the name of a database and then open a "dynamic"
view on that database. In effect, you contruct a string which looks exactly
as if you had typed the Query. You will probably need to concatenate several
strings in order to achieve the desired results (see sample programs in
the appendix). One trap is that the value of all variables, including database
fields, must be converted to strings. Therefore you end up with odd looking
lines like:
VIEW data = 'FROM ' & filename & ' WHERE UPDATE < "' &
(note the imbedded quotes)
De-bugging compiler programs
Although "Compiler" checks the program as it compiles it doesn't have
"trace pane" and "symbol table" facilities so checking and debugging a
compiled program is more difficult than with Programmer. Also, with large
programs you will probably need to SEGMENT your program so that it runs
more efficiently (segment sizes of around 12K seem best - segment sizes
are displayed during compilation).
Variable types can be mixed (eg INT dnum = sysdate + 0) but you will
get an error message such as "value not compatible with variable type"
if some other type of error occurs earlier in the compilation. The value
error message should vanish when the other error is fixed!
Run-time applications
Compiled programs can, with a little effort, be run outside the Open
Access environment and they don't need to be licensed (the Compiler is
licensed, of course). To invoke a compiled program from DOS , assuming
that the correct files have been loaded onto the hard disk (see the Compiler
manual), just type CMP from DOS. A screen similar to the Options screen
of OA will appear and you can select your program from the Applications
menu.
A fantastic feature of Open Access is its ability to define the directories
that are available to users. Nicknames is used to avoid the need for complicated
paths including directories on network drives. (With the proliferation
of hard disk space and networks this feature would be useful on recent
"Windows" software!)
Searching order
DOS directories are fairly cumbersome. Open Access allows (requires!)
you to nominate the directories that you wish to use and to assign nicknames
to them. In the above example the DOS directory c:\oadata\general is assigned
the nickname GENERAL. Any valid DOS directory can be included in the searching
order. Even the DOS root directory can be included so that you can use
the OA Notepad to edit files such as AUTOEXEC.BAT.
Up to 20 directories can be permanently stored in the searching order
for OA3.
When setting the Searching_Order (Utilites, Configure) make sure that
you also select Miscellaneous from the Configuration menu and make SCRATCH
the default directory. You might also want to change some other defaults
such as the date format.
It is advisable to set up several directories for storing data otherwise
users will be faced with huge lists of files when using the A suggested directory structure is:
Any DOS type file names may be used. Think about identifying groups
of files with a common prefix e.g.: HRJ_OBS , HR_UNITS for Human Resource
files. Be sure to nominate the directory nickname when creating a file
(e.g. GENERAL:HR_JOBS) otherwise it will end up in the default directory.
Substituting directories in the Open Access Searching Order
Note this might not work on network drives, but you can achieve the
same effect using the MAP network command.
The DOS command SUBST can be used to substitute the name of a valid
directory for an imaginery DOS drive. The command takes the form
SUBST K: c:\oadata\general
where K: is a non-existent drive. (Note that if this drive is greater
than E: then you will need to include a line such as
You can use this command to vary the searching order in Open Access
without going to the Configuration menu. The steps to do this are (steps
1 to 3 can be achieved using the Notepad):
1.Create a DOS batch file for each of the substitutions. The files should
be of the form
echo off
Call this file, say PICKGEN.BAT. It must be located in your OA code
directory.
2.Create a menu file (say SUBST.MNU) which calls up each of the batch
files
"Substitution Menu" 25,3
3.Edit your APP.MNU file to refer to the substitution menu ...
SUBSTITUTION_MENU subst.mnu ...
4.Change your OA searching order to include the K: drive
Nickname Directory
Now, when you call the Applications Menu from the Options menu (or the
[desk:F8] Menu) you can, in turn, call the Substitution menu and then choose
the required directory. Note that your previous substitution is lost when
a new one is selected so caution is required if you are in the middle of
working with files from the previous directory.
When using Open Access don't forget to use the nickname assigned to
the K: drive ("K" in the above example) when you want to use a file from
the substituted directory.
You to assign a default to K: before you load Open Access - this could
be done in the AUTOEXEC.BAT file.
Using the Applications Menu to run batch files
Have you ever been in the middle of an Open Access session and realised
that you needed to format a floppy disk? How about executing other common
DOS commands such as "Backup" or "Dir"? These and other commands can be
easily called up from the Applications Menu of Open Access. What is more,
they are available from the [desk:F8] Menu anywhere within Open Access.
The Application Menu is generated from a text file called APP.MNU. This
file can be edited with Notepad to produce a wide range of functions. The
types a functions available include;
\ filename A DOS batch file contained in the OACODE directory
An APP.MNU file might look like this;
Notice that the first line contains the title and the position (X,Y)
of the Applications Menu on the screen. The remaining lines are the menu
items. Only the words on the left are displayed in the menu and they must
not contain spaces (note the use of underscores _ ). The text on the left
is the relevant command.
For example, the first menu item MY_PROGRAM will cause the execution
of the condensed Open Access program MY_PROG.CND (note that .CND files
do not appear in the Application Menu when it is accessed from the [desk:F8]
function). The next menu item executes the file DIRA.BAT which should be
contained in the same directory as the OA code.
DOS batch files called up in this way can, in turn, call up other software
(Open Access reduces to a "shell" of about 20K so it rarely gets in the
way). I have successfully loaded Word5, AMI (run-time Windows!), Laplink
and Xtree from the menu. When you quit the software you return to the same
point in Open Access provided that you change to the OA code directory
within the batch file. For example, the batch file DO_WORD.BAT might look
like this;
echo off
The last line is important because you must be in the Open Access code
directory when you return to Open Access.
The file to create a directory listing (DIRA.BAT) is
echo off
The floppy formatting file (FMTA.BAT) is
echo off
The above file is much safer to use than relying on users to remember
the DOS format command. Formatting options such as formatting 360K disks
in a 1.2Mb drive can be included in the batch file.
Finally, that essential but tedious task of backing up data files can
be achieved with the following type of batch file (BACKOA.BAT)
echo off
Creating custom OA configurations for different applications
A neat way of having a different OA configuration for each "application"
is to start OA using the following type of batch file:
See WIN95 tips
for advice about passing parameters when starting DOS applications.
Open Access allows you to export database or spreadsheet files into
a format that Microsoft Office can easily use.
Exporting Database Files
Microsoft Access can read and write to dBase III files. Open Access
allows you to export database files into this format. To do this:
Using the dBase File in MS Access
Within your Microsoft Access database you can import the dBase file
to create a table within the database, or attach the dBase table to your
database. If you attach the table to your database then each time the dBase
is recreated by exporting from Open Access, the data within your MS Access
database is refreshed. This means that you can set up reports and forms
in Access which refer to the dBase file created from Open Access. Whenever
this dBase file is recreated, MS Access will automatically refer to the
new data.
Importing A dBase File
To import a dBase file into MS Access:
Attaching a dBase File
From the database window,
Automating the Export
You can create a macro to export your database file as a .DBF file and
place this macro on either the main menu (MAIN.MNU) or a sub menu under
the Applications menu. The key strokes are simply:
NAME, eg. customer [RET]
Spreadsheet files are probably best exported to the Lotus format (WKS).
Only spreadsheet files in the FMD, the default format, can be exported
to Lotus. The compressed OA4 format CMP, cannot be exported using the Desk
functions.
To export an OA4 spreadsheet file:
The dBase file, created using Open Access - File Converter, can be referred
to as your data source for Mail Merge in Word.
To do this involves one extra step when setting up the Mail Merge Helper.
On choosing to open the data source, in the Mail Merge Helper, select the
type of file as a dBase III file. Then select the .DBF file and proceed
as usual. All the other steps in using Word's Mail Merge are the same.
Winword Mail Merge has a "Catalog" function. This creates output which
is similar to the Records section of an OA print mask. That is the records
are listed down the page, rather than one record per page with a "Form"
mail merge. In this way it is very easy to create, say, an attractive phone
list from CARDSPI.DF. The Winword mail merge dialog box has a Query button
which allows the selection of certain dBase records and also provides sorting
of records. Also the TAB dialog box allows "leaders" to be included with
tabbed spaces
Use of comma-separated-variable data
When exporting OA database records for use in Word for Windows you can
create a CSV file instead of a dBase file. The advantage is that it can
all be done within a print form which is printed "to file" rather than
by using a file convertor. Also the print form can have tables joined,
only certain fields can be selected and the records can be ordered. Tricks
to doing this are to delete the default contents of the print form, except
for the field names in the header section and the fields in the record
section. Put quotes around any text fields ( ' " ' + fieldname + ' " "
) and, under Miscellaneous, make sure that there is no extra line between
records. Also make sure you have enough width for printing the contents
of each field.
Word for Windows has a mail merge option for CSV files and automatically
picks up the field names from the first row of the data file.
The System Manager operating system, by Datapac allows DOS applications
such as Open Access to be run in a Unix-like environment. The system (and
applications) is run on a powerful server PC and numerous PCs can operate
as "terminals" off the server. Furthermore, each "terminal", and the server
console, can have several sessions running concurrently. A key advantage
of this system compared with, say, a Novell network, is that all types
of PCs can be utilised as terminals and they all share the powerful resources
of the server (for example, the server might have a 486 processor and a
large, fast hard disk).
Open Access on a Network
1998 Note: These tips may also apply to other Netbios networks. Not
all netbios emulations are the same - for example the WIN95 emulation does
not record lock properly (if anyone knows of a better emulation let us
know).
System Manager is able to emulate Netbios therefore the network version
of Open Access can be installed on the server and all users can share the
data and software. Of course, the standard version of Open Access is limited
to 4 concurrent users - if another user tries to load Open Access once
four users have already loaded it then they will not have "Database", "Spreadsheet"
or "Word Processor" appearing in their Main Options Menu. They will however,
be able to run compiled or condensed applications from the Applications
menu. This might be acceptable for some situations, otherwise you will
need to invest in a full network version of Open Access., or a runtime
kit.
Installing Open Access on a Network The Netbios (or Novell) protocol
must be present in order to install a network version of Open Access. With
System Manager, you must have the emulator NETBIOS.COM running when the
system boots up. This is achieved by adding the line
Locks on data
Open Access network version has a wide range of features for controlling
data in the network environment. Views on database records and areas of
spreadsheets can be "locked" while processing is undertaken. Other users
attempting to use/change these records receive a message such as "Record
guard conflict with USERX - Retrying" (see the Network section of the Open
Access manuals for more information).
A difficulty I had with OA4 under version 4.3 of System Manager was
that this record locking was not recognised. This is easily tested by going
to Database and retrieving some records from a database. Bring up the Database
Operations menu ([F2]) and select "Lock" (if "Lock is not on the menu then
you are not running OA network version). Set a "private lock" on the records.
Now start another session of Open Access (with System Manager just press
Alt-2 to multi-task) and try setting a private lock on the same records.
You should get a network warning message if the record locking is working
correctly.
To overcome this problem I had to load the advanced Netbios emulator
NETBBUF.EXE which is shipped with the latest version of System Manager.
This emulator is loaded by adding the line
to the SYSINIT.BAT file. It works in the background, just like the System
Manager Print Manager.
By contrast, Theo successfully used an earlier version of NETBIOS.COM
when using OA3 under System Manager version 4.3. The latest "minimal" Netbios
driver is called NETBIOSD.COM but this did not provide record locking for
my OA4 system..
Once these hiccups are cleared up the combination of Open Access and
System Manager makes a versatile office information system.
OASYS
This is by far the most useful utility in the package. It provides for
systematic analysis and documentation of the structure of databases, print
forms, screen forms and Programmer source code. The utility is run by typing
OASYS from DOS. An OA-style Main Options Menu appears.
The Database Documenter allows you to nominate a database (or select
from a directory listing). It then displays an output-device selection
window. I suggest that you always print to "file" so you can view and edit
the results before making a hard-copy (note however, that a Notepad is
not available within OASYS and that Postscript printers are not supported
they do not even appear in the output-device section list even though the
list is based on your OA CONFIG.PRT file).
Once an output device is selected the utility generates a report giving
details about the number of records, deleted records and a complete list
of fields.
The Screen Documenter does a similar job for a screen form. The report
includes field attributes (eg "lower case not allowed") and notes about
must-match and external fields. A warning is printed if the display width
on the screen form does not match the field width. The query associated
with the form is shown. Dependent/contingent expressions are also included
in the report - very useful for de-bugging! Finally a sampler of each page
in the screen form is printed. A disappointment is that this shows where
fields are located on the form but it does not include their names.
The Print Documenter is very similar to the Screen Documenter. It also
includes levelbreak, group header, sub-totals and grand-total information
(expressions etc).
The Text Documenter works through an OA Program, starting from the main
program file. Several options can be set to include in the report. These
include an Xtree-style diagram of the program structure, copies of every
VIEW statement used in the program and SCREEN statements. Unfortunately
a variable list is not available.
Reformat a program works through your source code indenting lines within
WHILE, IF, SCREEN, ERRORS, KEYS or MENU statements. It reports nesting
errors (unterminated IF statements etc).
The Organiser allows you to group all files (DF, TXT, SMK, CHT etc)
for a given project. A description can be assigned to each file and batch
processing, using the other utilities, can be undertaken.
OTHER TOOLKIT UTILITIES
SIZES A utility to report on free disk space and number & size of
OA files of various types. It also performs a backup of directories in
the OA Searching Order.
REMOVE (for networks) Removes phantom locks on files due to network
crashes. It cleans out "&" files.
LST2TXT (compiler) Converts a Compiler LST file back to program TXT
files. This can be useful for de-bugging.
BATCOL Sets pre-defined screen colours, date format & decimal format
from DOS, before loading OA. Gee whiz!
OACOLOUR Sets custom screen colours, borders, user-function keys and
date formats from DOS.
MAKEDF Creates a one-record, one-field database called TOOLS.DF and
set the field to a string of up to 77 characters. This can then be accessed
from within OA!
SRCHORD Replaces the OA Searching Order with a single item. The idea
is then to use a macro to build your desired Searching Order. This seems
to be intended for cases where several users use, and change the configuration
of, one machine.
UNDELETE DATABASE RECORDS
In addition to these programs, there is DOS utility called OA_UNDEL.EXE
which can be used to recover accidentally deleted database records. This
works on OA3 & OA4 databases, provided that no records have been added
and the database has not been "resized" since the records were deleted.
One of the questions raised during an OAUG meeting was along the lines
"how can I print percentages of the grand total against the record items
in a print mask?". The problem is that the printing procedure works through
the records, printing as it goes. Therefore it doesn't "know" the grand
total until the end of the report. A two-pass procedure is required in
order to calculate the grand total then use this in the percentage calculation
for each record.
One of the suggested solutions was to create a one-record dummy database
with a number field (to store the grand total) and a blank text field.
A blank text field is also added to the source database. Then the OA Database
Query is used to join the two databases (via the blank text fields - which
must both be key fields) and the Update function is used to set the grand-total
field in the dummy database to its value plus the value of the source record.
After the Update is finished the grand total field will be the sum of all
the source records (see the OA Database manual for a more thorough explanation
of this use for the Update command). The dummy database can then be used
in the print mask, using the same blank text field to join the databases.
An alternative solution is to use the VAR C-call under Programmer. First
include the following item in the print mask, in order to print a percentage
value:
100*amount/(call('var','g','','real1')+0.0001)
This "gets" a value called "real1" from an area in memory set aside
by the C-call.
Incidentally, the 0.0001 is a lazy way of trapping divide-by-zero errors.
Now load the Programmer and type in the following program:
Save the program ([F2]S). Now just press [Alt F10] to run the program.
First it retrieves the desired records. Then it calculates the grand total
and passes this value to the C-call. Finally it prints the retrieved records
via the print form which uses the C-call value "real1" to calculate percentages.
The 'g' and 's' parameters in the C-call refer to "store" and "get".
Note that the OA4 manual contains an error - the SETORGET parameter is
not Boolean - it must be 'g' or 's'. Also "real1" seems to be passed as
an integer and "int1" doesn't seem to work in the OA3 version (check the
file timestamp for the version number of the C-call).
The 's' option of the C-call also works in a print mask (or database
screen mask!) therefore there is another method for obtaining our grand
total in order to work out percentages. The trick is to have a "store"
C-call at the end of the print form in the totals section and to run the
print form twice. For example, the totals section could have:
"sa":sum(amount)
This assigns the grand total to the "real1" variable in the C-call.
First you will need to "attach" the VAR C-call using the Utilities/Language_call
selection from the Main Options Menu. Then "print" the report to the screen.
If the print form is now printed a second time (to paper!) the grand total
will be passed to the formula in the record section.
The VAR c-call can be recursive so you can have an expression such as
call("var","s",call("var","g","","real1")+1,"real1)
to increment a counter by one each time. If this is done it is best to
reset "real1" at the end of the print form with call("var","s",0,"real1")
otherwise it might not be zero when the printing is repeated.
Open Access Programmer is much more than a database programming language.
It contains many tools for communicating with external devices. These notes
provide a quick guide to these tools.
ASCII Files
ASCII text files can be opened and read, line by line, with the INPUT
and READLINE statements. For example, suppose we have a text file called
DATA.TXT in a directory which is included in the OA Searching Order. The
following program will open that file and display each line on the screen:
s1 = ''
Note that all text operations in Open Access have a limit of 255 characters
therefore the text file must have a "line feed" every 255 characters or
less - if it does not the 255th character is treated as a "line feed" and
is lost.
To create a text file use the FILE statement. In this example a text
file called DATA1.TXT is created and several lines are added to it.
s1 = ''
These two built in functions are generally all that is required for
manipulation of text files. However, there is also a C-call available which
has DOS file functions. It is called FILECOMP.OAC. The functions are:
Multiple Printers
The PRINTER statement is actually a special case of the FILE statement.
The FILE statement can be used to open and print to printers as well as
ASCII files. Using the FILE statement, numerous printers can be "open"
and used at the one time. In the following example of a hypothetical point-of-sale
system three "printers" are opened, a laser printer (for invoices), a cash
register printer and a serial display device.
PRINTER IS "HP LASER"
Input from Serial Port
Some "real world" devices communicate with PCs via the COM1 serial port.
OA4 is supplied with a C-call which enables a program to read data coming
in via the serial port. The C-call also works with OA3.
I have successfully used this C-call to read measurements from the NSW
Roads & Traffic Authority's Truckalyser (a mobile roller brake testing
rig for truck brakes). The Truckalyser is designed to measure wheel loads
and brake forces at each wheel. The data can then be sent to a PC via a
serial port. My OA program displays a screen mask which records data about
the vehicle under inspection. When the inspector presses the Database tips from Shane Trengove
1. When converting a large database with a memo field to dBase format
I found that it would hang the PC when it got about 80% of the way thtrough
the conversion. Eventually I found this to be due to unformatted memos
which came about when the data entry operators merely typed in the text
with no carriage returns.
To fix this I retrieved all records (or can be done in browse) opened
the SMK. I then created a macro which moved the cursor to the memo, opened
it, formatted it using the format command, resaved the memo, then saved
the record and advanced to the next record. As I had 9000 records to edit
I made the macro edit 5 records and then used the REPEAT command to run
it 999 times. I then ran the macro twice (plus a few more) to edit all
records.
This is a handy way of editing large numbers of records if you can't
or don't use programmer and the UPDATE command cannot be used. I find the
repeat macro command a great bonus for such jobs and doesn't seem to exist
in other software.
2. Whilst on macros, I have set my Shift-F8 key to invoke TEMP.MON (see
"User defined keys" in the manual). I use little temporary macros a lot
and have no need to keep them. So I call them all TEMP and invoke them
quickly with SHIFT-F8 rather than Alt-F8. Its quick and very versatile.
3. On networks I found it useful to set c:\tmp (or c:\temp)at the top
of the search order in OA and also set it as the scratch file directory.
Setting c:\tmp at the top of the search order removed the increasing
number of user's temporary data files and queries from the group drive
on the server and distributed them back to the users.
Setting c:\tmp as the scratch volume can also speed up OA on some networks
as the scratch files can be read and written faster on the user's hard
disk rather than the server.
4. I regularly have to import and export data via Lotus format spreadsheets.
The only problem I have is with the dates. This is overcome in the following
ways.
a. Lotus dates start at 1/1/1900 and OA starts at 1/1/1948 (I think
these are correct). Since they are numeric fields they have a numeric value
difference of 17532. In your OA spreadsheet it is a simple matter to insert
a cell, column or row then copy the dates you have and use the UPDATE command
to add or subtract this value depending on whether you are importing or
exporting.
b. You can convert the dates to text by printing the date data (if its
rows or columns) to file and then reimporting it via the EDIT command and
the notepad into an adjoining row or column. The text dates usually import
fairly easily into Lotus or Excel as dates.
One glitch I have found here is that the EDIT command seems to require
a column one cell longer than the column you are importing or conversely
'select' your column one row from the bottom in notepad before pasting
into the edit window. This avoids the 'Maximum Number of Lines Exceeded'
error message but means you have manually enter the last cell of data.
Database Passwords & Must Match Fields
by Michael Paine
Note: This section was produced by scanning an old document. Some scanning
errors might have been missed.
If you have several users accessing a database application then you
a-re probably going to need to look at password protection of some of the
data. There are several security features built into Open Access 3/4 and
these notes explain some possible strategies for protecting data. There
are four types of password which can be applied to a database:
Data file passwords are set from the Design/Modify File menu - selection
of the Database Menu. Screen form passwords are set from the Design/Screen
Form menu selection. Passwords must be entered twice. Note that passwords
are case sensitive "abc" "ABC" and "aBc " would be treated as different
passwords. If a password is not working check the CAPS LOCK setting!
Must Match Fields & Passwords
Confusion can arise where must-match fields are concerned. If you are
about to use a screen form which has a must-match field and the database
which links to the must-match field has a read/write or read only password
then you will be prompted for the password before the screen form is displayed.
Therefore if you have several password-protected must-match fields you
will need to enter a series of passwords. This can be a nuisance but it
is often the look-up database that you wish to protect from changes. One
way around this is to NOT assign a data file password to the look-up database
but to assign a Use Form password to the screen form for that look-up database.
Then the only time a password entry prompt will appear is if the user presses Duration of Passwords
Once a Read/write, Read only or Use Form has been entered during a session
it will remain in force until that session is terminated. Therefore the
only sure way to password-protect your data is, to exit from the module
(Database, Programmer or Compiler) after completing your session.
Passwords in Programmer/Compiler
Programmer has a PASSWORD statement which can be used to transparently
enter a password. For example, if the database "CARDSPI" has a read/write
password "secret" then the following statement will invoke that password:
There is an annoying limit to the number of PASSWORD statements which
can be issued during a Programmer or Compiler session. it seems to be about
8 but this could depend on available memory. As an alternative to the PASSWORD
statement you could use a macro (or the TYPABEAD C-call) to automatically
enter the passwords but this usually means temporarily opening views on
all relevant databases when the program is started in order to invoke the
passwords once and only once. This method also seems to have a limit on
the number of passwords which can be invoked - it appears that no more
than 16 passwords can be invoked during a session. Therefore the "rule"
is do not set more than 16 data file passwords for any one application!
With a Compiled program the user will receive a warning if the password
is incorrect at the time that the PASSWORD statement is processed. No such
warning is issued with Programmer. In these cases both Programmer and Compiler
treat the database as still protected and they will prompt for a password
the first time the database is accessed.
Contingent Must-Match Fields
A powerful feature of Open Access database is the provision of must-match
fields in database screen forms. These force the user to only enter a value
from a related database. For example, an Order Entry screen form might
have a Customer Code field. You would set this to "must- match" a customer
database so that the customer code in the Order record is the same as one
from the database of customers.
What happens when you want to enter an order for a new customer? The
traditional method, with a relational database system, is to first create
the new record in the customer database then to create the required Order
record. With Open Access you can create the new Customer record "on-the-fly"
from within the Order screen form. When you type the new customer code
and press [enter] a must match list is displayed because the customer code
was not found in the Customer database. With the "must-match" list displayed
press [Crtl Enter]. This will display a blank Customer screen form. The
new customer details can be entered and the record saved ([F IO]). It now
appears in the "must match" list from where it can be selected and "entered"
into the Order screen form.
An alternative approach (particularly under program control) is to make
the ORDER.CUSTCODE field a "contingent must-match" field. Under screen
form design, move the cursor to the CUSTCODE field and press [F6] to display
the "Entry Attributes" window. Move the cursor to the "Evaluated" row and
select "Contingent". You are prompted to enter an expression. Just press Incidentally, if you intend to create new database records by using
a KEYS GET statement within a loop (so that various function keys can be
made active during data entry) then it is a good idea to use the SKIPCHECKS
ON statement so that you can temporarily leave the screen form without
needing to satisfy any must-match or range conditions.
Top of page
Top of page
WORD PROCESSOR
The OA Word Processor is pretty basic by today's standards. It won't allow
you to take full advantage of the fonts available with laser printers.
It doesn't support columns and it does not allow you to view graphics on
the page (although "image" files can be included in printouts). Having
said that, it is great for quickly producing reports. The spelling checker
can be set to beep when it doesn't recognise a word that you have just
typed and you don't have to wait ages for a printout - the printer's internal
fonts are used rather than characters being generated by the software.
Top of page
PROGRAMMER
Managing databases with Programmer
$HELP # 6700
Note that the $M3 prefix displays the line in "title" mode.
$M3 HELP FOR ACCOUNTS MENU
You have selected the ACCOUNTS MENU.
GENERAL_LEDGER Add a general ledger record
STATEMENT Print a statement
$HELP END
$HELP # 66800
etc etc! Demonstration of help usage
This help function does not work directly with LIST and GET (SCREEN MASK)
statement. The usual database help will be displayed in these cases. This
can be over-ridden with a little inconvenience, by putting the MENU in
a loop and testing for the [help:F1] key, for eaxmple
HELPFILE "myprog" "c" ! MYPROG.HLP is on the C drive
HELPNUMB 6700 ! Reference the above help text
MENU
: GENERAL-_LEDGER : DO genledg
: STATEMENT : DO statement
: K__-UNDO : ! exit menu
END MENU
HELPNUMB 6800 ! Reference the next help text
If an error message of the form "Unable to sort non-key field" occurs
when you are running a programmer application in a network you might need
to "attach" the language call SORT.OAC This might be necessary even though
the field you are sorting is a key field AND the program works in a standalone
environment. The language call can be attached from within the program
using the ATTACH command or when OA3 is called from DOS ( eg OA3 a=sort
p=f:\oa3 ). In the latter case you will also need to create a one-line
text file with just the characters "sort" and name your file "sort.txt"
- see page 1.3 of the Language Calls Manual.
Top of page
UPGRADING FROM OA2 TO OA3 - TAKE CARE!
Conversion of database and spreadsheet files
Top of page
COMPILER
When you become proficient at writing programs in Programmer you should
consider purchasing Open Access Compiler. This uses nearly all of the commands
available in programmer plus several extra ones. Programs are written in
modular form, as with Programmer and in many cases they can even run under
Programmer. The main difference is that, with Compiler you must declare
all of the working variables with STR, REAL, INT, DATE, TIME or BOOL statements.
Programmer ignores these statements.
STRING(sysdate) & '"'
Top of page
UTILITIES
Directories
Nickname Path Use
File Names.
SCRATCH C:\OADATA\SCRATCH\ Test data
GENERAL C:\OADATA\GENERAL\ General use data
FRED C:\OADATA\FRED\ Fred's data (what else?)
UTILITY C:\OADATA\UTILITY\ Utility files (eg macros)
CODE C:\OA\ OA Programs, printer info
A A:\ Floppy drive
LASTDRIVE = K in your AUTOEXEC.BAT file).
SUBST K:/d (reset K:)
SUBST K: c:\oadata\general (substitute the directory)
echo K is now GENERAL
GENERAL \ pickgen
HOME \ pickhome
FLOPPY \ picka
etc
...
K K:\
...
filename.cnd A condensed program (generated from programmmer)
filename.mnu Another menu (multiple levels of menus are allowed)
filename.mon An OA macro
codename Compliled code
" My OA Applications " 30,7
MY_PROGRAM my_prog.cnd
DIRECTORY_OF_FLOPPY \ dira
FORMAT_FLOPPY \ fmta
BACKUP_OADATA \ backoa
MS_WORD \ do_word
GAMES_MENU games.mnu
echo Loading MS_WORD
cd\word
word
cd\oa3
echo Directory of a floppy disk
dir a: /p
echo Formatting a floppy disk in drive a:
format a:
echo Backing up modified OA data files
backup c:\oadata\*.* /m /s
echo Backup completed
by Keith O'Donnell
echo off
The %1 substitutes a value in the batch file. From DOS the batch file is
started with a 3-letter parameter, such as OA4 XXX. This copies the file
CONFIG.XXX, if it exists, to CONFIG.OA4 then starts OA. On exiting OA,
any changes to the configuration (e.g. screen colours or searching order)
are copied to the file CONFIG.XXX (or if it doesn't exist, a new file is
created). It is then available the next time that OA4 XXX is entered. Any
number of CONFIG files can be created in this way, provided that the 3-letter
suffix is unique.
[c:\oa4.bat]
echo Loading Open Access Application
cd \oa4
copy config.%1 config.oa4
oa4
copy config.oa4 config.%1
cd\
Top of page
COMMUNICATIONS - TALKING TO OTHER COMPUTERS
The Open Access Communications module provides a versatile, but relatively
cumbersome, way of communicating with other PCs and mainframes. However,
if you are reading this document on the Internet then you already have
access to better communciation tools than OA provides!
Top of page
USING OPEN ACCESS DATA WITHIN MICROSOFT OFFICE
by Keith O'Donnell
Press F8 to display the Desk Accessories
The conversion is very rapid. The file created is a .DBF file. If there
are any memo fields in the database file, then an additional .DBT file
is created to store these memos.
Choose File Converter
Choose Database
Choose DF/IF > dBaseIII
Enter the name of the database file to convert and the name of the
resulting dBase file to create. Then press F10.Open the Database into which you wish to import the file
A table of the same name as the dBase file will have been created.
From the database window choose File - Import
Select the data source as dBase III
Locate the file and click on ImportChoose File - Attach Table
The message telling you that the attachment is successful will appear.
This attached dBase file is regarded by MS Access as a table within the
database. You can query, create forms and reports based on the data in
this attached table.
Select the Kind of File as dBase III and click OK
Locate the file and click Attach
OA4 does not create index files, so click the Close button rather than
attempt to find the *.NDX files which are requested.F8
Exporting Spreadsheet Files
F
D
E FILENAME [DO]Press F8 to display the Desk Accessories
The file is now in Lotus format. It can be read directly by Excel. To view
this file from Excel,
Choose Spreadsheet
Choose FMD > WKS
Enter the name of the speadsheet file, the desination file and press
the F10 keyStart Excel and choose File - Open
Using OA4 Data for Mail Merge in Word
Change the type of file, at the bottom of the dialog box to Lotus 1-2-3
files (*.WK*)
Select the file from the list and click the OK button
by Michael Paine
Appendix A - Netbios Tips
System Manager Operating System
INSTALL =\SMUTILS\NETBIOS.COM
Once this is done Open Access should install successfully. Datapac (the
distributors of System Manager) have also developed some Netbios drivers
which use a minimal amount of RAM and appear to allow Open Access 3 to
load successfully. However, Theo Poulos and I have had mixed results with
this technique, as discussed below.
to the CCONFIG.SYS file (SMCONFIG.SYS for version 5.0).
\SMUTILS\NETBBUF.EXE
Appendix B - OASYS Toolkit
DISPI Netherlands has a set of utility
programs for Open Access. They are all designed to be run from DOS (in
some cases they can be launched from the OA application menu, as a DOS
program).
Appendix C - Passing values to Print Masks (VAR C-call)
The Open Access 4 manual (page 276 of Utilities Reference) describes a
C-call for passing strings and numbers to database print forms. In the
July newsletter I mentioned that I had obtained, via the SPI bulletin board,
the equivalent C-call for OA3. I have now had an opportunity to try out
this C-call - it is very useful!
ATTACH "var"
(you will need to substitute the items in [] brackets with your own file
name, numeric field name and print form name, respectively)
ok = TRUE
VIEW va = FROM [myfile]
USE va
IF COUNT(va) > 0
gtot = DB_SUM(va,"[fieldname]")
ok = call('var','s',gtot,'real1')
PRINTER IS ""
REPORT va WITH [printform]
PRINTER END
END IF
USE va END
DETACH "var"
call('var','s',sa,'real1')
Appendix D - Input and Output
(working with ASCII files and serial data)
INPUT td IS 'DATA'! td is a "handle" to the file
WHILE NOT EOF(td) ! loop until end of file
s1 = READLINE(td)
PUT DO NEWLINE
PUT s1
END WHILE
INPUT td END
FILE tx = 'FILE' TO 'DATA1.TXT'
OUT TO tx 'This is line 1'
OUT TO tx DO NEWLINE
OUT TO tx 'This is line 2'
FILE tx END
showdir - displays OA directory window (but is not interactive)
filexist test whether a file exists
filedel delete a file
apendall append one file to another
apendone append a line of text to a file
whereis return location of a file
clrwrite overwrite contents of a file
FILE pcr = "CASH_REG"
FILE sdd = "DISPLAY_DEV"
REPORT1 vi WITH "INVOICE" ! print one copy of the current record to
the laser
OUT TO pcr DO NEWLINE,'Total = ',vi.total PRECISION 2
OUT TO pcr DO NEWLINE
OUT TO sdd vi.total PRECISION 2
PRINTER END
FILE pcr END
FILE sdd END
Setting Passwords
PASSWORD "cardspi" IS "secret".
If a screen form has a Use Form password then the syntax is:
PASSWORD "cardspi.smk" IS "secret"
Where a database has both Read/write and Read Only passwords you can
add some logic to allow different users appropriate levels of access:
IF userauth > I
PASSWORD "cardspi" IS "bigsecret"
ELSE PASSWORD "cardspi" IS "secret"
END IF
Prepared by Michael
Paine (email) (home
page) 6 June 1998.