问题描述

VB6实现excel导入SQL SERVER7程序问题

Set cnn = New ADODB.Connection
cnn.Open "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initial Catalog=labor;Data Source=RPC_004"
cnn.CursorLocation = adUseClient
    Set RS = New ADODB.Recordset
STR = "SELECT * FROM litem"
RS.Open STR, cnn, adOpenKeyset, adLockOptimistic
If RS.RecordCount > 0 Then
    RS.MoveFirst
    Do Until RS.EOF
        RS.Delete
        RS.MoveNext
    Loop
End If
Set RS = Nothing
MsgBox "刪除完成"
CommonDialog1.Filter = "文件(.xls)|*.xls"
CommonDialog1.DialogTitle = "请选择要导入的文件"
CommonDialog1.ShowOpen
Text1.Text = CommonDialog1.FILENAME
STR1 = "INSERT INTO litem SELECT ITEM_NO,ITEM_NAME,ITEM_UNIT ... FROM  OPENROWSET ('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;DATABASE=" & CommonDialog1.FILENAME & "',Sheet1$)"
cnn.Execute STR1, , adExecuteNoRecords
MsgBox "导入成功"

提示错误cnn.Execute STR1, , adExecuteNoRecords，信息“OLE DB provider ‘MICROSOFT.JET.OLEDB.4.0’ reported an error.The provider did not give any information about the error.”
复制 STR1到 SQL SERVER7中执行,可正常执行写入数据库。
不知是何原因？请各位老大帮忙，谢谢！

解决方案

试试下面

STR1 = "INSERT INTO litem SELECT ITEM_NO,ITEM_NAME,ITEM_UNIT ... FROM  OPENROWSET ('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;DATABASE=" & CommonDialog1.FILENAME & "','select * from [Sheet1$]')"