DNS-LESS Connection to AS/400

lmonaghan

Board Regular
Joined
Nov 27, 2004
Messages
84
My IT department has been trying to figure out a way to use a DNS-less connection so that Excel 2003 files can be deployed across the company without having to configure an ODBC in the control panel.

We have searched numerous websites for the syntax, but cannot seem to get it to run. We can establish a connection by using the following code:

Code:
Dim ConStr

Set con1 = CreateObject("ADODB.Connection")
ConStr = ""
ConStr = ConStr & "Driver=Client Access ODBC Driver (32-bit);"
ConStr = ConStr & "System=10.1.1.10;"
ConStr = ConStr & "UID=NET;"
ConStr = ConStr & "PWD=netgti;"
con1.Open ConStr
MsgBox "Connection Opened!"

However, when we try to incorporate the connection string, I get a Run-time error: '1004' error.

Code:
SQL = "SELECT omnidata.omeyadlm.alusr as PLANNER, omnidata.omeyuser.eyname as NAME " & _
    "FROM omnidata.omeyadlm, omnidata.omeyuser " & _
    "WHERE omnidata.omeyadlm.alusr = omnidata.omeyuser.eyid " & _
    " AND omnidata.omeyadlm.alid='PLANNERS' " & _
    "ORDER BY omnidata.omeyadlm.alusr "

    With ActiveSheet.QueryTables.Add(Connection:="ODBC;Driver={Client Access ODBC Driver (32-bit)};System=10.1.1.10;Uid=NET;Pwd=netgti;" _
        , Destination:=Range("A1"))
        .CommandText = SQL
        .Refresh
    End With

Please help us! Any suggestions??? :confused:
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

lmonaghan

Board Regular
Joined
Nov 27, 2004
Messages
84
I have both sets of code to see if I can get connected to that server and it seems to work. On a separate note, I am trying to use similar path, user id, password, etc. to connect and I can't get it to work. I'll look over your link to see if I can make some sense of it.

Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,910
Messages
5,574,989
Members
412,633
Latest member
simon_elvin
Top