I am pulling data from our warehouse weekly with an agent, but the agent doesn’t delete existing docs before pulling the new ones - causing dupes each week. How can I set this code to clear the ADMINOFFS view before loading the new forms? Thank you.
Sub Initialize
Dim session As New NotesSession
Dim db As NotesDatabase, codedb As NotesDatabase
Dim con As ODBCConnection
Dim qry As ODBCQuery
Dim res As ODBCResultSet
Dim idView As NotesView, codeview As NotesView
Dim doc As NotesDocument, iddoc As NotesDocument
Dim max As Double, id As String
Dim i As Double, j As Double
Dim bigArray() As String
Dim answer As Variant
Dim newone As String
Set db = session.CurrentDatabase
Set idView=db.getView("ADMINOFFS")
’ warehouse control
Dim wh_db As New NotesDatabase("","")
Dim wh_doc As NotesDocument
Dim wh_view As notesview
Set wh_db=session.getdatabase("DDAS2/Notes","warehousctacs.nsf")
Set wh_view=wh_db.getview("CT1")
Dim c1 As String, c2 As String, c3 As String, c4 As String
Set wh_doc = wh_view.Getfirstdocument
If Not wh_Doc Is Nothing Then
c1 = wh_doc.ColumnValues(0)
c2 = wh_doc.ColumnValues(1)
c3 = wh_doc.ColumnValues(2)
c4 = wh_doc.ColumnValues(3)
End If
Set con = New ODBCConnection
Set qry = New ODBCQuery
Set res = New ODBCResultSet
Call con.connectTo(c1,c2,c3,c4)
Set qry.Connection = con
'Get Codes
qry.SQL = "select distinct zz_adv_table.adv_table_code, zz_adv_table.adv_short_desc, allocation.alloc_division from zz_adv_table, allocation where adv_table_type = 'J6' and allocation.alloc_division = zz_adv_table.adv_table_code order by adv_table_code"
Set res.Query = qry
res.Execute
res.LastRow
max = res.NumRows
For j = 1 To max
Set doc = db.CreateDocument
doc.Form = "ADMINOFFS"
res.CurrentRow = j
doc.Code = res.GetValue("adv_table_code")
doc.Desc = res.GetValue("adv_short_desc")
doc.AUnit = res.GetValue("alloc_division")
Call doc.Save(False,False)
Next
res.Close(DB_CLOSE)
End Sub