Fingerprinting data sets
   Securing an Access database
   Repairing a corrupted Access database
   Linking to other data sources
   Linking to other database subsystems
   Merging descriptor projects (data sets)
   Next table of contents: Appendix 2: Development documentation

Previous table of contents    Main table of contents

Scientific data should be shared as much as possible. Yet, since the procedures of electronic publication, authorship and copyright to data sets are not yet settled (note that major changes to data set copyright were introduced around 1997), this if often not the case. The protection of the intellectual property rights of databases is a difficult problem. Regarding DELTA data sets, it seems that currently almost nobody is willing to share her or his work, because of concern about fraudulent use.

One method, which can be used to share data sets without loosing control over them, is to fingerprint them. Fingerprinting allows other people to use them, but it allows the author to recognize that somebody is in fact using her or his work. There are two variants that can be used:

The logic behind fingerprinting is: if anybody invests enough work to find the minor errors introduced by the fingerprinting, she or he has probably also discovered some real errors present in the data set, and spent enough time improving the quality of the data, to demand a kind of authorship her- or himself.

Fingerprinting is commonly used in commercial data collections. For example, any serious address collection will have some incorrect entries, which possibly point to private addresses of employees of the publisher. If anybody start using these addresses in forbidden mailings, or incorporates them into another address collection, the source can be identified.

Appendix 1: Table of contents    Main table of contents

DeltaAccess databases can be protected from being accessible to anybody, using the Microsoft Jet security model. Since DeltaAccess is distributed under a GNU General Public License, any user is allowed to secure her or his version of DeltaAccess.

To secure your database, you must have a full version of Microsoft Access. It is not possible to do the following with a run-time version. Please consider the alternative method of copyright protection using data fingerprinting.

Database password protection: A simple method of protecting data is setting a password for the entire database file. Close all forms of DeltaAccess, leaving the database explorer open. In the Tools menu, select Security and then Set Database Password. All users must now enter this password before they are allowed to open the database. This is an easy way to prevent unwanted users from opening a database. However, no other security measures are provided once it is open.

User-level security model: A much more powerful security model is the user and group level security of the Microsoft Jet Database engine. Users and groups can be given very specific rights or restrictions for each object separately. Some users may be allowed to modify the character definitions, while other users may only read them.

Using user-level security requires a good knowledge of the security model and can be complicated. I recommend studying the documentation provided by Microsoft (see Security in the Microsoft Access help contents). The following chapter Tips on user-level security may help you to get started and avoid some potential pitfalls. It is, however, not a step by step introduction.

Note that DeltaAccess exports data during the backup process to an unprotected database. This is intentional. It avoid problems during upgrading or when exchanging data with other users. (It can be quite confusing to unprotect a database.) If you use database security, keep all backup copies made by DeltaAccess in a secure place, and protect them by other measures.

Appendix 1: Table of contents    Main table of contents

User and group definitions are stored in the system.mdw database. Each installation of MS Access (or any other application using Jet) installs this file. If multiple users in a network use databases concurrently, they must use a single security workgroup database. The MS Access Workgroup Administrator, a link to which can be found in the main office directory, allows the administrator to change the security workgroup database to which an installation of Access is connected.

The default account is called, e. g., Admin or Administrator (the name depends on the language version of Access that you have installed). Any new installation of MS Access uses that account. As long as this account does not have a password, you are not given the option to log in under a different user name. To start using user-level security, you thus must enter a password for the administrator account.

Enter additional users and groups. (Unfortunately, user passwords can not be set from the administrator account. You must close Access, and log in under the new user name to set a password.) It is good practice to give rights only to groups and not to users. Create the groups you consider necessary, e. g., "Developer", "Scientist", and "Typist".

During the creation of groups and users, you are asked for a personal security ID (PID) for this user or group. This is a crucial point of the security model. You should make a note of the string you have entered here and keep it at a secure place. Note that the PID is case sensitive. The string is used to create an internally unique identifier in the workgroup. If another workgroup has defined the same group names, but did not use the same PIDs, the groups will be treated as different by Access. However, if you install your database in a new workgroup, and create groups with the same name and PID as in your original workgroup, the new groups will have the same rights as they used to have in the previous workgroup.

