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 ReferenceThis 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
Posts
