MS Access-AS/400


Active Member
We have MS Access Application on PC and want to access Database on AS/400
(OneWorld B7332) to validate some information for e.g. When Item number is
entered on one form, it should validate it with OneWorld table on as/400.
I have defined ODBC connection. After that????

Any documentation,ideas ..

TIA (Thanks In Advance)..right zoltan.


Get Your Private, Free E-mail from MSN Hotmail at


Legendary Poster
Hi Venkat,
Welcome again here!

"After that????"
... after that I do not see your system configuration informations!
OK, I will give it up and accept that your system configuration informations are TOP SECRET.

Go back to your issue.
I have read your update mail too.
(... by the way why have you sent it in a new mail instead of answering (replying) your original via e-mail or on the Forum? This way you originated two separate thread for the totally same issue on the Forum!!!!)

Ok, you have linked the table into Access.
What is your problem a bit more closer?
Don't you see the records in Access?
Don't you see the values in Access?
Is something "In Access Ible"?
What do you want to validate?
How do you want to do it?
... Do you looking for a professional "thought-reader"? :)))

Please, make a bit easier for us to help you! TIA

Best regards,
P.S.: Thanks for your confirmation to my decoding of TIA!

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)


Active Member
This way you originated two separate thread for the totally same issue on
the Forum!!!!)
**I was not aware of this..Thanks..will take care hereafter.

Now to be specific :-
I have established connection through link tables and able to access records
Requirement:- client don't want to show up link's established since people
around here go and play with access tables and in turn live data.
I have suggested them to establish database securities but they want to find
out way to establish connection through program and make use of it.

I went through ADO (Access Data Object) documentation to figure out how to
do it. Still searching..I got the feel that we can establish connection to
ODBC source through program..How?? Yes.working on it.

SO, If anybody has done this within ms-access using ADO or Jet Engine
Connection,recordset etc..(without making use of link table) Please forward
the details..!!

OneWorld Xe,AS/400-DB2 database.
Get Your Private, Free E-mail from MSN Hotmail at


Legendary Poster

here's some code pulled from an Access Report I wrote recently. The report/code's purpose was a quick & dirty update of the Leadtime Offset field in the Bill of Material table (F3002) based on a rather complicated set of criteria - which is why Access was used rather than RDA. Hope this can help.

before entering the code you have to reference "Microsoft ActiveX Data Objects 2.1 Library" (on the VB screen select 'Tools' - 'References').
Option Compare Database
Dim msSQLConnect As String
Dim msSQL As String
Dim mrsF3002 As ADODB.Recordset
Public gdbConn As ADODB.Connection

Private Sub Report_Open(Cancel As Integer)
' Open OLEDB Connection to Oracle
Dim sErrormsg As String

Set gdbConn = New ADODB.Connection
With gdbConn
'below uses OLEDB driver for Oracle as the provider, not ODBC
.Provider = "MSDAORA"
.CursorLocation = adUseClient
.ConnectionTimeout = 20 ' die if more than 20 secs
.Open "Password=" & "XXXX" & ";User ID=" & "XXXX" & ";Data Source=XXXXX;"
End With
'Create Recordset Object
Set mrsF3002 = New ADODB.Recordset

End Sub

Private Sub Report_Close()
Set mrsF3002 = Nothing
Set gdbConn = Nothing
End Sub

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
'Note no error checking below, assumes row exists
'Update/Set LT Offset (LOVD) to -1

msSQL = msSQL & " IXKIT = " & CStr([IXKIT])
msSQL = msSQL & " AND IXTBM = '" & [IXTBM]
msSQL = msSQL & "' AND IXMMCU = '" & [IXMMCU]
msSQL = msSQL & "' AND IXCPNT = " & CStr([IXCPNT])
msSQL = msSQL & " AND IXSBNT = " & CStr([IXSBNT])
msSQL = msSQL & " AND IXBQTY = " & CStr([IXBQTY])
msSQL = msSQL & " AND IXCOBY = '" & [IXCOBY] & "'"
mrsF3002.Open msSQL, gdbConn, adOpenDynamic, adLockPessimistic, adCmdText
mrsF3002.Fields!IXLOVD = -1
End Sub

Larry Jones
[email protected]
OneWorld B733.1, SP 11.3
HPUX 11, Oracle SE 8.1.6
SandBox: OneWorld XE
Call Orchestrations From Excel – The Easy Way to Make the Orchestrator Work for You.