用Excel向Access数据库添加纪录的问题

ym21ht

如图 想把多个列同时保存到access数据库里，代码参照别人的 可是结果只保存最后一列，前面的都不保存，是什么问题呢？代码如下：
Sub addRecords() '从Excel工作表中向数据表添加纪录
    Dim cnn As Object, SQL$
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\1.accdb" '连接数据库
    SQL = "INSERT INTO 产品名称 SELECT 品名 FROM (SELECT 品名 FROM [Excel 12.0;Database=" & ThisWorkbook.FullName _
        & ";].[Sheet1$A1:A" & Range("A" & Rows.Count).End(xlUp).Row & "] )"
    SQL = "INSERT INTO 产品名称 SELECT 型号 FROM (SELECT 型号 FROM [Excel 12.0;Database=" & ThisWorkbook.FullName _
        & ";].[Sheet1$B1:B" & Range("B" & Rows.Count).End(xlUp).Row & "] )"
    SQL = "INSERT INTO 产品名称 SELECT 规格 FROM (SELECT 规格 FROM [Excel 12.0;Database=" & ThisWorkbook.FullName _
        & ";].[Sheet1$C1:C" & Range("C" & Rows.Count).End(xlUp).Row & "] )"
cnn.Execute SQL
    MsgBox "纪录添加成功。", vbInformation, "添加纪录"
    cnn.Close
    Set cnn = Nothing
End Sub

EXCELLMY

老会员了，附件也不上一个

microyip

1. Sub addRecords() '从Excel工作表中向数据表添加纪录
   
2.     Dim cnn As Object, SQL$
   
3.     Set cnn = CreateObject("ADODB.Connection")
   
4.     cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\1.accdb" '连接数据库
   
5.     SQL = "INSERT INTO 产品名称 SELECT 品名 FROM (SELECT * FROM [Excel 12.0;Database=" & ThisWorkbook.FullName _
   
6.         & ";].[Sheet1$A1:C" & Range("A" & Rows.Count).End(xlUp).Row & "] )"
   
7. cnn.Execute SQL
   
8.     MsgBox "纪录添加成功。", vbInformation, "添加纪录"
   
9.     cnn.Close
   
10.     Set cnn = Nothing
    
11. End Sub

复制代码

jsgj2023

用for循环写入!

ym21ht

刚才上传不上去

ym21ht

jsgj2023 发表于 2019-12-15 11:15
用for循环写入!

大神，我上传附件了 麻烦你看下

jsgj2023

一个循序写入的栗子供参考!

opiona

1. '======================================================================================================== 批量添加
   
2. INSERT INTO [表格名] SELECT * FROM [Excel 8.0;Database=" & ThisWorkbook.FullName & "].[" & SH1.Name & "$A2:K]  WHERE LEN(排产日期)>0"
   
3. SELECT * INTO [MS Access;Database=" & Application.StartupPath & "\工资条分发.mdb].[" & ComboBox1.Text & "] FROM [Sheet1$]"
   
4. SELECT * INTO [查询表名] FROM [Excel 8.0;DataBase=" & tmpFile & "].[" & ActShtName & "$]"

复制代码

opiona

ym21ht

opiona 发表于 2019-12-15 20:18

FROM 子句语法错误