Saturday, March 07, 2009

How to update an Access Table using VBA

How to update an Access Table using ADO

The following code snippet would be helpful to update an Access 2007 database table using VBA. The code uses ADO and requires a reference to ActiveX Data Objects Library

The sample uses a simple table which contains a name and a location field.

The code uses the SQL update query to update the database. The query is executed by the ADO’s command execute method

Sub Simple_SQL_Update_Data()

Dim Cn As ADODB.Connection '* Connection String

Dim oCm As ADODB.Command '* Command Object

Dim sName As String

Dim sLocation As String

Dim iRecAffected As Integer


Set Cn = New ADODB.Connection

Cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\comp\Documents\SampleDB.accdb;Persist Security Info=False"

Cn.ConnectionTimeout = 40


sName = "Krishna Vepakomma"

sLocation = "Cincinnati, OH"

Set oCm = New ADODB.Command

oCm.ActiveConnection = Cn

oCm.CommandText = "Update SampleTable Set Location ='" & sLocation & "' where UserName='" & sName & "'"

oCm.Execute iRecAffected

If iRecAffected = 0 Then

MsgBox "No records inserted"

End If

If Cn.State <> adStateClosed Then


End If

Application.StatusBar = False

If Not oCm Is Nothing Then Set oCm = Nothing

If Not Cn Is Nothing Then Set Cn = Nothing


If Err <> 0 Then

Debug.Assert Err = 0

MsgBox Err.Description


Resume Next

End If

End Sub

Table Before Update Command

Table After Update Command


  1. Neat routine, congratulations Shasur :-)

  2. Anonymous10:54 AM

    For stable connections to database I prefer calling a query stored in the database. For me it has worked better!

  3. Anonymous8:36 AM

    i used the code to update the access2003
    but it can't work in access2007
    i've opened the "MIcrosoft ActiveX Data Objects 2.6 Library"

    Private Sub CommandButton4_Click()
    Set conn = CreateObject("adodb.connection")
    Set rst = CreateObject("adodb.recorset")
    conn.Open "provider=microsoft.jet.oledb.4.0;;data source=" & ThisWorkbook.FullName

    Sql = "..skip.."

    conn.Execute Sql
    Set conn = Nothing

    End Sub

  4. Can you please post the error and the line in which you are getting the error.


  5. Anonymous8:18 AM

    what would the syntax be if you wanted to update two columns on the same "where" statement?

  6. What would be the correct syntax so I could use two locations followed by where. i.e.. I want to update two columns using one where statement.

  7. Hi cody

    Update Database1
    Set ColumnName1 = Value1, ColumnName2 = Value2 where ColumnName3 = Value3

    should solve your problem

  8. Hi, how would you do vice versa of this? I mean, with a criteria, you'll query from Access and show it in a sheet in Excel? Thanks!


Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO Google Group