A DSN, Data Source Name, is defined via the ODBC Driver manager in the Windows Control Panel. This causes an entry to be made into the system registry recording all of the connection information that you enter into the Control Panel. The problem with using a DSN is that every time you connect to the AS/400, the target computer must look up the connection information in the Windows registry. This is not a factor if you are casually connecting from applications like Excel, Access or SQLThing, but if you are doing Active Server Pages these registry lookups do add a small amount of time to the connection process. Also, using a DSN-less connection can give you finer control of connection attributes; in fact you can control your connection attributes on a page-by-page basis. In addition, by not requiring a DSN you make you application or web pages easier to distribute to target computers, i.e. you do not have to go to the target computer and use the control panel to create a DSN.

There are a host of connectivity options that you can use when connecting to the AS/400, and these can lead your connection string to become quite complex. Instead of typing all of the options in, you can let Windows create a connection string for you by creating a Universal Data Link (UDL) file. A UDL file is a file persisted set of information on how to connect to a data source. In this case, we will be using the UDL file as a quick way to build an ODBC or OLEDB connection string. To start, open a Command Prompt window, switch to a directory you wish to use and type COPY CON A.UDL and press Enter. Next, press the F6 key to send a ^Z command to the console. You should see the text, 1 file copied and then be returned to a command prompt. You have just created a 0 length file called A.UDL in your target directory.

Next, pull up the Windows Explorer and right click on the A.UDL file and select the Properties menu item. This will bring up an interactive window where you can set the provider and all connection options for that provider. Once you have your settings correct, press Apply then OK to save the UDL information. Next, open the UDL file with notepad, or some other editor, and Volla, you have a preformed almost perfect connection string. The only problem with the string is that it still points to an ODBC DSN. To make the string perfect, remove the DSN information from the extended properties and replace the quote (“) characters with double quotes (“”) so that the string will not be confused in your ASP page. Finally, after the Provider=MSDASQL.1; place the following information: Driver=Client Access ODBC Driver (32-bit); This string lets the MSDASQL OLEDB provider know to use the Client Access ODBC driver when it attempts to connect. Here is a code snippet that shows a proper connection string with lots of options:

ConStr = "Provider=MSDASQL.1;Driver=”
ConStr = ConStr & “Client Access ODBC Driver (32-bit);Password=SECRET;”
ConStr = ConStr & “Persist Security Info=True;User ID=HFA;Extended”
ConStr = ConStr & “Properties=""SYSTEM=172.16.2.200;CMT=0;”
ConStr = ConStr & “DBQ=WEBOE,HD1100PD,HD1100PO;NAM=0;”
ConStr = ConStr & “DFT=5;DSP=1;TFT=0;TSP=0;DEC=0;XDYNAMIC=1;”
ConStr = ConStr & “RECBLOCK=2;BLOCKSIZE=32;SCROLLABLE=0;”
ConStr = ConStr & “TRANSLATE=1;LAZYCLOSE=1;LIBVIEW=0;
ConStr = ConStr & “REMARKS=1;CONNTYPE=0;SORTTYPE=0;
ConStr = ConStr & “PREFETCH=0;DFTPKGLIB=QGPL;LANGUAGEID=ENU;”
ConStr = ConStr & “SORTWEIGHT=0;SSL=2;MAXFIELDLEN=32;”
ConStr = ConStr & “COMPRESSION=1;ALLOWUNSCHAR=0;”
ConStr = ConStr & “SEARCHPATTERN=0;MGDSN=0;"""
Set Con1 = Server.CreateObject(“ADODB.Connection”
Con1.Open ConStr