• Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

MS Access-AS/400

vliyer

Active Member
Hi,
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.

regards,
venkat.

_________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
 

Zoltan_Gyimesi

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,
Zoltán
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)
 

vliyer

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

TIA,
Regards,
Venkat.
OneWorld Xe,AS/400-DB2 database.
_________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
 

Larry_Jones

Legendary Poster
Venkat,

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()
gdbConn.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 = "SELECT IXLOVD FROM PRODDTA.F3002 WHERE "
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] & "'"
gdbConn.BeginTrans
mrsF3002.Open msSQL, gdbConn, adOpenDynamic, adLockPessimistic, adCmdText
mrsF3002.Fields!IXLOVD = -1
mrsF3002.Update
gdbConn.CommitTrans
mrsF3002.Close
End Sub




Larry Jones
ljones@wagstaff.com
OneWorld B733.1, SP 11.3
HPUX 11, Oracle SE 8.1.6
SandBox: OneWorld XE
 
Top