如何通过查询MAx列并加1将excel数据导出到MS Access？

Sub ExportToAccess()
    Dim conn As Object
    Dim rs As Object
    Dim dbPath As String
    Dim strSQL As String
    Dim maxVal As Long
    
    ' 设置MS Access数据库文件路径
    dbPath = "C:\Path\To\Your\Database.accdb"
    
    ' 创建数据库连接
    Set conn = CreateObject("ADODB.Connection")
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
    
    ' 查询Max列的值
    strSQL = "SELECT MAX(ColumnName) FROM TableName;"
    Set rs = conn.Execute(strSQL)
    
    ' 获取Max列的值并加1
    maxVal = rs.Fields(0).Value + 1
    
    ' 导出数据到MS Access
    strSQL = "INSERT INTO TableName (ColumnName) VALUES (" & maxVal & ");"
    conn.Execute strSQL
    
    ' 关闭连接
    rs.Close
    conn.Close
    
    ' 释放对象
    Set rs = Nothing
    Set conn = Nothing
    
    MsgBox "数据已成功导出到MS Access。"
End Sub