从EXCEL中UPDATE数据到MSSQL中报错,急啊,,,,

AGP

我先將SQL Table下載到Excel中,在Excel中修改後將原SQL Table 刪除,用

1. .Open "select * into idd_test from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=yes;database=d:1.xls;','select * from [Sheet1$]')"

复制代码

上傳數據表.但在這里會報錯.
如下圖

1. </FONT></P>
2. <P><FONT size=2>rsPubs.Save<BR>r = 3 ' the start row in the worksheet<BR> Do While Len(Range("A" &amp; r).Formula) &gt; 0<BR> ' repeat until first empty cell in column A<BR> With rsPubs<BR> '.AddNew ' create a new record<BR> ' add values to each field in the record<BR> .Fields(company_code) = Range("A" &amp; r).Value<BR> .Fields("user_code") = Range("B" &amp; r).Value<BR> .Fields("user_name") = Range("C" &amp; r).Value<BR> ' add more fields if necessary...<BR> .Update ' stores the new record<BR> End With<BR> r = r + 1 ' next row<BR> Loop<BR><BR><BR>rspubs.Open "select * into idd_test1 from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=yes;database=1.xls;','select * from [Sheet1$]')"<BR><BR><BR><BR>Private Sub CommandButton1_Click()<BR><BR>Dim cnpubs As adodb.Connection<BR>Set cnpubs = New adodb.Connection<BR>Dim strConn As String<BR>strConn = "PROVIDER=SQLOLEDB;"<BR>strConn = strConn &amp; "DATA SOURCE=ibm3000;INITIAL CATALOG=idd;Uid=sa;Pwd=sa"<BR>cnpubs.Open strConn<BR>Dim rsPubs As adodb.Recordset<BR>Set rsPubs = New adodb.Recordset<BR><BR>With rsPubs<BR> .ActiveConnection = cnpubs<BR> .Open "SELECT * FROM idd_test"<BR> Sheet1.Range("a3", "az65535").Delete<BR> Sheet1.Range("A3").CopyFromRecordset rsPubs<BR> .Close<BR>End With<BR>cnpubs.Close<BR>Set rsPubs = Nothing<BR>Set cnpubs = Nothing<BR> <BR><BR><BR><BR>End Sub<BR><BR>Private Sub CommandButton3_Click()<BR> ActiveWorkbook.SaveAs Filename:="d:1.XLS"<BR>Dim cnpubs As adodb.Connection<BR>Set cnpubs = New adodb.Connection<BR><BR><BR>Dim strConn As String<BR><BR><BR>strConn = "PROVIDER=SQLOLEDB;"<BR><BR>strConn = strConn &amp; "DATA SOURCE=ibm3000;INITIAL CATALOG=idd;Uid=sa;Pwd=sa"<BR><BR>cnpubs.Open strConn<BR><BR>Dim rsPubs As adodb.Recordset<BR>Set rsPubs = New adodb.Recordset<BR>With rsPubs<BR><BR> .ActiveConnection = cnpubs<BR><BR> 'rsPubs.Open "drop table user_msg"<BR> .Open "select * into idd_test from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=yes;database=d:1.xls;','select * from [Sheet1$]')"<BR> <BR>End With<BR><BR><BR><BR><BR><BR><BR><BR><BR>End Sub<BR><BR>Private Sub CommandButton4_Click()<BR>Sheet1.Range("a3", "az65535").Delete<BR>End Sub</P>
3. <P>

复制代码