How to Insert Data to an Access 2007 Database Table using Excel VBA

VBA code for inserting data to Access 2007 database

Inserting data to Access database can be performed from VBA using ADO commands. The following code uses ActiveX Data Objects (ADO) to insert data to an Access 2007 database (accdb)

The code needs a reference to Microsoft ActiveX Data Objects library

Microsoft ActiveX Data Objects library Reference


This example uses a sample table with three fields (ID – autogenerated one , Name, and Location)

Sub Simple_SQL_Insert_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 = "Narayanaguda"

Set oCm = New ADODB.Command

oCm.ActiveConnection = Cn

oCm.CommandText = "Insert Into SampleTable (UserName, Location) Values ('" & sName & "','" & sLocation & "')"

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

The above code uses ADO’s command object to execute the insert query.

Access Table with Updated Values