VBA – Populate data in Access table with VBA

Posted on October 25, 2014 by

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

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

🙂

VBA – Populate data in Access table with VBA

Related posts: