这段程序导入三万多条记录要十多分钟,还有没有更快的办法,就像access自带导入程序一样(<1秒钟)快的办法,请指教一下。
Private Sub Command1_Click()
Set conn = CreateObject("adodb.connection")
dbpath = "d:\vb_excel\report.mdb"
conn.Open ("driver={Microsoft Access Driver (*.mdb)};Set OLEDB:Allow Zero Length;dbq=" & dbpath)
Set xlapp = CreateObject("Excel.Application")
strsource = "d:\vb_excel\report.csv"
Set xlBook = xlapp.Workbooks.Open(strsource)
Set xlsheet = xlBook.Worksheets(1)
Set Rs = CreateObject("adodb.recordset")
sql = "select * from report"
Rs.Open sql, conn, 3, 3
I = 7
Do While I > 0 And xlsheet.Cells(I, 7) <> ""
Rs.addnew
Rs("date") = xlsheet.Cells(I, 1)
Rs("Keyword") = xlsheet.Cells(I, 2)
Rs("Keyword Matching") = xlsheet.Cells(I, 3)
Rs("Keyword Status") = xlsheet.Cells(I, 4)
Rs("Destination URL") = xlsheet.Cells(I, 5)
Rs("Ad Group") = xlsheet.Cells(I, 6)
Rs("Campaign") = xlsheet.Cells(I, 7)
Rs("Maximum CPC") = xlsheet.Cells(I, 8)
Rs("Impressions") = xlsheet.Cells(I, 9)
Rs("Clicks") = xlsheet.Cells(I, 10)
Rs("CTR") = xlsheet.Cells(I, 11)
Rs("Avg CPC") = xlsheet.Cells(I, 12)
Rs("Cost") = xlsheet.Cells(I, 13)
Rs("Avg Position") = xlsheet.Cells(I, 14)
Rs.Update
I = I + 1
Loop
conn = Nothing
xlapp.Quit
End Sub
Private Sub Command1_Click()
Set conn = CreateObject("adodb.connection")
dbpath = "d:\vb_excel\report.mdb"
conn.Open ("driver={Microsoft Access Driver (*.mdb)};Set OLEDB:Allow Zero Length;dbq=" & dbpath)
Set xlapp = CreateObject("Excel.Application")
strsource = "d:\vb_excel\report.csv"
Set xlBook = xlapp.Workbooks.Open(strsource)
Set xlsheet = xlBook.Worksheets(1)
Set Rs = CreateObject("adodb.recordset")
sql = "select * from report"
Rs.Open sql, conn, 3, 3
I = 7
Do While I > 0 And xlsheet.Cells(I, 7) <> ""
Rs.addnew
Rs("date") = xlsheet.Cells(I, 1)
Rs("Keyword") = xlsheet.Cells(I, 2)
Rs("Keyword Matching") = xlsheet.Cells(I, 3)
Rs("Keyword Status") = xlsheet.Cells(I, 4)
Rs("Destination URL") = xlsheet.Cells(I, 5)
Rs("Ad Group") = xlsheet.Cells(I, 6)
Rs("Campaign") = xlsheet.Cells(I, 7)
Rs("Maximum CPC") = xlsheet.Cells(I, 8)
Rs("Impressions") = xlsheet.Cells(I, 9)
Rs("Clicks") = xlsheet.Cells(I, 10)
Rs("CTR") = xlsheet.Cells(I, 11)
Rs("Avg CPC") = xlsheet.Cells(I, 12)
Rs("Cost") = xlsheet.Cells(I, 13)
Rs("Avg Position") = xlsheet.Cells(I, 14)
Rs.Update
I = I + 1
Loop
conn = Nothing
xlapp.Quit
End Sub
解决方案 »
- VB和SQL连接不上??
- 测字符串大小函数
- 串口乱码
- 用sheet动态创建数据库表 仔细看代码的都有分啊
- 如何用VB实现鼠标操作?
- 【简单问题】关于字符串截取
- 如何在一个pictureBox先后画两个圆(要动态显示画圆的过程)
- 狂送100分(27)
- 在VB运行环境下,工程可以完成密码修改,但生成EXE文件后,密码修改不成功,为什么?如何正确生成EXE文件?(如何带上数据库生成正确的文
- TrueDBGrid7.0 绑定到 ADO RecordSet时,代码运行Recordset.AddNew后的巨大BUG?!
- 有没有其它办法了??????????
- 请问怎么样可以当最大化窗体时,里面的控件位置(例如lable控件)可以自动按比例调整位置?
conn.Execute "SELECT * into report FROM [Driver={Microsoft Text Driver (*.txt; *.csv)};dbq=d:\vb_excel\].[report.csv]"
conn.Execute "INSERT into report select * FROM [Text;HDR=NO;DATABASE=d:\vb_excel\].report.csv"