Thus, if anybody gets to know the names of groups and their PIDs, e. g., by stealing your written note, she or he can make a copy of the database, and create the appropriate groups in a new installation of Access. It is not necessary to know any user names or passwords, since she or he can create any user, and assign this user to the duplicated groups. On the other hand, if you do not know the PIDs of your own groups, it may be impossible to update your data to a new version of DeltaAccess or Microsoft Access at a later point. Also, your system.mdw might become corrupted or lost, in which case you will be locked out from your own security. If you know the PIDs and group names, you can recreate the most vital part of the system.mdw. The easiest way to understand the PID is to view them as a password for the right to create a specific user or group.

Now revoke or grant your new groups for each object in the database the desired rights. A good strategy would be to make all forms, reports and modules read only. Grant read or write rights to the project tables (or queries in the case of subsets).

If you have followed these steps so far, you have secured your users. This will avoid unintentional changes or deletions by users, which should not be allowed to do certain changes. Unfortunately, the database itself is not yet secure! The reason is that anybody who does not use your workgroup definition (system.mdw), but has access to the database (whether by stealing it or by normal access over a network) has still all original administration rights. The following two steps describe how to create a truly secure database.

The database and objects in it have an owner, who has always the right to the object, whether she or he has explicit user rights or not. The owner of DeltaAccess, as distributed, is the administrator! (This is necessary, if I want to give you full access, including the chance to fully secure a version of DeltaAccess. Only these administrator rights, give you the right to secure DeltaAccess.) Again, as in the situation above, any newly installed version of Access has full rights to the database! You must therefore change the ownership as well, to secure the database. Note that a user with administration rights can assume ownership of all objects in the current database. Unfortunately, you can not assume ownership of the database itself. Only this process in non-reversible, and is therefore required. To fully secure a database, you must create a new database while you are logged in under a username you have created yourself, i.e. not as the database administrator. Import all objects from the other database into your new database. Doing this, you will become the owner of these objects.

Another common error which is easier to fix than the ownership issue, is to remove users and groups from the default groups (like the administrators group). The effects is that indeed user rights are restricted to the rights given to the user itself or to the groups to which she or he still belongs. The problem is, that although the operation in the current workgroup (system.mdw) has been secured, the database has not been secured. Any user accessing the database (e. g. DeltaAccess.mdb) with a newly installed version of Microsoft Access will be an administrator in her or his own system.mdw. Thus, it is important to revoke all rights the default groups have to the objects in the database you want to protect. Select the administrator user, select all tables, queries, etc., and revoke all rights. Do the same for the default groups which are present in a newly installed version of Access.

DeltaAccess has not yet been tested in a fully secure mode. If you have problems, please contact me. You may have to set more differentiated rights for some forms. If you figure out what you have to change, I would appreciate a note. If sufficient interest exists, I could create default groups in the distributed version of DeltaAccess, which could be used as templates for your own groups.

My recommendation about implementing security is, not to secure DeltaAccess.mdb itself. Create a new database under a non-administrator user account as described above. Import only the tables and queries of your base and subset projects. Secure these fully in the new database. Open the original DeltaAccess.mdb again and delete the tables and queries you have imported to your new database. (If you use the management option Delete project, forms will be deleted as well; use the management option "add editing forms" at a later time to recreate the forms.) Create links back into the original DeltaAccess.mdb. You now have table like objects, which function almost like a table, but do not contain any data. The process is similar to linking tables from SQL-server into DeltaAccess. You do not have to revoke any right from these links. Even if a link has full rights, the external source of the data, i.e. the mdb file which you have secured, will limit the rights. The advantage of this solution is that it is easy to upgrade to new versions of DeltaAccess. You do not have to test which application objects require which rights for which users.

