Deleting Data Via ADO

[c68]

I have a process of uploading a set of data from a spreadsheet to an Access table. Before I upload anything, I want to make sure that the records that appear on spreadsheet do not exist in the database. I use the routine below to conduct this process. The routine does work, but it takes a very long time. Can anyone suggest a different method? Your help is much appreciated. Thank you.

Dim rst As Object
Dim cn As Object
Dim i As Integer
Dim lstCell As Long

lstCell = [a65536].End(xlUp).Row

Application.ScreenUpdating = False

Set rst = CreateObject("ADODB.Recordset")
Set cn = CreateObject("ADODB.Connection")

With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = tmPath
.Open
End With

For i = 2 To lstCell
With rst
.Open "Select * From Returns Where Port_ID='" & Cells(i, 1) & "' And As_Of_Date=#" & _
Cells(i, 2) & "# And Tier=" & Cells(i, 9), cn, 1, 2
Do While Not .EOF
.Delete
.MoveNext
Loop
.Close
End With
Next

cn.Close
Set rst = Nothing
Set cn = Nothing

Application.ScreenUpdating = True

 

[dbrown14]

I believe the constant opening and closing of 'rst' is slowing your program down. Try opening 'rst' once putting the data in an array then using the array in place of 'rst.'

 

[c68]

Unfortantely, I don't know how to do that. Will you be able to help me out with that? Thank you.