Problem solve Get help with specific problems with your technologies, process and projects.

DSN files causing ODBC problems

We are gradually upgrading PCs on our network from Client Access to Client Access Express. After the upgrade we've had trouble with ODBC connections using DSN files that have the system name. I have been able to eliminate these errors in most cases by replacing the system name with the IP address. However, any Excel spreadsheet that uses a dsn with a system originally defined retains the system name even after the dsn & query have been changed to use the IP address. I can tell that the system name is embedded in the worksheet because the ODBC configuration screen is displayed when the error occurs and shows the system name. Also if I pull the workbook up using Word, I scan and find the system name. If I create a new worksheet in the workbook using the same query, it works properly. How can I avoid having to recreate all the original worksheets?

You Can View User Feedback To This Expert Response

What you should really do is make the DSN work with system name. Best way to do it is like this:

* IBM client access
* IBM operation navigator
* Right-click and "add as400 connection"

Make sure you can ping the iSeries by name (i.e ping myas400). If not you should add the iSeries name to name resolution:

Add an entry for the iSeries NetServer to a Domain Name Server (DNS), the PC's local LMHOSTS, or set up a Windows Internet Name Server (WINS) to resolve the iSeries server name. The quickest way is to add an entry to the LMHOSTS file located in the Windows directory on a Windows 95 PC, or the WINNTsystem32driversetc directory on a Windows NT PC. If you cannot find an LMHOSTS file in the specified directory it might not have been created yet. Rename LMHOSTS.SAM in that same directory to LMHOSTS and then add a line with the following format:

TCP/IP-address iSeries-NetServer-server-name #PRE

For example: QNETSERVER #PRE

  • Continue with the setup

This should get you what you need.


  • This might be a simpler solution: The main step is to configure the relational database (RDB) name on the server. To do this a user should do the following:

    1. On the OS/400 CL command line, type the following statement replacing MY_SYSTEM-NAME with the name of your system: ADDRDBDIRE RDB(MY_SYSTEM_NAME) RMTLOCNAME(*LOCAL).

    2. Type the name of the server in the Relational Database field.

    3. Type *LOCAL in the Remote Location field and press the Enter key.

    The reason the user's system name shows up in Excel windows is because the driver always returns the RDB (Relational Database Name) through its interfaces. There is no defined interface through ODBC to return the IP address. ?Carol Miner


Ask your systems management questions--or help out your peers by answering them--in our live discussion forums.

The Best Web Links: tips, tutorials and more.

Dig Deeper on FTP

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.