When to update a counter field in a table

Posted on 2011-05-03
Last Modified: 2012-05-11
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?

Question by:Delta7428
    LVL 83

    Accepted Solution

    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.

    Author Comment

    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 & ")) " & _
                "ORDER BY BOXLOG.ORDER_NO"
                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

    LVL 83

    Assisted Solution

    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.

    Author Comment

    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

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Prime numbers are natural numbers greater than 1 that have only two divisors (the number itself and 1). By “divisible” we mean dividend % divisor = 0 (% indicates MODULAR. It gives the reminder of a division operation). We’ll follow multiple approac…
    SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now