Search code examples
excelodbcibm-midrangevba

VBA odbc connection accessing a single library only

I have successfully connected to an as400 server. But whenever i execute an sql statement

select * from nosd0

It doesn't work, because nosd0 is in lib1/fil1(nosd0)

it gives an error saying nosd0 is not in lib2.

When I execute the query on STRSQL on as400 it works fine.

I tried creating an alias and it's malfunctioning. Please I really need some help on this one

Alias is working, I am accessing the wrong file.

Solution

  • Ok I figured out the problem, this will also help all those who wants to connect to their AS400 iSeries using VBA. ;)

    My problem above is that when I try my Query on the box, it accesses lib1/nosd0, and in VBA, I was trying to get lib2/fil1(nosd0) which is a description of the table nosdo itself. The simple solution is to query

    select * from lib1.nosd0

    More on that when connecting to an AS400 iSeries using ODBC, there is a parameter called DBQ Connection String Parameters

    My final connection string would be.

    ConnectString = "Driver={ISeries Access ODBC Driver};System=" & DCServer(I) & ";Uid=--;Pwd=--;NAM=0;DBQ=lib1,*ALL;"