How to commit a database update in VBA code

Posted on 2007-07-26
Last Modified: 2013-11-28
How does one commit a change to the database in Access VBA code?  The problem is, I have the following code:

private Sub button_Click()

  If ([Status] = "PASSED") Then
    [Status] = "IDLE"
  End If
  Shell("my_program.exe", 1)

End Sub

and "my_program.exe" is seeing [status] = "PASSED" instead of "IDLE".

If I watch the Access form, I see [status] change to "IDLE" but the little pencil indicating an uncommitted edit remains until the whole "button_Click" sub finishes.  I need the change to be committed before "my_program.exe" runs.

I suspect there exists some method that will fix my problem along these lines:

  If ([Status] = "PASSED") Then
    [Status] = "IDLE"
    [Status].commit or Me.commit
  End If


OS: MS XP Version 2002, 5.1.2600 Service Pack 2 Build 2600
Access: Access 2003 (11.6566.8122) SP2
Database: Microsoft SQL Server 2005
Question by:hcstttam
    LVL 61

    Accepted Solution


      If ([Status] = "PASSED") Then
        [Status] = "IDLE"
        Me.Refresh  <---- Add this
      End If
    LVL 75

    Assisted Solution

    by:DatabaseMX (Joe Anderson - Access MVP)
    Basically .... you can use

    Me.Dirty = False   '** this will save a record (commit)


    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

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    731 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

    17 Experts available now in Live!

    Get 1:1 Help Now