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

On Error GoTo ADO_ERROR

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

Cn.Open

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

Cn.Close

End If

Application.StatusBar = False

If Not oCm Is Nothing Then Set oCm = Nothing

If Not Cn Is Nothing Then Set Cn = Nothing

ADO_ERROR:

If Err <> 0 Then

Debug.Assert Err = 0

MsgBox Err.Description

Err.Clear

Resume Next

End If

End Sub


Table Before Update Command






Table After Update Command





8 comments:

  1. Neat routine, congratulations Shasur :-)

    ReplyDelete
  2. Anonymous10:54 AM

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

    http://vbaexcel.eu/vba-macro-code/database-connection-retrieve-data-from-database-querying-data-into-excel-using-vba-dao

    ReplyDelete
  3. Anonymous8:36 AM

    i used the code to update the access2003
    but it can't work in access2007
    why???
    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
    conn.Close
    Set conn = Nothing

    End Sub

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

    Thanks

    ReplyDelete
  5. Anonymous8:18 AM

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

    ReplyDelete
  6. Anonymous12:47 PM

    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.

    ReplyDelete
  7. Hi cody

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

    should solve your problem

    ReplyDelete
  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!

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.