DNS-LESS Connection to AS/400

[lmonaghan]

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:

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.

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???

 

[BrianB]

I cannot see how you can mix ADODB and ODBC code.

This might help :-

http://www.erlandsendata.no/english/index.php?d=envbadacimportwbado

 

[lmonaghan]

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!