Paradox Community
Search:

 Welcome |  What is Paradox |  Paradox Folk |  Paradox Solutions |
 Interactive Paradox |  Paradox Programming |  Internet/Intranet Development |
 Support Options |  Classified Ads |  Wish List |  Submissions 


Interactive Paradox Articles  |  Beyond Help Articles  |  Tips & Tricks Articles  


Using ODBC Data Sources
© 2001-2002 Liz Woodhouse

Notes

Note for those trying to use ODBC for databases other than MS Access: All of the steps below apply, except that you may not need DAO/MDAC - though, depending on your database, these packages may have relevant drivers. Everywhere MS Access or it's driver is mentioned, simply substitute your database program and its ODBC driver. Drivers may be installed by your software (sometimes custom install is needed), or you can check DAO/MDAC, or you can check DataDirect Technologies (formerly Merant, maker of INTERSOLV ODBC drivers) to see if they make a driver for your database software. There may be more than one driver for your database software - if so, you may wish to try each of these as different versions/capabilities may be supported by different drivers. Please note that some of the ODBC DSN setup steps may vary slightly from driver to driver - simply use your best judgment when setting up your particular DSN.

Note about the native BDE drivers: This tip describes setting up and using ODBC because I found certain things weren't possible using the native BDE driver for MS Access. If a native BDE driver will do everything you need, I recommend you use that instead of ODBC.


Pre-Configuring The BDE

