• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

When to update a counter field in a table

Occasionally I have to write a program that uses a field to keep track of the last counter value used to create an invoice number, key field value, etc.   As I loop through a recordset and generate the next value, I always update the field stored in a sql table each time I increment it.

Is it better to update a variable as I loop through a recordset or whatever, then update the database field at the end of the process?  I never felt comfortable doing that.

I’m converting an Access application to VB keeping the Access tables.  It is really slow as it is and sometimes takes hours to run.  Am I reducing overhead much if I update the counter field at the end rather than as I go?

  • 2
  • 2
2 Solutions
Can you show us some code? There should not be a problem updating the db at the end as otherwise you are connecting to DB in each iteration of the loop.
Delta7428Author Commented:
I think you already answered my question CC.  I've always suspected it's best to update at the end rather than all that trashing on the database as you go.  But they've been rather small datasets in the past and I guess I was concerned more about a system crash or something and that value not being updated.

Here's some sample code from a vb program.
Sub ReadOrders(lAsn As Long)
    Dim rcOrder As ADODB.Recordset
    Dim sOrder As String
            '---------Get list of orders per current ASN
            Set rcOrder = New ADODB.Recordset
            sqry = "SELECT BOXLOG.ORDER_NO, BOXLOG.ASN " & _
            "From BOXLOG " & _
            "Where (((BOXLOG.ASN) = " & lAsn & ")) " & _
            rcOrder.Open sqry, consql, adOpenStatic, adLockOptimistic
            While Not rcOrder.EOF
            '--------Get list of detail line mo's per current order
                sOrder = GetNextOrderNo + 1
                Call WriteToTextFile(sOrder, lAsn)
End Sub

Sub UpdateCounter (sOrder)
     Dim sqry as string
     sqry = “Update OrderCounter set LastCounter = ‘” & sOrder & “’”
     Consql.execute sqry
End Sub

Open in new window

Are you using VB6 or VB.NET? It depends on how big a problem this would be if the system does crash and you lose the counter vs the performance impact.
Delta7428Author Commented:
I think I've settled on VB6 instead of .net for various reasons but I can do it in either.  

Another factor with the project is that I suspect user activity is a factor in the inconsistency in the performance of the existing macro program.

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now