[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


How to commit a database update in VBA code

Posted on 2007-07-26
Medium Priority
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

mbizup earned 100 total points
ID: 19579261

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

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 100 total points
ID: 19579269
Basically .... you can use

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


Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 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