Macro to fetch AS400 data

[#1]

Hi all,

Does anyone have a working macro (not load script) that fetches data from an AS400? I need to collect a few bits of simple data dynamically for a user selection from a unique key within a single PF on the AS400 but have read things on ODBC vs ADO that make me question the approach.

I really would appreciate a working model rather than trying to re-invent the wheel.

Regards,

Gordon

[#2]

If of interest to anyone, here is my macro. The user will input a value using an input box tied to a variable vWSO and get the values
of 2 fields returned through the variables vJNSLSM and vJNSRNO.
Using 'Insert/Update' you should also be able to share data dynamically between users and a document....

sub GetData

' Fetch selection
set doc=ActiveDocument
vWSO = doc.Variables("vWSO").GetContent.String

' Open connection using System DSN
set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = "DSN=AS400;Uid=myuserid;Pwd=mypassword;"
conn.Open

' Create recordset (only one row will be returned as JNWORD = unique key)
query = "SELECT JNSLSM, JNSRNO FROM S0F040 WHERE JNWORD = " & vWSO
set rs = CreateObject("ADODB.Recordset")
set rs.ActiveConnection = conn
rs.Open query

' If successful set Qlikview variables to values in 1st row of recordset (1st field = 0) else initialise them
if conn.Errors.Count = 0 and not rs.EOF then
doc.Variables("vJNSLSM").SetContent rs.Fields(0).Value, false
doc.Variables("vJNSRNO").SetContent rs.Fields(1).Value, false
else
doc.Variables("vJNSLSM").SetContent "", false
doc.Variables("vJNSRNO").SetContent "", false
end if

' Tidy up
rs.Close
set conn = Nothing
set rs = Nothing

end sub

Regards,

Gordon