[Revised Aug 05].
See below 'Access' regarding change of database handling in Aug 2005.
The program uses the MicroSoft 'Jet Engine v4' to handle its data. 'The Jet engine is also used by Microsoft 'Access 2000' , so the raw database can be read by any program that understands this format, including 'MS Access' MS Excel' and 'MS Word'.
Note however, that the program does not use 'MicroSoft Access' itself, so none of the services or restrictions of 'Access' apply .
Almost all the data is retained for a minimum of three years.
Tables and fields are meaningfully named, with nothing hidden or encrypted except the passwords.
- Using 'Access' special reports can be written, external to the pharmacy program, to read and report on the data.
- Retrospective data analyses can be done using 'Access'
Most computer professionals, and many computer hobbyists, can set up 'Access' to provide these special functions in a way that is easy to use for a non-expert.
User support
HealthSoft is happy to assist with any support required to handle the data extraction but cannot assist with support and advice on use of the MicroSoft 'Access' 'Excel' or 'Word' programs as we do not have any special competence with these.... contact MicroSoft support.
- A 'Report generator' is provided to enable special reports to be written from within the pharmacy program for most purposes without requiring the Microsoft 'Access' program.
Version of 'Access'.
From August 2005 'Access 97' and 'Office 97' can no longer be used to access the data, the oldest compatible being 'Access 2000' or 'Office 2000'
The program does not use Access itself, but it uses the 'Jet' DB engine V4 which is also used by 'Access'. Thus the raw database can be loaded in 'Access'. The data can normally be loaded in later versions of Access too, but; WARNING... see below regarding change of version.
Work off a copy Search for, and if possible, make a copy of the database, which is called LOTS.MDB . Check to make sure you have the current copy of the DB by confirming the date, as the program makes safety copies that will not be fully up to date. Always work off a copy so the 'live' database can be left alone. If working off a backup disk, unpack the backup files using PKUNZIP or its equivalent.
Working off 'live' data Only if unavoidable. Warning. If using a later version of Access than 'Access 2000', DO NOT accept any offer to "convert database to [the later version]" or the program will be unable to use it again and it will have to be restored to the old version from backup. Apart from this, it should be safe to use a later version. Remember that the program is not using 'Access' itself, it is using the raw 'Jet' engine.
Mail merge
To Mail Merge to MS 'Word'.
This may require 'Word 2000' or later.
- Export from Clubs Mail Label utility
This exports to a in a folder that it will create called 'Export directory'.
The file is called 'xxxxMail Merge.txt' where 'xxxx' can be 'Lots' or 'PhcyPro' or similar. You can rename this and move it to wherever you want and then use it for your work requirements
The text file has the following fields for merging:
Title
Full_Name
First_Name
Last_Name
Address_1
Address_2
Address_3
- Open a new Word document
- Type in the text you want and leave spaces where you want to insert the fields from the exported text file.
- Save the file calling it what you want. This will be the basis of your form letter.
- Within 'Word' Select Tools Menu- Mail Merge option.
- In the window that opens, select:
1 Main Data
2 Create Main Document -Form letter- Active Window
3 Data Source
4 Get Data- Open Data Source - then scroll down in the Files of Type option box (at the bottom of the active window) and select All Files. This will allow you to view your exported file (*.txt) - as long as you are looking in the correct directory. Click on the text file then select Open (or double click on the file).
5 You will receive a message that Word found no merge fields Edit Main Document
6 Click OK. This basically reverts you to document in which you have type your initial text (your form letter).
7 Position the cursor where you want to insert a field , say the Title Field
8 Select Insert Merge Field from the tool bar at the top of the main screen. (Left had side of screen)
9 Click on Title in the drop down menu that is displayed.
10 This places the Title field at the cursor in your document.
11 Continue to place other fields you may require into your document - in the same way. Leave a space between fields on the same line (by hitting the space bar) and / or use punctuation as you require. To refine your merge you can experiment with the effects of Filtering records merged / excluded using the Insert Word Field options.
12 Save the document.
13 Select Tools - Mail Merge-Merge.
You will be asked where to merge to.
Select New Document (best to check your result rather than send straight to the printer)
Select Merge
The merge can also be effected from the Merge tool bar rather than going into the Tools menu option.
Most computer shops have people available who are experienced in setting up mail merge, or our local agent may be able to assist. Such work will be charged by the person concerned directly.
Writing special reports.
Requires 'Access 2000' or later version, and the ability to extract reports using it.
Search for and load lots.mdb in 'Access' , (or lots.mdb unzipped from a backup disk) which will bring up the tables and fields, all of which are visible, meaningfully named, and the purpose of which should be self explanatory.
If a special report is written for future user use is written, a Macro should be placed on the desktop for easy use in future. Although new fields will be added in future versions, existing fields will not be deleted in order to preserve backward compatibility with existing reports written by third parties.
It is safe to read any data, but nothing should be written to a 'live' database in case data corruption occurs.
Links between tables
Use standard DB techniques. E.g. finding out which supplier applies to a particular stock item;
Each product in the stock table is listed with a Primary Key of StockID an intersection table is used to link this to SupplierID in the Supplier Table as this can be a many-to-many relationship.
Gateways
By using the Gatways in 'gateway.ini' programs written by third-party developers can be invoked by the pharmacy program in real time when appropriate. Real-time access is not necessary for reports.
Related topics