Note that it would be very difficult to create a DeltaAccess user interface for administering user-level security. A considerable knowledge of the local situation (in a network) is required, and security would not be complete unless the programmer has no ownership of the database anymore. To administer any security model in a local area network, a database administrator is required.

Appendix 1: Table of contents    Main table of contents

If your system is stable, database corruption occurs very rarely under Access 97. It usually occurs if your computer or the operating system crashes, and you could not close your programs normally. I have not had a case yet, where the options 2.a) or 2.c) did not work.

The following is a short list of things you should do to repair a corrupted Access database (*.MDB) file:

1. Close Access and make a copy of the database file. This is important, because sometimes a failed repair attempt can prohibit another attempt, which would otherwise have been successful. In a network, make sure no other user is accessing the database.

2. Try one of the following options. Always work on a new copy of your original database. Each option may repair your database. Use the next option only, if the previous option was not successful.

3. Rename the copy back to your original filename (usually DeltaAccess.mdb).

Appendix 1: Table of contents    Main table of contents

There is no automatic routine to link external data sources (from Microsoft SQL-Server, or other databases) into DeltaAccess. The exact procedures depend on the database server available. I assume that this task is performed only by experienced users and therefore provide the following instructions to do it manually instead. Linking data sources is not possible with a run-time version of Microsoft Access; you need a full version. Two tasks can be distinguished:

Linked tables are a special feature of Microsoft Access. A linked table can be opened like an internal table, but the data remain in the external source. The table is not imported. Changes in the external data are immediately reflected inside Microsoft Access. Linked tables are represented in the database explorer by an arrow in front of the table symbol (e. g. ). The linking of existing tables is necessary, e. g., if you integrate other database subsystems manually; see also the following chapter Links to other database subsystems.

To manually link a table, select the database explorer window and use the menu entry Get external data, Link tables in the File menu of Microsoft Access. The following process depends on the driver for the external database. Consult your Microsoft Access documentation if you can not find your data source or the linking does not work (see the Microsoft Access help topic Import or link data). You may have to install additional drivers either from the Microsoft Access package (not all drivers are installed in the default setup) or you may have to install an ODBC driver supplied by the manufacturer of your database server. Perhaps try to link any table from another Access database (mdb-file; use, e. g., a backup of a descriptor project) to get acquainted with the process and make sure your problems are driver specific.

b) Porting the information model used by DeltaAccess to an external database server

If you want to keep the data of a descriptor project on a SQL server database server and use DeltaAccess as a front end to it, you must first create appropriate data structures on the server, before you can link these tables back into DeltaAccess. Porting data to a server usually requires administrator privileges.

To create a functional descriptor project with data residing on a SQL server, first create an empty new project. Now create a table on your database server for each table created in DeltaAccess. This must be done manually, unless you migrate to Microsoft SQL-Server. A very useful porting tool for MS Access databases (or other ODBC sources) is the dbScripter (see www.dkgadvancedsolutions.com). It can convert the table structure to a SQL script, which can be run on the database server.

The foreign tables must have the same structure as the ones in Microsoft Access, especially regarding the field names. The sequence of fields (= attributes) does not matter. The field types should be equivalent to the types used in DeltaAccess. Replacing some types should work well (e. g. using double precision real numbers where DeltaAccess uses single precision, or integer where DeltaAccess uses Boolean = Yes/No). The most difficult situation occurs, if you have no equivalent type to the memo type used by DeltaAccess (= VARCHAR; up to 64000 characters). You should test the behavior of DeltaAccess and inform me about errors. I may be able to improve the error handling in a future version of DeltaAccess.

Some functionality of DeltaAccess described in this manual relies on relational integrity rules being implemented. Beware if you use a database which does not implement these rules.

After you have created the tables of the DeltaAccess information model in the external database, you must link these tables back into DeltaAccess, see the task discussed above.

Next, delete each table created for your empty new descriptor project inside Microsoft Access (select it in the database explorer and press the Delete key) and rename the equivalent linked tables from the foreign source to these names. The table names in the foreign source (SQL server) and inside Microsoft Access can be different, but you must use the standard names for the linked tables inside DeltaAccess. To rename a linked table click twice on it in the database explorer.

