Creating Docs with an Agent

I want to create an agent that uses an ODBC connection to get a Qry, Then for every record in the Qry create a new document in the database and populate the fields with the  Qry's current row.

I know how to set up the ODBC and get the backend data. I do not know how to create a new document, populate it's fields, then loop to create another new document. What would the code look like to do this?

I really just need to see a simple example of an agent looping and creating a new document each pass.
Who is Participating?
Simon_HendryConnect With a Mentor Commented:
Here is a piece of skeleton code for odbc data extraction in to notes documents

----- CODE STARTS ------
' Specify this in options section of Code - it is required to load the ODBC 'Lotuscript Conduits for the ODBC Classes
Uselsx "*LSXODBC"

Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
Dim OurDB As NotesDatabase
Dim NewDocument As NotesDocument

Set OurDB As New NotesDatabase("OurServerName","Members.nsf")
' Connect to the datasource
If Not con.ConnectTo("OurODBCSource") Then
Messagebox "Could not connect to Members",, "Error connecting"
Exit Sub
End If
' Set up the odbc objects
Set qry.Connection = con
Set result.Query = qry

' make a query for all of the data in the Members table
qry.SQL = "SELECT * FROM Members"

' and execute the query - and wait for the result set to fill up with data

' Now roll through each row of the result set and create a new document in our database for each row in the table
If result.IsResultSetAvailable Then

' Create the new document and load up the fields with data from the current row of the result set
Set NewDocument As New NotesDocument(OurDB)
NewDocument.Form = "New Member"
NewDocument.MembersName = result.GetValue("Name")
NewDocument.MembersAge = result.GetValue("Age")
NewDocument.MembersAddress = result.GetValue("Address")
Call NewDocument.Save(True,False)

Loop Until result.IsEndOfData

' dump the memory for the result set

' if we didn't get any data then inform the interactive user
Messagebox "No data retrieved for Members",, "No data"
Exit Sub
End If

' Close the connection to the ODBC Source

End Sub
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.