Deleting Records In Notes DBases From Access/VBA

[HaydenMB]

Hi,

I have an Access DBase that records all our European shipments that exports the data to a Notes DBase that has a Web application built ontot hat.

Currenty we have to export the data from Access, delete the current Notes data before replacing it with the new output.

But I have replaced that with a dynamic link from the database that only adds, updates and deletes the information automatically in the background.

The problem is I cannot get the procedure to delete old records. I can find out which records are to be deleted and the below code does that and appends the field "My_Order_ID" to "tbl_Notes_Published_Remove". Does anybody have any idea how I can the go on to delete this?

Thanks in advance
Hayden




[blue]Private Sub [/blue]NotesDeleteUpdate()

Dim rec As ADODB.Recordset
Dim s As NotesSession
Dim db As NotesDatabase
Dim vw As NotesView
Dim doc As NotesDocument
Dim itm As NotesRichTextItem
Dim rtstyle As NotesRichTextStyle
Dim str As String
Dim var As Variant
Dim i As Integer
Dim SQL As String

[Green]'Clear out temporary table of items to be deleted[/green]
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete tbl_Notes_Published_Remove.* FROM tbl_Notes_Published_Remove;"
DoCmd.SetWarnings True

Dim cnnAccess As ADODB.Connection
Set cnnAccess = Application.CurrentProject.Connection

[green]'Open Lotus notes database and scroll through[/green]

Set s = CreateObject("Lotus.NotesSession")
s.Initialize myPassword
Set db = s.GetDatabase NotesServer, "demo\lmstest.nsf")
If Not db.IsOpen Then
db.Open
End If


Set vw = db.GetView("AccessLink")
Dim myOrder_ID As String
Set doc = vw.GetFirstDocument
While Not (doc Is Nothing)
myOrder_ID = doc.ColumnValues(0)
[green]'Ignore dodgy order ID's[/green]
If IsNumeric(myOrder_ID) = True Then

SQL = " SELECT tbl_Notes_Published_Data.IPF_Order_ID FROM tbl_Notes_Published_Data "
SQL = SQL & " WHERE (((tbl_Notes_Published_Data.IPF_Order_ID)=" & myOrder_ID & "));"


[green]'Open Access Table[/green]
Set rec = CreateObject("ADODB.Recordset")
rec.Open SQL, cnnAccess, adOpenDynamic, adLockOptimistic
If Not (rec.EOF And rec.BOF) Then
Debug.Print myOrder_ID & " found"

Else
Debug.Print myOrder_ID & " to be deleted"
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tbl_Notes_Published_Remove ( Notes_ID ) SELECT " & myOrder_ID & " AS Expr1;"
DoCmd.SetWarnings True

End If
rec.Close
End If
Set doc = vw.GetNextDocument(doc)
Wend

cnnAccess.Close
Set cnnAccess = Nothing
Set rtstyle = Nothing
Set itm = Nothing
Set rec = Nothing
Set doc = Nothing
Set vw = Nothing
Set db = Nothing
Set s = Nothing
End Sub