You now have an empty descriptor project, where the data reside on the SQL server and the editing and dialog forms are available in DeltaAccess. Avoid to delete this descriptor project using the delete management option. This will break the connection to the SQL server (it will not delete the data though).

Note that you can not import DELTA files directly into such a server based descriptor project! The import process always deletes existing projects and creates a new project. To import data from DELTA into your SQL server tables, import the data set to a different (temporary) project name, and use the overwrite management action, to move the data to the SQL server. This action should preserve the connection to the server (this has not been tested yet, please inform me if problems occur!).

Note that the information model and thus the table structure may be changed in new versions of DeltaAccess. When upgrading to a new version of DeltaAccess, the restore backup mdb function changes old projects to conform with the current information model. If you have moved data to a server, you may have to do this manually. The information model on the client and on the server side must be identical. You have several options:

• Create a backup of your server based descriptor project, and restore this to the new version of DeltaAccess. Export the new table definitions to your server again.
• Create a new, empty descriptor project, and manually check the table definitions for changes, which would affect the server. Changes like different phrases in the description do not affect the server. Manually change the server tables accordingly.
• If you are experienced with Microsoft Access/VBA, you can read the upgrade code used by DeltaAccess. Open the module DeltaLibRestore and look at the vba code starting with "If Version =" in the RestoreBackupMDB function. This code performs the version conversion during restore. Upgrades are often performed by SQL queries, but since these are embedded into the general VBA-restore code, some knowledge of VBA will be necessary.

Experiments were made with linked external tables, but porting descriptor projects to a SQL database server has not been tested. I will try to help you if you experience problems. The following problem was already found:

When you create a linked character subset, a special table containing the CID numbers included in the subset is created. DeltaAccess tries to protect this table through referential integrity to make it immune to changes of CID. Such changes occur, e. g., if you reorganize the sequence of characters. If the subset is based on a descriptor project linked in from an external data source, referential integrity will not be possible. In contrast to character subsets, linked item subsets should be no special problem. Static subsets are vulnerable to changes of IID, but dynamic subsets should work as expected.

Appendix 1: Table of contents    Main table of contents

