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
boon.
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("<YOUR DATA SOURCE>")) Then
If (doc.IsNewNote) Then
qry.SQL = "<SOME BOGUS SQL DESIGNED TO RETURN NO DATA>"
Else
qry.SQL = "SELECT * from <YOUR TABLE> WHERE ..."
End If
Call res.Execute
If (doc.IsNewNote) Then
Call res.AddRow
Elseif Not(res.IsResultSetAvailable) Then
Call res.AddRow
Else
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
Else
Call res.SetValue(tempname$,
Trim(Str(tempvalue(0))))
End If
Next
Call res.UpdateRow
Call res.Close(DB_CLOSE)
Call con.Disconnect
This was first published in November 2000