EXCEL中利用VB实现自动录入ACCESS数据库中提示报错

hint60

Sub WriteRowToDatabase()
    Dim conn As Object
    Dim connectionString As String
    Dim excelRow As Range
    Dim cellValue As Variant
    Dim columnCount As Integer
    Dim query As String
    Dim i As Integer

    ' 设置数据库连接字符串，这里以Access为例
    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\database.accdb;Persist Security Info=False;"

    ' 设置要写入的Excel行
    Set excelRow = ThisWorkbook.Sheets("Sheet1").Rows(1)
    ' 假设是第一行

    ' 初始化连接对象
    Set conn = CreateObject("ADODB.Connection")
    conn.Open connectionString

    ' 获取查询字符串，这里假设表名为yourTable，且有两个字段field1和field2
    query = "INSERT INTO yourTable (field1, field2) VALUES ('"

    ' 遍历Excel行中的单元格，构建查询字符串
    columnCount = excelRow.Cells.Count
    For i = 1 To columnCount
        cellValue = excelRow.Cells(1, i).Value
        query = query & Replace(cellValue, "'", "''") & "', '"
    Next i

    ' 移除行尾的逗号并添加结束括号
    query = Left(query, Len(query) - 2) & "')"

    ' 执行查询
    conn.Execute query

    ' 关闭连接
    conn.Close
    Set conn = Nothing

    MsgBox "数据已写入数据库"
End Sub






运行后提示报错，有大佬能帮忙修改下呗

f8b1987

  : query : "INSERT INTO yourTable (field1, field2) VALUES ('123', 'df', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '"

你这字段输入超出限制了吧，不调试看看变量？

你最终把query更正为

1. query = "INSERT INTO yourTable (field1, field2) VALUES ('123', 'df')"

复制代码

写入就正常了。
至于循环结束后，怎么想办法去掉query多余的内容，你自己想想了

ZHLK

Sub WriteRowToDatabase()
    Dim conn As Object
    Dim connectionString As String
    Dim excelRow As Range
    Dim cellValue As Variant
    Dim columnCount As Integer
    Dim query As String
    Dim i As Integer
   
    ' 设置数据库连接字符串，这里以Access为例
    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\database.accdb;Persist Security Info=False;"
   
    ' 设置要写入的Excel行
'    Set excelRow = ThisWorkbook.Sheets("Sheet1").Rows(1)
    ' 假设是第一行
   
    ' 初始化连接对象
    Set conn = CreateObject("ADODB.Connection")
    conn.Open connectionString
   
    ' 获取查询字符串，这里假设表名为yourTable，且有两个字段field1和field2
    query = "INSERT INTO yourTable (field1, field2) VALUES ('"
   
    ' 遍历Excel行中的单元格，构建查询字符串
    columnCount = Sheet1.Range("a1").End(xlToRight).Column
    For i = 1 To columnCount
        cellValue = excelRow.Cells(1, i).Value
        query = query & Replace(cellValue, "'", "''") & "', '"
    Next i
     
    ' 移除行尾的逗号并添加结束括号
    query = Left(query, Len(query) - 3) & ")"
Debug.Print query
    ' 执行查询
    conn.Execute query
   
    ' 关闭连接
    conn.Close
    Set conn = Nothing
   
    MsgBox "数据已写入数据库"
End Sub