Very Slow ADO

[c68]

I have a process of updating a table from an Excel file. Before I upload anything to the table, I want to make sure that I do not have the same data already in the database based on values that appear in column A, B and I of the spreadsheet. If the data points do exist, then they are deleted. The routine below examines each row vs. the table one by one. As a result, the process takes a long time. Someone has suggested that I place those values from column A, B, and I in arrays and examine the database that way. I don’t have much experience with arrays to go about this process. Can someone please help me? Your help is much appreciated. Thank you.


Sub DeleteIfReturnsAlreadyExist()
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
End Sub

 

[xenou]

Possibly setting a unique (composite) index in the access table would work -- if I understand the constraint correctly. then you just run the insert and let Access reject the duplicates.

 

[c68]

I keep getting an error because of this: Format(As_Of_Date,"yyyymmdd")

 

[c68]

I keep getting an error because of this: Format(As_Of_Date,"yyyymmdd")

 

[c68]

Thanks Xenou. Unfortnately I can't do that since the values for the three criterias maybe subject to changes.

 

[xenou]

Can you provide a little bit of sample data to show how this works? Is the database a permanent data store (you keep adding to it) or a temporary data store (you use it with the data in the workbook for a while then start again with a new dataset)?

 

[BarCodeWiz]

Your delete statement should basically look something like this (like your SELECT statement). It only affects the data when all criteria are met.

"DELETE From Returns Where Port_ID='" & Cells(i, 1) & "' And As_Of_Date=#" & _
Cells(i, 2) & "# And Tier=" & Cells(i, 9)

 

[c68]

Thank you everyone. I made a slight adjustment to Richards SQL and everything worked out.

strSQL = "DELETE * FROM Returns WHERE (Port_ID & As_Of_Date & Tier) In(" & vArr & ")"

 

[BarCodeWiz]

Perfect