mysql - 使用vba将excel数据导入mysql数据库

Question

我正在尝试将数据导入 MySQL 数据库，我搜索了许多示例和解决方案，但它不起作用，我不知道为什么。下面是我的vba代码。我收到运行时错误 2147217900(80040e14)，表示您的 sql 语法有错误；查看与您的 mysql 相对应的手册，了解在 '='41282' 附近使用的正确语法

Sub Getdata()
Dim conn As New ADODB.Connection
Dim server_name As String
Dim database_name As String
Dim user_id As String
Dim password As String

Dim a As Long ' counter
Dim i As Long, j As Variant, k As Long
Dim sqlstr As String ' SQL to perform various actions
Dim table1 As String, table2 As String
Dim field1 As String, field2 As String
Dim field3 As String
Dim rs As ADODB.Recordset

sqlstr = "INSERT INTO" & table1 & "SET" _
& field1 & " = '" & i & "', " _
& field2 & " = '" & j & "', " _
& field3 & " = '" & k & "'"
conn.Execute sqlstr
Next a
End With
skipwrite:
End Sub

Answer 1

简单地，将连接字符串中的表名称和 SET 命令隔开。另外，由于列名称中使用了保留字，请在前后添加刻度线(顺便说一下，作为最佳实践应该避免这种情况):

sqlstr = "INSERT INTO " & table1 & " SET " _
            & "`" & field1 & "` = '" & Format(i, "YYYY-MM-DD") & "', " _
            & "`" & field2 & "` = '" & j & "', " _
            & "`" & field3 & "` = '" & k & "';"

另外，正如@Lelio 提到的，格式化日期以正确读入 MySQL Date 列。