Figure 1. Installed ODBC data sources
by Dr. Peter Wayne
| You asked for it, you got it! ODBC lets your Alpha Five application manipulate tables stored in a number of different database engines. |
OK, so your company has standardized on Oracle, but your department would still like to use Alpha Five to create its own reports and forms.
Alpha Five version 4 allows your Alpha Five application to access a table that is not in Alpha's .dbf format. There could be several reasons why you might want to do this:
Version 4 can manipulate the foreign database through a protocol known as Open DataBase Connect, or ODBC for short. ODBC allows 2 database engines to communicate with each other through a common interface known as Structured Query Language, or SQL for short. You don't have to know SQL to use Alpha Five's ODBC capability.
(Alpha Five version 2 had limited support for ODBC. This was removed from version 3 but has returned in version 4.)
Before Alpha Five can use ODBC, you must make sure that ODBC is enabled on your computer. So the first thing you do is to click on Windows' Start button, then choose Settings,Control Panel, and click on 32bit ODBC. I have installed Microsoft Access 97® on my computer, so I see this:
Figure 1. Installed ODBC data sources
Access in not my favorite database product, so bear with me as I create a sample database and table for us to use. From within Access, we'll create a database, called employee.mdb. And we'll add a table to this database, called employee. Creating a new database to Access and adding a table to an empty database are not much different from the same dialogs in Alpha Five. The only slight difference is in the Table Design mode, which looks like this:
Figure 2. Designing the Access table
Access uses the term "Text" when Alpha Five uses "Character". Also, in this Access table, I have made employee_id into a key field, by highlighting the field and clicking on Access' key icon on the Access toolbar.
I then saved the Access table and moved back to my familiar Alpha Five. We can now create a path to the Access table by attaching it to one of my Alpha Five databases. From the Alpha Five Control Panel, I select Tables, and then from the menu choose Tables/Sets, Attach SQL Table:
Figure 3. Attach a SQL Table to an Alpha Five database
I next tell Alpha Five that I am attaching an Access table:
Figure 4. Attach an Access table to Alpha Five.
Next, I select the Access database that contains the table:
Figure 5. Choose the Access database that contains the table we are attaching.
Next, I get to select the Access table of interest from the list of Access tables that are contained in this database:
Figure 6. Choose the Access table
(Don't be confused by the fact that I named both the Access database and the Access table employee. One is a database and the other is a table, and in this case it is just a coincidence that I gave them each the same name.)
And finally, I give the virtual table a name for the Alpha Five database to use. Alpha Five calls this virtual table a link table. No new table is constructed, but Alpha Five can be used to create forms and reports based on this table. In addition, you can use Alpha Five field rules to control your data entry from within Alpha Five. (Of course, any field rules that you create in Alpha Five won't be enforced if another user manipulates the table through Microsoft Access.) I called my link table MyEmployees:
Figure 6. Assign a table name to the link table.
Most of Alpha Five's famous field rules are now available for controlling data entry into this link table. Here, for example, I created a transformation rule for the last_name field:
Figure 7. Create a transformation rule.
Be aware, though, that you can't use a field rule that affects the structure of the ODBC table.
| ODBC tables have certain limitations |
For example, you can't create an autoincrement field rule for a table linked by ODBC. That makes sense, because Alpha Five cannot prevent other users from working with the table through, say, Access itself. Also, you can't restructure, pack or delete an ODBC table, nor can you create an index for the table. Other limitations of ODBC are not so intuitive:
There are other limitations that are described in Alpha Five version 4's documentation.
Now I can create a form in Alpha Five that will let me do data entry on this Access table. In this example, Alpha Five is enforcing the Word transformation field rule for me:
Figure 8. Data Entry through Alpha Five.
There is nothing special about the connection to Access. You can make the same ODBC connection to any remote database that speaks SQL. That includes Microsoft SQL Server, Oracle, DB2, and a number of other databases. They all look like Alpha Five tables once you attach them!
In general, all data entry and query operations will run much faster with Alpha Five's own tables than with an external, or ODBC, table. ODBC's limitations make it unattractive for heavy-duty data entry. But for the Alpha Five user who needs a connection to a foreign database format, ODBC is here and it works.
9/20/98 - pkw
revised 9/24/98
Don't forget, we need your feedback to make this site better!