Migrating to Microsoft SQL-Server: If you consider using Microsoft SQL-Server as your database server, a migration tool called upsizing wizard (http:// www.microsoft.com/ AccessDev/ProdInfo/ AUT97dat.htm) is available free of charge from Microsoft. This tool will create tables with a compatible data structure on the MS SQL-Server and will create triggers simulating the relational integrity rules that were defined in Microsoft Access. It also will automatically move your data, and give you much information about the cooperation of Microsoft Access and Microsoft SQL-Server. A good book about the interaction between both systems is Access and SQL Server by Viescas, Gunderloy and Chipman (Sybex). If you are using MS SQL-Server 6.x, be sure to select "Use Triggers" and not "DRI" (= declarative referential integrity) when exporting the referential integrity. Only triggers will export the cascaded updates and deletes, which are necessary for the functionality of DeltaAccess.

The attributes CollUnit, LitRef, and ItemName of the Item entity should link into separate database subsystems for collection management, literature references, and nomenclature respectively. Currently these attributes are simple text fields, not protected by relational integrity. You can enter any text, whether it is available in your specimen, literature, or nomenclature database or not. This clearly is not how it should be. The entries should be selected using pick lists linked directly into the foreign database, and a double-click should open the linked record in the foreign subsystem, e. g. a literature reference.

I can do this for my own literature and collection subsystem database (an unpublished Microsoft Access application), but other databases must provide an ODBC driver and you have to do the linking manually using the standard Microsoft Access tools. If enough experience is accumulated, it may be possible to generalize this process sufficiently, that it can be performed with the help of a wizard like tool. Anybody volunteering in this area is more than welcome!

I would very much like to link commercial literature/reference software into DeltaAccess. I would be grateful to anyone who could help me with this, by checking if his or her software allows Microsoft Access to link to any part of it (preferably a list of unique reference identifiers, if possible user readable like using the Harvard format). In general this would either mean that the reference manager uses a standard PC database format, or that it provides an ODBC driver. I currently do not know of any reference manager that provides this.

Some technical hints about using links in Microsoft Access can be found in the previous chapter Linking to other data sources.

Appendix 1: Table of contents    Main table of contents

DeltaAccess supports multiple independent descriptor projects. This corresponds with the current use of DELTA in taxonomy. Yet, in many cases it will make sense to join existing projects concerning closely related subjects into a single project. Multiple subset views, restricting the visible characters or items, can be created for different researchers, taxonomic groups, or regions. In contrast to the text-file-based classical DELTA programs, DeltaAccess supports multiple, concurrent users as long as they are connected through a local area network.

The main problem in merging data sets is that usually the character definitions of the descriptor projects are incompatible. If they are compatible, you only need to append the item definition and item descriptions, see the chapter Appending data to a project.

If the character definitions of the descriptor projects are dissimilar, you have to merge the character definitions first. This can be a difficult process, because usually only experts can decide which characters are identical and which are not. It is not sufficient that the character name is identical: the same term might be used for entirely different features if the item groups are sufficiently distinct.

A true merge, which will append those characters which have identical meanings, but append all other characters is not yet available among the options of the append management action. Two semi-manual methods are therefore outlined in the following paragraphs:

To achieve a merging of two projects with only partially compatible character definitions, you can do the following:

Open the Reorganize method, select all characters of the second project and "move" them as many steps down as the first project has characters. The result is that all characters will be renumbered in a way that the two projects have separate sets of character numbers, which do not overlap. If you have verified that some characters are identical, you can move these characters down again, so that their CID number equals the CID number of the corresponding character in the first project. Export both projects to DELTA coded text files, and join the two projects by manually appending the additional characters of the second project to the character definition of the first project. Do the same with the item descriptions and re-import the combined DELTA file. Use the Reorganize character states method to join those characters which are identical indeed (join all character states of these characters). Beware that some DeltaAccess features might be lost when you export to and re-import from DELTA coded text files!

Appendix 1: Table of contents    Overview manage projects    Main table of contents

So far I found the following sources for problems you might have:

• If you cannot open the file DeltaAccess.mdb in Microsoft Access, try to open Access and using the file open command, instead of double-clicking on the file in the Windows explorer. If this does not work either, you might be using the wrong version of Access. Check which version of Microsoft Access you have installed, and which version of DeltaAccess you have downloaded. DeltaAccess is available only for Access 97 (= Access version 8) and can not be used with earlier version of Access like Access 95 or Access 2. It may work with the coming Office 2000, but this has not been tested yet.
• If text on the screen is only partially readable (labels on the forms, etc.), you should check whether the Windows standard TrueType fonts Arial and Tahoma are properly installed. Some reports also need the standard TrueType fonts Times New Roman and Wingdings. These fonts are installed by default on any computer with MS Office/Access 97, but you may have deinstalled them.
• If you receive the following error immediately after opening DeltaAccess: "The expression XXX you entered as the event property setting produced the following error: ...", where XXX stands for On MouseMove or On Timer, Microsoft Access has probably failed to localize a library necessary for its operation. This is a problem of the Microsoft Access installation itself. To close the error dialog box and prevent the error from reappearing immediately, move the mouse to the background area and press the escape key twice. If the welcome message is still open, close it (use the menu: File, Close; use the keyboard if the mouse can not be used). If the database explorer, is not visible, unhide it (menu: Window, Unhide, "DeltaAccess: database"). Select a module in the database explorer (e. g. UsrLib) and click on the Design button. Select References from the Tools menu, and check the path to the libraries marked as used in the list. One or several of these libraries might be missing. Correct the path to the libraries, close the dialog box and select Compile and save all modules from the Debug menu.

Installation    Main table of contents    Next