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

 

[Fazza]

hi,

It isn't clear what is being done. Where is the database? Where is the code?

I guess the database is in an Excel file - otherwise there'd be a DELETE query.

If you're working in Excel, I wonder if it might be suitable to have an UPDATE query to tag the records to be deleted. Then sort the table on that tagged field & delete the unwanted records in one step.

regards

 

[BarCodeWiz]

I agree with Fazza. Also, I don't think you should be opening a recordset for each row (or, at all). I think this is what takes up most of your processing time. Just use an UPDATE or a DELETE statement if you can.

 

[c68]

Hi,

It's an Access database updating via Excel. My query examines the database one row at a time and therefore it takes a long. Clearly, it's not the most effecient method. I don't know how to create arrays for those values that appear in column A, B, and I so I can delete the matching values in database at once. I hope that this makes it clear. Thank you.

Regards,
c68

 

[Richard Schollar]

Hi

You can use a delete query like the following:

Dim comm As Object
Dim conn As Object
Dim vArr As Variant
Dim strSQL As String
Set comm = CreateObject("ADODB.command")
Set conn = CreateObject("ADODB.Connection")
vArr = "'" & Join(Application.Transpose(Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value), "','") & "'"
With conn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = tmpath
    .Open
End With
strSQL = "DELETE * FROM Returns WHERE Port_ID In(" & vArr & ")"
comm.activeconnection = conn
comm.CommandText = strSQL
comm.Execute
Set comm = Nothing
Set conn = Nothing

 

[c68]

Thank you Richard. I added two more criteria to your code & now I'm getting a "Run-time error." The modified code is pasted below
Dim comm As Object
Dim conn As Object
Dim vArr As Variant
Dim strSQL As String
Set comm = CreateObject("ADODB.command")
Set conn = CreateObject("ADODB.Connection")

vArr = "'" & Join(Application.Transpose(Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value), "','") & "'"
vArr2 = "#" & Join(Application.Transpose(Range("B2:B" & Cells(Rows.Count, "A").End(xlUp).Row).Value), "','") & "#"
vArr3 = Join(Application.Transpose(Range("I2:I" & Cells(Rows.Count, "A").End(xlUp).Row).Value), "','")

With conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = tmPath
.Open
End With
strSQL = "DELETE * FROM Returns WHERE Port_ID In(" & vArr & ")" & " And As_Of_Date In(" & vArr2 & ")" & _
" And Tier In(" & vArr3 & ")"

comm.activeconnection = conn
comm.CommandText = strSQL
comm.Execute
Set comm = Nothing
Set conn = Nothing

 

[c68]

Hi Richard. I got it work. Thank you so much for your help. Much, much appreciated.

Dim comm As Object
Dim conn As Object
Dim vArr As Variant
Dim strSQL As String
Set comm = CreateObject("ADODB.command")
Set conn = CreateObject("ADODB.Connection")

vArr = "'" & Join(Application.Transpose(Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value), "','") & "'"
vArr2 = "#" & Join(Application.Transpose(Range("B2:B" & Cells(Rows.Count, "A").End(xlUp).Row).Value), "#,#") & "#"
vArr3 = Join(Application.Transpose(Range("I2:I" & Cells(Rows.Count, "A").End(xlUp).Row).Value), ",")

With conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = tmPath
.Open
End With
strSQL = "DELETE * FROM Returns WHERE Port_ID In(" & vArr & ")" & " And As_Of_Date In(" & vArr2 & ")" & _
" And Tier In(" & vArr3 & ")"

comm.activeconnection = conn
comm.CommandText = strSQL
comm.Execute
Set comm = Nothing
Set conn = Nothing

 

[Richard Schollar]

You're wlecome

Did it significantly increase the speed of execution?

 

[c68]

A million times faster.

As a test, I changed the value of a "Tier" on the spreadsheet to a different value, but that record set was deleted anyway in the database. I'm not sure why though. Do you think that my conditions set in strSQL is wrong? All three conditions must be true before deleting any record. Thanks.

 

[Richard Schollar]

So the record to be deleted must satisfy A2 & B2 & C2? The problem with the way the SQL is currently written is that the record just needs to match ANY value in A column, any value in B column, and any value in C column. If that is the case then I think you will need to generate a unqiue key to use (eg in D column along the lines of:

=A2 & TEXT(B2,"yyyymmdd") & C2

and then change the SQL to pick up this key for the WHERE caluse and write it like this:

DELETE * FROM Results WHERE (Port_ID & Format(As_Of_Date,"yyyymmdd") & Tier) In(" & vArr & ")"

if that makes sense? This will hit performance though as it is a slower query to execute.