by Gary Butler - GUI Computing
I recently had to build an application that was essentially an automotive specifications database, incorporating specifications for all makes and models of motor vehicles sold on the Australian market. The application will be distributed to a large number of users throughout Australia and will be periodically updated as new vehicles and models are released.
The application itself is designed for relatively inexperienced users, and retrieves the required information from the database via an "explorer" like interface. Basically the data is then displayed and available for printing, but at this point the user has the option of creating his/her own user notes. Any notes that are saved are retrieved automatically, each time the same vehicle is selected. This allows the user to store notes that are specific to one vehicle and enables the user the ability to add, change or delete the notes as required.
The trick was that the database would be distributed on CDROM and updated over time. There were some good reasons for this:
However, there were a couple of complicating factors, namely:
The obvious problem arises from the fact that a CDROM is read only and can't be written to. MS Access attempts to create, and write, to an LDB file when it is first opened. Access uses the .LDB file information to determine who has which files (or records in a file) locked. This was easy to avoid - open the database with exclusive access and read only, and Access will not attempt to open or create an .LDB file.
The other problem is what to do with the user notes saved during use of the database.
Again, the solution was straightforward. A second Access database was created which was copied to the C: drive during the install process. This second database contained tables with identical key structure to the CD database, but only one field - "UserNotes".
The tables from the read only database contained on the CD were "attached" programatically to the second database when the program was used for the first time. The Access database on the CD was opened "Exclusively and Read only" so that the .LDB file is neither required nor created. The second database is opened for both read and write access, and as it is stored on a local drive, creating and writing to the LDB file was not a problem. This process takes only a few seconds and is quite invisible to the user.
All searching and data retrieval was directed to the second database on the local drive, but because the data tables were attached from the CD to the database on the local drive, the data was actually retrieved from the CD.
All the criteria of the project were obtained. The size of the database
could grow by hundreds of megabytes. Unauthorised use of the software is
difficult as the application retrieves data directly from the CD. To update
the database, the user simply inserts a new CD and the user notes are preserved
during the updating process.