DSN connection works; IBMDA400 does not (ADO to AS400)

[Guest.Visitor]

You can use an ODBC connection without a DSN using the line of code shown here; cn400.Open "DRIVER=Client Access ODBC Driver (32-bit); UID=xxxx;PWD=xxxx;SYSTEM=999.999.999.999" The ODBC driver really supports more functionality than the IBM OLE DB provider does. My ASP book from MC Press covers a lot on this topic. Mike

[Guest.Visitor]

Since you get past the ADO connection, sounds like your problem is not with the ADO connection, but in the stored procedure call. Have you looked at the joblog on the As400 job? One significant difference is the ADO connection is a sql connection, which is why you have the "." for the library qualifier. I believe that means it will expect to find the files in a library that matches the user profile. I include the following in my ADO connection string to specify where the files are located, where xxxx = your AS400 library name - "Default Collection=xxxxx" The other person suggests going back to an ODBC connection, but things I've read say the ADO connection is more efficient. I tried the Dsn-less ODBC connection, but I had to go to an ADO connection because it was the only way I could get it to prompt the user for their password when necessary. I didn't want to have to embed the password in the connection string.

[Guest.Visitor]

Just to clarify, both connections are ADO connections. The IBMDA400 provider is IBM's native OLE DB database provider. The option I show uses Microsofts OLE DB provider for ODBC databases. The bigest difference is that the ODBC provider has less overhead and supports more of the ADO functions.

[B.Nelson]

To get ODBC to prompt if necessary, I'd suggest doing two things: 1. Setting the Prompt Property on the connection, prior to calling the Open method: cn400.Properties("Prompt") = adPromptComplete 2. I'd use a connection string like: DRIVER=Client Access ODBC Driver (32-bit);SYSTEM=mySystem;SIGNON=1; If you want to add UID=myUserid; to the end of the string that's fine too. The SIGNON keyword indicates to use a default userid and prompt as needed. The only reason I suggest setting that is because it defaults to 3, which indicates to use iSeries Navigator settings for signon. iSeries Navigator default behavior may or may not cache passwords for users.

[jfoo0809]

Hi all, It seems that you all have done something with access AS400, we currently have an AS400 V3R2 and Client Access for NT installed in client machine. I am asking if I can use VB.NET to access using Client Access ODBC?? When I tried to use ODBCdataAdapter in VS, it said 'Can not connect to Database , error in DLL". Any ideas. JFoo

[hannow]

I have a routine (below) that calls a stored procedure on our AS/400 (simply returns an address). Note that the code has 2 connection strings, with the first one commented out. This first con string relies on a DSN, and works fine; the second uses a different provider, but always hangs-up on the cmd.execute line - ironically, the connection establishes just fine. I have client access 5.1 with the SDK, and am running this code from Access 2000. I have sometimes had trouble getting the correct references - could this be involved here? Any suggestions are appreciated!

---

Code

[B.Nelson]

V5R2 iSeries Access may work to a V3R2 box, but is not supported. V5R3 iSeries Access does not work at all to a pre-V4R4 system. You'll be fairly limited in what you can use in VB.NET (if it works) to a V3R2 box due to the server not having all the support that VB.NET requires of a ADO.NET provider. Regards, Brent Nelson