Rocode >> Contracting >> VetBasic

5. Software Engineering

In this section we discuss the software code used to link Microsoft Access with Firebird.

In Figure 31 we show the subsystems of VetBasic. The major subsystems of VetBasic are for Customer records, Marketing and Medical Procedures.

VetBasic Subsystems
Figure 31: VetBasic Subsystems.

The development of VetBasic was done using the Microsoft Office Developer XP software development kit. A strict process of development freeze, packaging and then release to production was followed.

Release to Production
Figure 32: Development Freeze and Release to Production.

There is an online copy of the release history for VetBasic.

A CD-ROM was created for each release. A readme file was included that contained installation instructions and version information.

Example Release CD
Figure 33: Example Release CD for VetBasic.

        **********************************************************************
        VetBasic 7 June 2005
        **********************************************************************
        This disc contains the VetBasic Package
        **********************************************************************

        ----------------

        A) SQL Server

        1) Install Firebird SQL server from Freeware Folder

        2) Create Database

        Create the directory C:\Databases

        Copy contents of the Database folder to C:\Databases

        ----------------

        B) User Terminals

        1) Install SetupOdbcJdbc on to PC

        2) Add Resolving Name with IP address to 

        \system32\drivers\etc\hosts

        Example:

        192.168.8.33	vetbasic

        3) Install GDS32.DLL

        Put in 
        C:\WINDOWS\system32


        4) Setup System DSN

        Go to Control Panel

        Select "Administrative Tools"

        Select "Database Sources (ODBC)"

        Select "System DSN Tab"

        Press "Add"

        Select "Firebird/Interbase Driver"

        Press "Finish"

        Enter your Data Source Name e.g. VetBasic

        Enter your Database e.g. vetbasic:C:\Databases\VetBasic.FDB

        Enter Database Account and Password

        Press "Test Connection"

        Close by pressing "ok"

        Close by pressing "ok"

        ...

        ----------------

        E) Technical History

        Changes:
        Removed Reference Link error with CDO on installation of Vetbasic to XP SP2

Figure 34: Excerpts from readme file on Release CD.

The main programming of VetBasic is done in VBA within Microsoft Access.

Figure 35 shows an example VBA script to filter Access Memo fields so that they may be passed via SQL and stored in a large Firebird VARCHAR field.

We added a MAX number to avoid trying to save a memo of greater length in the Firebird database. In this case a partial save is performed and an error popup is displayed.

Example VBA for Memo Fields
Figure 35: VetBasic VBA for Memo Fields.

For compatibility with Microsoft Access we added a boolean domain to Firebird.

Example of Boolean Domain
Figure 36: VetBasic Boolean Domain.

This domain allows us to save VBA 'true' and 'false' values to Firebird. We convert 'true' as "Yes" and 'false' as "No" on reading and writing to Firebird in VBA scripts.

        If Recordset("Microchip").Value = "Yes" Then
            Me!Microchip = "True"
        Else
            Me!Microchip = "False"
        End If

        If Recordset("PetSecure").Value = "Yes" Then
            Me!PetSecure = "True"
        Else
            Me!PetSecure = "False"
        End If

        If Recordset("TenYear").Value = "Yes" Then
            Me!TenYear = "True"
        Else
            Me!TenYear = "False"
        End If

Figure 37: VetBasic Boolean Read.

        Dim SQLMicrochip As String
        If Me!Microchip.Value = True Then
            SQLMicrochip = "Yes"
        Else
            SQLMicrochip = "No"
        End If
        Dim SQLPetSecure As String
        If Me!PetSecure.Value = True Then
            SQLPetSecure = "Yes"
        Else
            SQLPetSecure = "No"
        End If
        Dim SQLTenYear As String
        If Me!TenYear.Value = True Then
            SQLTenYear = "Yes"
        Else
            SQLTenYear = "No"
        End If

Figure 38: VetBasic Boolean Write.

Figure 39 shows how to open a connection to Firebird within VBA.

Global DBConnection As New Connection

Const Provider = "ODBC;DRIVER={Firebird/Interbase(r) Driver}; "
Const DataSource = "DBNAME=VetBasic:C:\Databases\VetBasic.fdb;UID=SYSDBA;PWD=blahblahblah;DSN=VetBasic;"

DBConnection.Open Provider & DataSource

Figure 39: VetBasic VBA Database Open.

We don't use a DNS server so you need to add a manual entry to your local hosts file for the IP address of the VetBasic database host. Figure 40 gives an example host file entry.

The host files is found as: C:\WINDOWS\system32\drivers32\etc\hosts

192.168.85.33	vetbasic

Figure 40: VetBasic Hosts Entry.

We used the Linked Table Manager of Microsoft Access to link the Access GUI elements, displayed in many Access Forms, to the Firebird tables of the VetBasic database.

To assist in operating in a concurrent environment, VetBasic includes a read before write check for concurrent update of Client and Patient records. On opening a client or patient file we store a duplicate of the original. We confirm that no concurrent updates have been made between the time of opening on screen and when updating on saving a client or patient record.

Click here for the next section.