VB6. Proceesing ADODB Table Is there a better way of doing this?

Question

I am using .Open to check if record exists. If it exists, I delete it. If not, I an adding it. Then I close the ADODB Recordset.

I am sure there is a better way of doing this - and this is probably a slow way of doing it. Is there a way of doing this with only one Open and One close?

Here is my code (which is in a Do Loop):

  Dim myRecSet As New ADODB.Recordset
        Dim strSql As String
        strSql = "select * from RentBalances where KeyTcyIdSubAcDate = '" & sKeyTcyIdSubAcDate & "'"
        'Display "SQL: " & strSql
        myRecSet.Open strSql, SQLSVSExtractConnection, adOpenKeyset, adLockOptimistic
        'Display "Total no of records = " & myRecSet.RecordCount
        If myRecSet.RecordCount < 1 Then
            'Display ("There are no RentBalances record for this ID. ID = " & sKeyTcyIdSubAcDate)
        Else
            ' delete the record

            myRecSet.Delete
            myRecSet.UpdateBatch
        End If

        myRecSet.AddNew
        myRecSet!KeyTcyIdSubAcDate = rsLocal.Fields("KeyTcyIdSubAcDate")
        myRecSet!KeyTcyId = rsLocal.Fields("KeyTcyId")
        myRecSet!SubAc = rsLocal.Fields("SubAc")
        myRecSet!PeriodEndDate = rsLocal.Fields("PeriodEndDate")

        myRecSet!Amount = rsLocal.Fields("Amount")
        myRecSet!RentAmount = rsLocal.Fields("RentAmount")
        myRecSet!ChargesAmount = rsLocal.Fields("ChargesAmount")
        myRecSet!AdjustmentAmount = rsLocal.Fields("AdjustmentAmount")
        myRecSet!BenefitAmount = rsLocal.Fields("BenefitAmount")
        myRecSet!BenefitBalance = rsLocal.Fields("BenefitBalance")
        myRecSet!TenantBalance = rsLocal.Fields("TenantBalance")
        myRecSet!PayAmount = rsLocal.Fields("PayAmount")
        myRecSet!TimeStamp = rsLocal.Fields("TimeStamp")
        myRecSet!UpdateFlag = rsLocal.Fields("UpdateFlag")
        myRecSet.Update
        myRecCount = myRecCount + 1
        myRecSet.Close

A) You should not concatenate strings to build SQL queries. B) Why delete and then insert instead of just updating? — UnhandledExcepSean, Apr 30 '15 at 15:55
A) Because of SQL injection B) Because it is two transactions and not one — UnhandledExcepSean, Apr 30 '15 at 16:00
A) No chance B) I am checking if record exists first. It may not. — Steve Staple, Apr 30 '15 at 16:05
A) Show me how to do it properly. B) What happens if I use Update and the record is not there? — Steve Staple, Apr 30 '15 at 16:38

score 0 · Answer 1 · answered Apr 30 '15 at 17:00

0

The most optimal way of doing this is to bulk insert into a staging table from your code and then call a stored procedure to merge the data from your staging table into your proper table.

answered Apr 30 '15 at 17:00

UnhandledExcepSean

12,504
2
35
51

VB6. Proceesing ADODB Table Is there a better way of doing this?

1 Answers1