Like many other products that make data easy to use by everyday users, Access uses ODBC to access foreign data. Although ODBC is almost ubiquitous in database connectivity, there are some performance issues that may limit the use of ODBC. For example, I wouldn't use ODBC for any application that requires Online Transaction Processing (OLTP)-level performance. However, there are many applications where the benefits of a great GUI overrides any performance issues—for example, using the charting and data visualization features in Access.

The first step of this process is to establish an ODBC data source for your database. This was covered in detail in my previous 10-Minute Solution, "Web Access to Informix via ODBC." If you haven't installed ODBC and set up data sources, please read that article and come back here when you've finished. This Solution is written using Microsoft Access 97. The same techniques should apply for later versions, but the screens may look somewhat different.

When you first open Access, you are presented with a screen that invites you either to choose an existing database or to create a new one.

Select Database Wizard and click on OK:

The New screen appears, defaulted to the Databases tab. Choose the General tab and click on Blank Database to create a new database. Then click OK:

The File New Database screen asks you for the filename of your new database. Here, I'm using the filename "informixaccess." Because Access is a rather simple file-based database, it stores all of its data in files, even though the actual data may come from an ODBC data source. Click on Create to create the new database:

Once you create the database, you get a screen showing you the elements of your new database. The first thing you want to do is create some tables in your new database. Do this by selecting the Tables tab and clicking on New:

The next screen to pop up asks you how you want to create the table. There are two ways you may proceed from here. If you select the Import Table choice, you will completely copy all of the table structure and its data, into the Access database. For small tables that don't require up-to-the-minute data, this may be the choice for you. Once you create the table this way, it is separate from the Informix tables. Subsequent changes to the Informix data will not be reflected in the created table.

The other choice is to Link Table. This copies the structure of the Informix table into Access but relies on a live ODBC link to get the data. This is the preferable method if you have large tables that you don't want to duplicate completely into the Access system or if you need to access the most current information in the Informix table.

Linking to the Table
Let's start with the Link Table option. (The Import Table option is covered in the next section.) Select Link Table and click OK:

The Link screen next can be a little bit confusing, because the correct option is not immediately obvious. The screen appears to ask you to choose a document (most likely an Access database) from which to get the data. Only when you click on the Files of Type list box at the bottom of the form and scroll all the way to the bottom do you see the choice you need: ODBC Databases. Don't enter a filename; just select the ODBC Databases choice:

Next the Select Data Source screen pops up and shows you your available data sources. There are two tabs, one for File Data Sources and one for Machine Data Sources. Choose Machine Data Source and highlight the name of the ODBC data source for your Informix database that you created when you set up the ODBC data sources. Click OK:

ODBC pops up the following screen to allow you to log into your Informix database:

Enter your password and click OK. Assuming that everything was properly set up in your ODBC data sources, and assuming that the target Informix database is up and running, you'll get the following Link Tables screen:

Choose the table(s) you want to include in your Access database, or click on Select All to link to the entire database. In this case, you're only going to create the config_parm table. Click on the table name and hit the OK button.

The next screen gives you a list of the tables for which you have created Access links. If you highlight any of the tables and then click Open, you'll retrieve the data from the Informix table:

At this point, you can treat the new table(s) as Access tables and do whatever you wish to do in Access:

Importing the Table
If you choose to import from the ODBC data source instead of linking to it, as I just described, you will go through the same procedure listed above, only all of the data will reside in your ODBC data source instead of in your Informix database.

Be cautious of large tables in the Informix database. If you try to import a large table rather than link to it, you may be in for a long wait.

Issues Involving Access with Informix
There are a few issues that you have to deal with when using Access with Informix. One of the first issues is the matter of unique keys to the tables. Access expects a unique key for each table. If you don't have unique constraints or unique keys in your Informix tables, Access will prompt for a combination of fields that will define a unique key. Choose a combination of fields that will ensure uniqueness. Access also adds the owner name to the first part of the table name. If this is not what you want, you'll have to change it manually.

Also be aware that giving others access to your Informix database may introduce some security issues and data quality issues. Whether it's done on purpose or not by the designers at Informix, it is relatively complicated to change Informix data from either their GUI or from dbaccess. It needs to be done through an UPDATE statement. This means that any changes made to the data require at least some planning by the user. Access, on the other hand, allows users to change data in the table output form. This updates the Informix database when the table is saved or closed in Access. Some DBAs create a view of a table that they want to give access to and restrict the user permissions on this table so that it is read-only. If you create the Access link to the new view rather than to the table, you'll be able to enforce some security and/or integrity into the database even though you're allowing Access users to get to the data.

Have fun with your new Access database!