Excel VBA- Automate MS-Access, Add a record from Excel into Access Table

By Ajeet Kumar

Add a record from Excel into Access Table
 
Option Explicit

Sub AppendRecord()
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim strDatabasePath As String
    Dim strConnection As String
   
    strDatabasePath = "E:\Exercise Excel\DatabaseX.mdb"
    With cnn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open strDatabasePath
    End With

    With rst
        .Open Source:="MyTable", ActiveConnection:=cnn, _
        CursorType:=adOpenDynamic, LockType:=adLockOptimistic, Options:=adCmdTable
    End With
   
    rst.AddNew
'' Ignore the Primary Key Field With AutoNumber
    With rst
        .Fields(1) = "Amitesh Kumar"
        .Fields(2) = "2/2/2017"
        .Fields(3) = 20000
    End With
   
    rst.Update
    rst.Close
   
    Set rst = Nothing
    Set cnn = Nothing
End Sub