Attach Table

… better ODBC support and fewer database limitations

Updated 16 Jun 98 for Alpha2


In ChemFinder 5 it is possible to "attach" a table or stored query from a local or remote database, so that the table appears to be contained in your ChemFinder database. The remote table may be in another ChemFinder / Access / Jet database (.MDB file), or it may be in any ODBC-accessible data source reachable from your computer.

The ability to attach tables solves several problems. First, it is the new, improved mechanism for connecting to ODBC data sources, replacing a cumbersome and restrictive earlier scheme. You can now create a database containing a table of molecules or reactions, alongside a table from Oracle, and maybe another from Excel, all easily linked and displayed in a single form.

To carry out certain operations --- such as sorting structural data or handling large hit lists --- ChemFinder needs to create temporary tables. When you are using a read-only database, such as one on CD-ROM, the program cannot create tables, so these operations fail. Attach Tables solves this problem. Instead of accessing the read-only source directly, you can create a "portal" database --- a local, writable database with attachments to the external tables of interest. The portal looks and behaves just like the target database, but without the limitations.

Because it takes a few steps to set up a portal database, ChemFinder offers to do it for you automatically when the need arises. For example, suppose you mount the Lancaster CD, do a search, and attempt to sort by formula. The program brings up an alert, and offers, with one button press, to create a local database, attach the current table, save the form, and proceed with the sort. Once you've done this, you can then subsequently use the new form to get the Lancaster data with full functionality and performance.

Note: automatic portal creation is offered only if you attempt a sort.  If you do a search which gets a lot of hits on a read-only database, the program alerts you that it cannot handle all the hits, and it automatically truncates the hit list to the number it can handle.


Procedures

To attach a table:

  1. In the Form Properties Database dialog, open or create a database. Note the new button on this dialog (see diagram).
  2. Click Attach Table. This brings up the new Attach Table dialog (diagram).
  3. Open and select a database and table: Click the Open ODBC Data Source button to bring up the ODBC Data Source dialog, or the Open Database button to bring up a file dialog for selecting a Jet (.MDB) file. The selected database and table names will be shown in the dialog.
  4. If you choose Open Database, a File Open dialog will appear.  The file types listed in the dialog are all those for which DAO drivers are available on your system, including Access, FoxPro, Paradox, Excel, Text, dBASE, and others.  If you have installed any of these applications, you may attach tables created by them to your ChemFinder database.

    Note: if you use the ODBC dialog, and attempt to connect to a Jet database, you will get an error message. Use Open Database for attaching Jet tables.

  1. Click OK to attach the selected table to your original database. The new table will appear in the Form Properties database tree diagram.
  2. Select the new table and click OK. You can now attach form boxes to the columns of the attached table, and browse its data.
  3. Save the form. When you reopen it, it will re-establish the attachment.

To automatically create a portal database:

  1. Open a CD-ROM database, or in Windows Explorer, mark an .MDB file as read-only, then open it in a form. In either case the status bar should say READ.
  2. Optionally do a search, then attempt to sort on formula. You will get an alert with an opportunity to create a portal database automatically:
  3. Click OK. A Save As dialog comes up. Specify the name of a local database to be created, or accept the default offered ("<dbname>_PORTAL.MDB").
  4. Click Save. The database and form are saved.
  5. The sort then proceeds normally.
  6. Close and reopen the saved form to verify that the setup is correct.

Testing Suggestions

Follow the above procedures. Also: