急！怎样用VB6向Access2003中批量添加数据？

[luffy9202]

是这样，我的数据库中有一个库存日报的表，每天启动程序的时候要向表中添加一个今天的库存信息，大概有150多条。即每天要增加150条数据，假如中间有两天没有启动程序，那么第三天启动时要添加3*150即450条。之前用conn.excute执行，如果只添加150条速度还可以，如果是两天或以上，那么程序就会无响应，第二次启动才能写入。后来改成rs.addnew，也是同样的情况，数据少的时候还挺快，不到1S，多了根本运行不起来。现贴下代码。

On Error Resume Next
    Dim rstTitles As New ADODB.Recordset
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Environ$("USERPROFILE") & "\Application Data\专卖店管理系统\study.mdb" & ";Mode=ReadWrite;Persist Security Info=False"
    conn.Open

    Dim a As Date
    Dim t1 As String, t2 As String
    t1 = Time$()
    conn.BeginTrans
    rstTitles.LockType = adLockPessimistic '不能使用adLockBatchOptimistic或adLockReadOnly
    rstTitles.CursorType = adOpenDynamic
    rstTitles.Open "select * from 库存日报", conn
    '-----添加新的库存记录-----'
    rs.Open "select * from 库存日报 where format(日期,'yyyy-mm-dd')='" & Format(Date, "yyyy-mm-dd") & "'", conn, 3, 3
    If rs.RecordCount = 0 Then
        rs1.Open "select * from 库存日报 order by 日期,产品编码", conn, 3, 3
        rs1.MoveLast
        a = rs1("日期")
        For i = Format(rs1("日期") + 1, "yyyy-mm-dd") To Format(Date, "yyyy-mm-dd")
            While rs1("日期") = a
                rstTitles.AddNew
                rstTitles("产品编码") = rs1("产品编码")
                rstTitles("产品名称") = rs1("产品名称")
                rstTitles("单位") = rs1("单位")
                rstTitles("类别") = rs1("类别")
                rstTitles("期初数量") = rs1("期末数量")
                rstTitles("入库数量") = 0
                rstTitles("销售数量") = 0
                rstTitles("压货数量") = 0
                rstTitles("自用数量") = 0
                rstTitles("零售数量") = 0
                rstTitles("退货数量") = 0
                rstTitles("借入数量") = 0
                rstTitles("调换数量") = 0
                rstTitles("期末数量") = rs1("期末数量")
                rstTitles("日期") = Format(i, "YYYY-MM-DD")
                rstTitles.Update
'                SQL = "insert into 库存日报(产品编码,产品名称,单位,类别,期初数量,入库数量,销售数量,压货数量,自用数量,零售数量,退货数量,借入数量,调换数量,期末数量,日期)VALUES("""
'                SQL = SQL & rs1("产品编码") & ""","""
'                SQL = SQL & rs1("产品名称") & ""","""
'                SQL = SQL & rs1("单位") & ""","""
'                SQL = SQL & rs1("类别") & ""","""
'                SQL = SQL & rs1("期末数量") & ""","""
'                SQL = SQL & 0 & ""","""
'                SQL = SQL & 0 & ""","""
'                SQL = SQL & 0 & ""","""
'                SQL = SQL & 0 & ""","""
'                SQL = SQL & 0 & ""","""
'                SQL = SQL & 0 & ""","""
'                SQL = SQL & 0 & ""","""
'                SQL = SQL & 0 & ""","""
'                SQL = SQL & rs1("期末数量") & ""","""
'                SQL = SQL & Format(i, "YYYY-MM-DD") & """)"
'                conn.Execute SQL
                rs1.MovePrevious
            Wend
            rs1.MoveLast
        Next
        rs1.Close
    End If
    rs.Close

    conn.CommitTrans
    rstTitles.Close
    conn.Close
    t2 = Time$()
    MsgBox t1 & "   " & t2

求可用方法。

[赵4老师]

不知道能不能将想添加的数据先写到一个临时纯文本文件.csv中，然后在Access中添加导入这个.csv文件。

[luffy9202]

不利用.csv有没有更好的方法呢？

[X-i-n]

几百条数据运行起来会卡死这肯定不正常 检查一下标红代码的逻辑 On Error Resume Next Dim rstTitles As New ADODB.Recordset conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Environ$("USERPROFILE") & "\Application Data\专卖店管理系统\study.mdb" & ";Mode=ReadWrite;Persist Security Info=False" conn.Open Dim a As Date Dim t1 As String, t2 As String t1 = Time$() conn.BeginTrans rstTitles.LockType = adLockPessimistic '不能使用adLockBatchOptimistic或adLockReadOnly rstTitles.CursorType = adOpenDynamic rstTitles.Open "select * from 库存日报", conn '-----添加新的库存记录-----' rs.Open "select * from 库存日报 where format(日期,'yyyy-mm-dd')='" & Format(Date, "yyyy-mm-dd") & "'", conn, 3, 3 If rs.RecordCount = 0 Then rs1.Open "select * from 库存日报 order by 日期,产品编码", conn, 3, 3 rs1.MoveLast a = rs1("日期") For i = Format(rs1("日期") + 1, "yyyy-mm-dd") To Format(Date, "yyyy-mm-dd") While rs1("日期") = a rstTitles.AddNew rstTitles("产品编码") = rs1("产品编码") rstTitles("产品名称") = rs1("产品名称") rstTitles("单位") = rs1("单位") rstTitles("类别") = rs1("类别") rstTitles("期初数量") = rs1("期末数量") rstTitles("入库数量") = 0 rstTitles("销售数量") = 0 rstTitles("压货数量") = 0 rstTitles("自用数量") = 0 rstTitles("零售数量") = 0 rstTitles("退货数量") = 0 rstTitles("借入数量") = 0 rstTitles("调换数量") = 0 rstTitles("期末数量") = rs1("期末数量") rstTitles("日期") = Format(i, "YYYY-MM-DD") rstTitles.Update ' SQL = "insert into 库存日报(产品编码,产品名称,单位,类别,期初数量,入库数量,销售数量,压货数量,自用数量,零售数量,退货数量,借入数量,调换数量,期末数量,日期)VALUES(""" ' SQL = SQL & rs1("产品编码") & """,""" ' SQL = SQL & rs1("产品名称") & """,""" ' SQL = SQL & rs1("单位") & """,""" ' SQL = SQL & rs1("类别") & """,""" ' SQL = SQL & rs1("期末数量") & """,""" ' SQL = SQL & 0 & """,""" ' SQL = SQL & 0 & """,""" ' SQL = SQL & 0 & """,""" ' SQL = SQL & 0 & """,""" ' SQL = SQL & 0 & """,""" ' SQL = SQL & 0 & """,""" ' SQL = SQL & 0 & """,""" ' SQL = SQL & 0 & """,""" ' SQL = SQL & rs1("期末数量") & """,""" ' SQL = SQL & Format(i, "YYYY-MM-DD") & """)" ' conn.Execute SQL rs1.MovePrevious Wend rs1.MoveLast Next rs1.Close End If rs.Close conn.CommitTrans rstTitles.Close conn.Close t2 = Time$() MsgBox t1 & " " & t2

[vansoft]

同一个库里的话，直接insert into select 一条语句搞定。

[一笑拔剑]

On Error Resume Next 别用 看看错误在哪里

[luffy9202]

谢谢大家热心帮助！ 在这里统一回复以上：三楼赛高，果然是那里有问题，用format都改为日期格式可以正常使用！四楼，因为是循环，我不知道怎么用一句代码实现。五楼，通过注释掉该句，发现了另一个错误哈哈。 谢谢大家！阿里嘎多！

[程序员老干部]

insert into 用这个，不要打开记录集