|
各位老师:
我学赵老师的代码,想将Excel工作表内容更新到SQL Server 2008数据库,提示出错如下图:
赵老师原代码:
Sub updateaddRecords2007()
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim myPath As String
Dim myTable As String
Dim strTemp As String
Dim arrFields As Variant
myPath = ThisWorkbook.Path & "\学校管理.accdb"
myTable = "学生档案"
On Error GoTo errmsg
cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & myPath '连接数据库
arrFields = Range("A1:J1") '工作表中的字段名写入数组
'生成更新字符串,如:a.姓名=b.姓名,a.性别=b.性别,……
For i = 2 To UBound(arrFields, 2)
strTemp = strTemp & ",a." & arrFields(1, i) & "=b." & arrFields(1, i)
Next
'生成更新SQL语句(请注意Office2007后需要加imex=0参数)
SQL = "update " & myTable & " a,[Excel 12.0;imex=0;Database=" & ActiveWorkbook.FullName & "].[数据$" _
& Range("a1").CurrentRegion.Address(0, 0) & "] b set " & Mid(strTemp, 2) & " where a.学生编号=b.学生编号"
cnn.Execute SQL '
SQL = "select a.* from [Excel 12.0;Database=" & ActiveWorkbook.FullName & "].[数据$" & Range("a1").CurrentRegion.Address(0, 0) _
& "] a left join " & myTable & " b on a.学生编号=b.学生编号 where b.学生编号 is null"
Set rs = New ADODB.Recordset
rs.Open SQL, cnn, adOpenKeyset, adLockOptimistic
If rs.RecordCount > 0 Then '
SQL = "insert into " & myTable & " " & SQL '
cnn.Execute SQL
MsgBox rs.RecordCount & "行数据已经添加到数据库!", vbInformation, "添加数据"
Else
MsgBox "工作表的数据数据库中已经存在。", vbInformation, "添加数据失败"
End If
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
Exit Sub
errmsg:
MsgBox Err.Description, , "错误报告"
End Sub
赵老师的代码更新Access数据库没问题,但更新SQL Server 2008出错,请赵老师和各位老师指定问题出在哪里?谢谢!
|
|