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  


Connecting to Lotus Notes® Databases
© 2001 Frank Alexander

Preface

The purpose of this article is to explain how to link Paradox® to a Lotus Notes database through the use of Windows® ODBC and the Borland® Database Engine (BDE). The set up is somewhat lengthy but once set up, the connection is as easy as pointing to an alias in Paradox.

Introduction

Connecting to a Lotus Notes database will require you to download and register the NotesSQL driver, clean up any previous versions of the driver, install the driver, set up the database connection, prepare the BDE alias, and assign an alias name to each database. It is important to note that for every Lotus Notes database that you want to access you will have to set up a separate ODBC datasource (more below).


Step 1: Check for previous versions of the NotesSQL driver and remove this driver

To perform this step, open up the Windows Control Panel.

Open the "Add/Remove Programs" applet.

Check the list of installed programs for "Lotus Notes SQL Driver" (or a variation of this name).

If you find this driver installed, remove it by pressing the remove button.


Step 2: Download the NotesSQL driver

Go to the Lotus website http://www.lotus.com/notessql and download the "Lotus Notes SQL 3.00 95/98/2K/NT" driver pack. You will have to register with Lotus to obtain the driver. Just fill out the registration and submit for access to the file. Save the file to a temporary directory.


Step 3: Install the Notes SQL driver

Install the driver that you just downloaded from the Lotus web site. Near the end of the install, you may see the "ODBC Data Source Administrator" open. If so, close that for now; we'll come back to this later.


Step 4: Determine the Notes database information

Open Lotus Notes. Go to your "workspace" and find the database you want to connect with Paradox. Right click on the database and select "Database > Properties". Make note of the "Server" and "Filename" field values; you will need them later. Close the properties dialog box.


Step 5: Set up the ODBC Data Source

Open the Windows "ODBC Data Source Administrator" from the Control Panel.

Note: the ODBC Data Source Administrator is in a sub-directory, "Administrative Tools", of the Control Panel in W2K.

Go to the "User DSN" tab. Click on the "Add" button. Select the "Lotus NotesSQL Driver (*.nsf)" driver from the list of available drivers. Press the "Finish" button. The next screen that appears is the connection definition screen where you will describe what database to connect to, the alias you will use for this database, and other "options". Under the "Data Source Name" field, enter any text (this will become your Paradox Alias name). Under "Description" enter any descriptive text for this database. Now pull out your notes from Step 4 (the database information). Enter the Server name into the "Server" box exactly as it appeared in step 4. Now you can either select the "Database" from a drop down list or type it exactly is appeared in step 4.

There is a special trick for databases with fields that exceed 254 characters: you can change the "Max Length of Text Fields" to 512 but when you pull the data in Paradox, it will result in all the fields as "Blob" fields rather than text fields. Don't do that unless you are sure that you need to access fields larger than 254 characters. I typically set up two aliases in this case, one for standard queries, and one for long field queries. It also requires special handling of Blob fields in Paradox, but that's a whole different article. Bottom line, leave the "Max Length of Text Fields" at the default of 254. Press the "OK" button.


Step 6: You're almost home. BDE settings

You're not quite done. The next step is to adjust some settings in the BDE. Open the BDE and go to the "Databases" tab. As mentioned in step 5, the text you entered in "Data Source Name" now shows up as a database (and is also your Paradox alias name now - I will refer to it as the "alias" from now on). Highlight the database in the tree structure on the left (if you don't see it, you might have to expand the " Databases" branch). On the right side of the window are the properties for the alias. If the "Database Name" field is blank (and it has been my experience that it will be) enter the "Data Source Name" again exactly as it appeared in step 4. If the "ODBC DSN" field is blank (it shouldn't be, but just in case) fill in the field with the same text that you used for the alias. Move off of the field so that the Blue Clockwise Arrow (Save Updates) is highlighted. Press the save updates button now. Accept all changes.


Step 7: So you have more Notes database?

Repeat steps 4-6 for each database. You will need separate ODBC entries and BDE entries for each database connection.


Step 8: You are Done

Close the BDE. Paradox is now configured to access the Notes database through your new alias. Note: if you had Paradox open during this time, you will need to close and restart Paradox for the changes to take effect.

As with all non-standard aliases, the Notes alias you created will not show up in the Project Viewer, but will show up in the various open or select table dialogs.

Limitations

Notes Databases will contain multiple tables depending on the Notes forms used to input the data. The Notes form will be the equivalent of a table in Paradox. There are some limitations to be aware of. If the forms contain more than 250 fields, you will not be able to open the table to view or use the Paradox query (QBE) functions due to the number of fields. However, you can use SQL to access the database data if you know the field names (enumFieldNames will return all the field names). One other nuisance is that Paradox will always prompt for an alias logon that does not pass along the password to Notes. Lotus Notes will run the request through to the Notes User ID file for logon information. You can set Notes to allow pass-through logons in the ID file settings. This will stop Notes from prompting for the password when Notes is running.


Summary

Setting up Paradox to connect to a Lotus Notes database is initially complicated. But once set up, its no harder than opening an alias in Paradox.


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: 15 May 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.