Append Data to Table, Access VBA

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:

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


How To?

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

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:
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:

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

See also:

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

Leave a Reply

Your email address will not be published. Required fields are marked *