Q
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: 9.5.10.1 QNETSERVER #PRE

  • Continue with the setup

This should get you what you need.

USER FEEDBACK TO THIS EXPERT RESPONSE

  • 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

==================================
MORE INFORMATION ON THIS TOPIC
==================================

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.


This was last published in January 2002

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataCenter

Close