Manage Learn to apply best practices and optimize your operations.

Export From Notes To Access

How to export data from Notes to Access. This agent code allows you to export
brand new Notes docs to Access as well as docs that have been edited in Notes
which need to overwrite the current Access equivalent.
Options Event:
Option Public
Uselsx "*lsxodbc"

Initialize Event:
'The following agent pushes both BRAND NEW docs and EDITED docs from Notes
to Access.
' Write to the Contracts table in Access from the Pre Contract Data Sheet
in Notes.
Dim ses As New NotesSession
Dim curdb As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument

Dim Con As New ODBCConnection
Dim Qry As New ODBCQuery
Dim Result As New ODBCResultSet
Set Qry.Connection = Con
Set Result.Query = Qry
Con.ConnectTo("TYPE DATASOURCE HERE")
' Access table name we're pushing to and pulling from.
Qry.SQL = "SELECT * FROM Contracts"
Result.Execute

' Print a mssg to the status bar to know the connection is good.
If Con.IsConnected Then
Print "Connection to Contracts Established."
Else
Print "No Connection to Contracts Established."
Exit Sub
End If

Set curdb = ses.CurrentDatabase
' Push docs from this view.
Set view = curdb.GetView("TYPE NOTES VIEW NAME HERE")
Set doc = view.GetFirstDocument

Do Until (doc Is Nothing)
'If the value of the first (1) field (Contract_No) in the result set (ACCESS)
matches the value in the CTR_TrackingNo field in the first Notes doc in the
view (NOTES), then this doc already exists in Access so push all the following
values in Notes to Access...............
If Result.LocateRow(1, doc.CTR_TrackNo(0)) Then
Call Result.SetValue("Annuitant_Name", doc.Name(0))
Call Result.SetValue("Original_Value", doc.Qte_ActualPrice(0))
Call Result.SetValue("SCC_Contract_Maturity",
doc.PC_SccLastDueDt(0))
Call Result.SetValue("Final_Contract_Maturity",
doc.Con_Maturity(0))
' Save to Access.
Result.UpdateRow
Else
' ......Otherwise, if the value of the first (1) field (Contract_No)
in the result set DOES NOT match the value in the CTR_TrackingNo field in the
first Notes doc in the view, then this doc DOES NOT already exist in Access so
create a NEW doc in Access by pushing the following values in Notes to Access.
Result.AddRow
Call Result.SetValue("Contract_No", doc.CTR_TrackNo(0))
Call Result.SetValue("Annuitant_Name", doc.Name(0))
Call Result.SetValue("Original_Value", doc.Qte_ActualPrice(0))
Call Result.SetValue("SCC_Contract_Maturity",
doc.PC_SccLastDueDt(0))
Call Result.SetValue("Final_Contract_Maturity",
doc.Con_Maturity(0))
' Save to Access.
Result.UpdateRow
End If
Set doc = view.GetNextDocument(doc)
Loop

' Close the result set.
Result.Close( DB_CLOSE )
' Disconnect from the datasource.
Con.Disconnect

Dig Deeper on Domino Resources - Part 5

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchWindowsServer

Search400

  • iSeries tutorials

    Search400.com'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 ...

SearchDataCenter

SearchContentManagement

Close