Open Access User Group Meeting - 11 February 97 TIP: If your browser doesn't word-wrap select FILE/SAVE LOCATION and view this file with your word processor. NEWS 1. Mark Sapper advises that the the Europeans are working on an update to Open Access 4, scheduled for release later this year. 2. A "patch" for dealing with the year 2000 and onwards should soon be available. The only known problem with OA is that abbreviated years are assumed to be in the 20th century (e.g. 01 becomes 1901 instead of 2001). The patch will allow you to nominate which century should be used. We understand that Lotus-based date systems will have a problem with the year 2000 being a leap year. This does not occur with OA (try F8 Appointments 29-02-2000). 3. No news - neither European web site has pages in English at this stage! Q & A ("Show and Tell") Corrupt index files? Keith O'Donnell reported a problem where a view under programmer intermittently did not find records that satified the query. This is possibly a due to a corrupt index (IF) file that is not repaired by resize. One trick is to DESIGN/MODIFY the datebase by changing all key fields to non-key, resizing the database, changing the relevant fields back to key fields and resizing again - tedious but worth trying. Lindy Kidman said that she found a similar problem went away when she changed another field to a key field. Loss of records during system crashes Lindy Kidman has experimented with the effects of system crashes (by turning her computer off part way through an OA program). She reports that the only sure way of ensuring records are not lost is to USE the view after each record is added. The problam of loss of data could be due to the disk cache. Most computer systems are installed with a disk write cache activated. This means that data might not be written to hard disk for a few seconds and therefore a system crash could result in loss of that data. In Lindy's case it might be that the time needed to re-USE the view is sufficient for the changes to be written to hard disk. In these days of fast hard disk access it might be that you only need the disk cache to work on reads and not writes - try disabling the cache function. Disk drives not recognised Reg Chasney reported that his Pentium PC intermittently (why are PC problems always intermittent!) did not recognise the CD ROM drive or other valid drives. This could be a problem with the CMOS battery which is usually located on the motherboard. This battery ensures that system boot-up settings are retained. In particular, check for a leaky CMOS battery which is causing corrosion of the circuits on the motherboard. A faulty CMOS battery also causes loss of system dat and time. Faulty keyboard? The voice of bitter experience from Keith O'Donnell! If you suspect you have a faulty keyboard, especially a short-circuit DO NOT TRY IT OUT ON ANOTHER PC as this could wreck the motherboard of that PC. Test a good keyboard on the problem PC. New keyboards are relatively cheap - compared with motherboards. Creating new tables from joined tables with common field names Phil Thompson reported problems when he used a query to joined two tables then used INTEGRATE to create a new database from the retrieved records. The problem was that several field names were duplicated. The Integrate function warned of a context problem and promptly rename all fields to A. B. C etc. At the meeting we tried the alternative method of using the MAKE command. This solved the problem because the duplicate field names were renamed with a 0 suffix. (eg. FNAME become FNAME0). DIF export has the same problem as INTEGRATE. Restoring Stations / Invoking a C-call from the Application Menu Shane Tregove writes that the easiest way to "restore" lost stations on an Open Access network (see December newsletter) is to add the following item to the APP.MNU file (or MAIN.MNU in OA4): RESET_STATIONS *STATIONS.OAC This automatically "attaches" and invokes the C-call. As usual, first you must ensure that all other users have "quit" Open Access. This trick also works will other C-calls that do not require parameters, such as MODES (to change the screen colours). Shane also reports problems with OA under WIN95 (3COM network on VAX/VMS). Response and printing have slowed and printing can be erratic. Version of OA files Michael Paine pointed out that the version of the files installed under OA is shown by the timestamp on these files. Xtree is useful for viewing the date and time of files (Alt F, Alt F). OA 3 has a timestamp such as 03:03 and version 4 has a timestamp 04:00. This can be useful if you somehow get the files mixed up (more likely with runtime installations). The Committee then presented a proposed programme for 1997. This is summarised below. User comments are most welcome. (best viewed with Courier font) TOPIC Feb Apr Jun Aug Oct Automation Batch files Macros, Prog Mail merge OA data used in Word, W.P. etc Scanning Scanners barcodes Printers Internet HTML pages Modems & Comms Comms Integration OA to OA to other of data MS Office software General Backing up PC Printers Future of Oracle Future PCs Network PCs directions Networking Networking OA After the break Keith gave a presentation on using OA data within MS Office products such as Word for Windows 6.0 and MS Access 2.0 - see separate notes (the trick is to convert your Open Access data to dBase format - with MS Word you can mail merge directly from dBase data and with MS Access you can "Attach" a dbase file). Keith has automated the data conversion process by creating an OA macro and including it in his MAIN.MNU file. Terry then played a video on Oracle's vision of the Network PC office (e.g diskless workstations running off a central computer like the old mini computers but with highly integrated software and data). Michael Paine observed that the Australian Datapac product Winframe gives users the choice of processing on a local PC, a "filesever" PC or a remote host (via modem or Internet), all under Windows NT and with "legacy" 386 PCs. Datapac are bringing out a Windows 95 version of this clever operating system. Using Open Access within Microsoft Office by Keith O'Donnell 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: Press F8 to display the Desk Accessories 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. 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. 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: Open the Database into which you wish to import the file From the database window choose File - Import Select the data source as dBase III Locate the file and click on Import A table of the same name as the dBase file will have been created. Attaching a dBase File From the database window, Choose File - Attach 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. 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. 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: F8 F D DF FILENAME, eg. customer [RET] dBASE FILENAME [DO] Exporting Spreadsheet Files 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: Press F8 to display the Desk Accessories Choose Spreadsheet Choose FMD > WKS Enter the name of the speadsheet file, the desination file and press the F10 key The file is now in Lotus format. It can be read directly by Excel. To view this file from Excel, Start Excel and choose File - Open 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 Using OA4 Data for Mail Merge in Word 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. Notice: Users should not act solely on the basis of the material contained in this document. Items contained in this document 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.