Exporting from EXCEL to Access with VBA

Your code doesn't compile as posted. You have your with block started inside a loop but ended outside the loop, which is invalid. You are missing an .Update call after the .AddNew. If we pass the field and the value to the .AddNew command then we don't need the .Update

I recommend using the With block to control the scope of your objects instead of setting them to nothing. Always closing before the End With. Notice I use an error handler to ensure that the close is always called. This should allow graceful destruction of your DB objects.

Here is an example that I can't test. You will likely need to do some tweaks to make it fit your environment:

Option Explicit

Public Sub AdddNewDatatoAccDb(ByVal con1 As String)

    On Error GoTo errHandler

    With ADODB.Connection
        .ConnectionString = con1
        .Open "T:\Folder1\VBA Test.accdb"
        With ADODB.Recordset
            .Open "VBAtest", .ConnectionString, adOpenDynamic, adLockPessimistic, adCmdTable
            Dim i As Long
            For i = 0 To 170
                .AddNew "Column1", Worksheets("Sheet1").Cells(i + 2, 0).Value
                .AddNew "Column2", Worksheets("Sheet1").Cells(i + 2, 1).Value
            Next i

errHandler:            
            .Close
        End With
        
        .Close
    End With

End Sub

Assuming you have MSAccess.exe (i.e., the Office software) installed, consider directly querying the Excel workbook from Access using SQL. No loops required.

Below include two different ways both aliasing to t and assumes headers are in specified range. You can save them as stored queries in Access for later use and even incorporate in append queries.

SELECT t.*
FROM [Excel 12.0 Xml;HDR=Yes;Database=C:\Path\To\Workbook.xlsx][Sheet1$O2:O170] AS t;

SELECT t.*
FROM [Sheet1$O2:O170] As t IN 'C:\Path\To\Workbook.xlsx'[Excel 12.0 Xml;HDR=Yes];

Option Explicit

Sub AdddNewDatatoAccDb()

    Const DB = "T:\Folder1\VBA Test.accdb"
    Const TABLE = "VBAtest"

    Dim cn As ADODB.Connection, rs As ADODB.Recordset
    Dim ar, i As Integer
    Dim fields(1), values(1)
    
    Set cn = New ADODB.Connection
    With cn
        .ConnectionString = "Provider=Microsoft.ace.OLEDB.12.0;Data Source=" & DB
        .Open
    End With

    Set rs = New ADODB.Recordset
    rs.Open TABLE, cn, adOpenDynamic, adLockPessimistic, adCmdTable

    ar = Worksheets("Sheet1").Range("O2:P172").Value
    fields(0) = "Column1"
    fields(1) = "Column2"

    For i = LBound(ar) To UBound(ar)
        values(0) = ar(i, 1)
        values(1) = ar(i, 2)
        rs.AddNew fields, values
    Next i
   
    Set rs = Nothing
    cn.Close
    MsgBox i - 1 & " records inserted", vbInformation
End Sub