odbc - ADO to connect to as/400

[Maddy]

Could someone help me (code) to connect to an as/400 database using ado and an odbc driver.

[Serge]

Check out This MSKB Article

[Maddy]

thanks but I have looked there and I am still confused about the connection string.
any help???

[packman]

Dim rsAS400 As ADODB.Recordset
Dim cnAS400 As ADODB.Connection


Set cnAS400 = New ADODB.Connection
Set rsAS400 = New ADODB.Recordset


'Live connection
cnAS400.ConnectionString = "DSN=AS400;DRIVER={client Access ODBC data source};SERVER=servername;DATABASE=libraryname;UID=userid;Passwowd=kmu;"


cnAS400.Open
Set rsAS400.ActiveConnection = cnAS400
rsAS400.LockType = adLockBatchOptimistic
rsAS400.CursorType = adOpenKeyset

In addition, you must set up a ODBC connection. Connection name is AS400

[oldharley]

Here is a connection string that works for me.

Set dbMyODBCDB = DBEngine(0).OpenDatabase _
("", False, False, "ODBC;DSN=AS400 ODBC;;TABLE=XXX@@XXXX.XXXXX;UID=;PWD=") 'Publishers")

tHE dsN WILL BE WHATEVER YOU NAMED IT THE xxxx@@@xxxx.xxxxx IS THE NAME OF THE DEFAULT as400 LIBRARIES AS DEFINED BY THE SYSTEM OPERATOR./

[Maddy]

hey packman,

you said that I must set up a ODBC connection.. how is this done.. I know that the driver is the name that I give the odbc within the client access... but I am confused about the DSN.

[packman]

-From the Windows atart menu
-Go to settings
-then go to control panel
-ODBC datasources and open it
-go to System DSN tab
-click the add button
-choose the driver: Client Access ODBC
-go to the General Tab and DSN should be whatever your connection DSN is (as400)
-Under the Server Tab: type in the default Library.

There are other setting. However, normally I only have to modify the afformentioned.

[Maddy]

so the DSn is the data source ... t And i know this may seem stupid but the area in the connection string.. for Driver.. what is this suppose to be...

[packman]

Thats the name of the Driver that you should have loaded on your PC. When you set up the ODBC connection it will give you a list of drivers to choose from. To ref that driver in code:
Driver = {client Access ODBC data source}

[Maddy]

i have done the code (packman) and I received this error..


[IBM][Client Access ODBC Driver (32 bit)][DB2/400] Communication Link Failure, Comm RC=0x5

here is my code

Dim connAS400 As ADODB.Connection
Dim rsAS400 As ADODB.Recordset

Set connAS400 = New ADODB.Connection
Set rsAS400 = New ADODB.Recordset
connAS400.ConnectionString = "DSN=AS400;DRIVER={client access ODBC data source};SERVER=todc88;database=altdelca.apt171la;UID=@@@@;PWD=@@@@;"
connAS400.Open
Set rsAS400.ActiveConnection = connAS400
rsAS400.LockType = adLockBatchOptimistic
rsAS400.CursorType = adOpenKeyset

[packman]

In the AS400 world the Database name is referred to as the Library. Do you have a library named "altdelca.apt171la".
Also, is that actually your pwd and userid?

[Maddy]

yes there is a library named that and the passwrod and userid are in the program.. i did not enter them here for work security purposes.

but isthe part about the driver suppose to be just like that