The BDE can be configured to show ODBC data sources by default. This can be done in one of two places (which options are available may depend on the BDE version you're using).
  • On the Configuration tab of the BDE, under Configuration > System > Init is an AUTO ODBC setting. When set to TRUE, the BDE will import ODBC data sources each time the BDE is initialized. This is apparently an older setting and should only be used if the "Virtual configuration" setting is not available.
  • Selecting Options... from the Object menu will bring up the Options dialog. Here, under "Select configuration modes to view" you can check the Virtual box, and this will cause all ODBC data sources to be shown in the list of aliases on the Databases tab. Data sources shown this way cannot be deleted or renamed and should be edited in the ODBC Administrator. If you alter the settings of one of these ODBC data sources within the BDE, it is changed from a "virtual" data source to a "persistent" data source and the changes are then stored in the BDE configuration file.
Please see the BDE Administrator help file for full details on these and other BDE options.


Getting The Necessary Drivers

In order to access a database through the ODBC, you'll need an ODBC driver for that database. If you don't already have MS Access ODBC drivers, or if you need newer drivers, please try one of the following (I strongly recommend using MDAC rather than the DAO download): If either of the above links becomes invalid, please email me and in the meantime, search Microsoft's web site for "data access". Note that newer versions of MDAC reportedly do not contain ODBC drivers for MS Access.

Please note that if you are installing MDAC, at the time this article was last updated, MDAC 2.5 SP2 was the last (most recent) version of MDAC to include MS Access drivers. If you are installing this version of MDAC on Win98SE or a newer version of Windows, it's possible this install will downgrade your ODBC version. For most folks, this won't matter, however, some drivers will only run under newer versions of ODBC. If this is an issue for you, you should also download the most recent version of MDAC (2.7 as of this writing), install 2.5 SP2 and then install the most recent. Installing the most recent will upgrade your ODBC version without removing the MS Access drivers. (Please note that I have not done extensive testing, and installing MDAC is done at your own risk. Please be sure you have the software needed to reinstall all your ODBC drivers and that you have your DSNs documented somewhere so that should it be needed, you can reinstall drivers and recreate DSNs (I didn't need to do this, but be warned).)

Update as of 25 Jun 2003: There is now an MDAC 2.5 SP3. I cannot be sure whether this includes MS Access drivers. As it is associated with Win2K SP3 and some may have issues with the EULA for that SP, I'll continue to link to MDAC 2.5 SP2 until it's no longer available, after which, I'll make the file available directly from the Paradox Community. Please see MS's Data Access page for notes about issues with MDAC (esp. with MS SQL Server).


Setting Up The ODBC DSN

Be sure to close Paradox before proceeding (or at least restart Paradox after the ODBC and BDE configurations are complete).

In the Windows Control Panel, start the ODBC applet (applet name varies depending on version, but always includes the letters ODBC).

In ODBC, make a new DSN (the User, System and File tabs have a little blurbs about when to use that type of DSN). Then follow these steps:
  1. From the driver list, pick: Microsoft Access Driver (*.mdb).
  2. Put a descriptive name in the Data Source Name field.
  3. In the Database section, click the Select... button, and select your .MDB file.
  4. Click on OK to close the ODBC Microsoft Access Setup dialog (other settings can be left default - unless the file requires a user name and password, in which case, click on the Advanced button and fill in those fields... I've never tested this part).
Close ODBC and open the BDE Administrator.


Configuring The BDE

The DSN you created in step above will appear in the list of BDE aliases. In some cases, this BDE alias may be available immediately for use in Paradox, in other cases, you may be able to simply edit some of the fields mentioned below (even if you edit them to the same value - sort of kick-starting the alias), and in other cases, you will have to follow the instructions below to create a separate alias.
  1. On the Databases tab, right click in white space and select New from the popup menu.
  2. Select Microsoft Access Driver (*.mdb) in the resulting dialog (New Database Alias) and click OK.
  3. Give the new alias a name, or keep the default (ODBC1).
  4. On the right, edit the fields as follows:
    1. enter the full path to your .MDB file in the DATABASE NAME field
    2. in the ODBC DSN combo field, select the Data Source Name entered in step 2 of "Setting Up The ODBC DSN" (you may need to save changes to the new alias before this list will display names to choose)
    3. if required/desired, fill in the USER NAME field (haven't tried this)
    4. you may also wish to fill in the LANGDRIVER and/or SQLQRYMODE fields
    5. couldn't begin to tell you want to do with the SCHEMA CACHE DIR field or the other fields filled in by default - I just left those as is.
  5. Save all changes and close the BDE.

Using The ODBC Data Source

Open Paradox (if Paradox was open during the above steps, you must restart Paradox after making the above changes), and you should be able to use this alias. Note, the alias won't show up in the Project Viewer, but it will show up in most of the dialogs that let you select an alias and then select tables from that alias (e.g. Open Table, Select File, Data Model, etc.).

To open an MS Access table, use the Open Table button on the toolbar or choose File > Open > Table... from the menu, select the alias created in the step above, then select the table. Note that certain types of MS Access queries will also show up in the Open Table dialog. It's possible that not all displayed files can be opened by Paradox! Note also that the Open Table dialog changes slightly after opening the alias, and you can choose types of tables to list from the 'List tables:' drop-down field. Finally, note that when you select the alias in the Open Table dialog, you'll get a login screen asking for user name and password - if these aren't required by the .MDB the alias points to, just leave the fields blank and press OK.

You can query MS Access tables in much the same way - simply create a new query or add a table to an existing query and in the Select File dialog, select the alias which points to your .MDB file, then choose the table of interest.

MS Access tables can be used for reports and forms and can be written to directly in Paradox.


Notes

MS Access aliases (and other aliases not identified in the BDE as "STANDARD") are not shown in the Project Viewer.

The max fieldname length in Paradox is 25 characters. Paradox accepts Access's fieldname lengths up to 31 characters and will automatically delete the characters above 25. If this deletion results in identical fieldnames, Paradox will put _1 at the end of the second field name. If the Access database has fieldnames larger than 31 characters you won't be able to open or query the Access table in Paradox.

To create a new table in a non-standard alias, you need to first access this alias somehow (e.g. open a table) to open a connection to the database.


[Editor's note: Check out Accessing an ODBC Data Source from Paradox in the Paradox Programming Tips & Tricks section for details on how to do this in code.]


Discussion of this article


 Feedback |  Paradox Day |  Who Uses Paradox |  I Use Paradox |  Downloads 


 The information provided on this Web site is not in any way sponsored or endorsed by Corel Corporation.
 Paradox is a registered trademark of Corel Corporation.


 Modified: 25 Jun 2003
 Terms of Use / Legal Disclaimer


 Copyright © 2001- 2003 Paradox Community. All rights reserved. 
 Company and product names are trademarks or registered trademarks of their respective companies. 
 Authors hold the copyrights to their own works. Please contact the author of any article for details.