In this article I will explain how you can append data to an Access table using VBA.

The article uses the ADODB.Recordset object. In order to use this object you may need to add reference to ADODB object library. For more information on this topic please see the link below:

• VBA Access Recordset, Object Library

Values are appended using the Recordset.AddNew method. For more information about this method please see the link below:

• Recordset.AddNew Method VBA Access

How To?

Assume we have a table with the name “MyTable1” with the following fields:

As you can see the table has 4 fields:

• MyField1
• MyField2
• MyField3
• MyField4

The following code will initiate an ADODB.Recordset object for the table:

Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset
'initated recordset obejct
objRecordset.ActiveConnection = CurrentProject.Connection
Call objRecordset.Open("MyTable1", , , adLockBatchOptimistic)


Note: “MyTable1” is the name of the table we want to append the new record to.

New records are added by using the Recordset.AddNew method. The code below will add a new record to the table:

Sub Example1()
Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset
Dim arrFieldList(0 To 3) As Variant
Dim arrFieldValues(0 To 3) As Variant

'initated recordset obejct
objRecordset.ActiveConnection = CurrentProject.Connection
Call objRecordset.Open("MyTable1", , , adLockBatchOptimistic)
'set field array
arrFieldList(0) = "MyField1"
arrFieldList(1) = "MyField2"
arrFieldList(2) = "MyField3"
arrFieldList(3) = "MyField4"
'set value array
arrFieldValues(0) = 10000
arrFieldValues(1) = 1100000
arrFieldValues(2) = "ranomd text"
arrFieldValues(3) = "more random text"
'add new record
Call objRecordset.AddNew(arrFieldList, arrFieldValues)
'update the recordset object
objRecordset.UpdateBatch
End Sub


Result:

Note: If you are working directly with an Access table, the values displayed in the table will not update automatically. One method to overcome this would be to press “F5”. For more information on this issue please see the link below:

• VBA Access Table Not Updating

You can download the file and code used in this article from the link below:

• AddNew.Zip

See also:

• Microsoft MSDN: AddNew Method (ADO)
• Modify Existing Data in Table, Access VBA Recordsets
• Read Data from Table, Access VBA
• Microsoft MSDN: Recordset Object (ADO)
• Delete Record, Access VBA Recordset
• Recordset.AddNew Method VBA Access

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com