odbc - ADO to connect to as/400

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

Check out This MSKB Article

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

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

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./

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.

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

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

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}

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

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?

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