VBA – Populate data in Access table with VBA

Sometimes you may need to populate data in Access table with VBA. This is actually quite useful, when you are building VBA Access tools. In the current example I will use hardcoding for the data entries, which are to be populated,  but later, you may change them into variables.

So, what do we need as prerequisites? First things first, go to VB Editor > Tools > References and add the library Microsoft ActiveX Data Object as in the screenshot:

Once you have done this, you may create your table in Access, looking like this:

You need just 4 columns – ID (auto), StudentNumber, StudentName and Grade. Once the table is ready, fill in the code to populate the two entries.

Here it goes:

Option Compare Database

Sub DataPopulation()

    Dim myConn As New ADODB.Connection
    Dim DBS As ADODB.Recordset

    Set myConn = CurrentProject.Connection
    Set DBS = New ADODB.Recordset

    DBS.Open "VitoshAcademy", myConn, adOpenKeyset, adLockOptimistic

    DBS.AddNew
    DBS!StudentNumber = 1
    DBS!StudentName = "Vitosh"
    DBS!Grade = 10

    DBS.AddNew
    DBS!StudentNumber = 2
    DBS!StudentName = "Doynov"
    DBS!Grade = 10
    
    DBS.Update
    DBS.Close
    
    Set DBS = Nothing
    Set myConn = Nothing
    
End Sub

🙂