Manage Learn to apply best practices and optimize your operations.

Lotus Notes/Domino 4.5, 4.6

The snippet of code below is very useful for writing ODBC scripts. It loops
through all fields in the table being accessed by your SQL statement, getting
the name of each field; if it finds a field named the same on the Notes
document being accessed by the script, it pushes the data to the relational
database after determining the type of data to push.
What does this mean? It means that you can write generic ODBC scripts, that
don't need to be changed if you add fields to your relational tables and/or
Notes forms, as long as the names of the fields are the same! It's cool
because it ignores any fields on the Notes form not specifically named the same
as fields on the Access database. Also, when the script looks in the Access
database for the corresponding record for the Notes document, if it finds a
match, it updates the match, but if not, it creates it no matter what. This
guarantees a measure of redundancy in the system, which is sometimes a great
You could place script using this technique in the QuerySave event of a form
for the Notes client, or in an agent run by the WebQuerySave event of a form,
or in an agent that loops through documents in a view, etc. .... it really
doesn't matter.
Dim tempvalue As Variant
Dim con As New ODBCConnection
Dim res As New ODBCResultSet
Dim qry As New ODBCQuery

Set qry.Connection = con
Set res.Query = qry
Set uidoc = ws.CurrentDocument
Set doc = uidoc.Document

If (con.ConnectTo("&LTYOUR DATA SOURCE>")) Then
If (doc.IsNewNote) Then
qry.SQL = "SELECT * from &LTYOUR TABLE> WHERE ..."
End If
Call res.Execute
If (doc.IsNewNote) Then
Call res.AddRow
Elseif Not(res.IsResultSetAvailable) Then
Call res.AddRow
Call res.FirstRow
End If
For n = 1 To res.NumColumns
tempname$ = res.FieldName(n)
tempvalue = doc.GetItemValue(tempname$)
If (Datatype(tempvalue(0)) <> 5) Then
If (tempvalue(0) <> "") Then
Call res.SetValue(tempname$, tempvalue(0))
End If
Call res.SetValue(tempname$,
End If
Call res.UpdateRow
Call res.Close(DB_CLOSE)
Call con.Disconnect

Dig Deeper on Domino Resources - Part 7

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.




  • iSeries tutorials's tutorials provide in-depth information on the iSeries. Our iSeries tutorials address areas you need to know about...

  • V6R1 upgrade planning checklist

    When upgrading to V6R1, make sure your software will be supported, your programs will function and the correct PTFs have been ...

  • Connecting multiple iSeries systems through DDM

    Working with databases over multiple iSeries systems can be simple when remotely connecting logical partitions with distributed ...