On Error GoTo Click_Err: Dim oCon As New ADODB.Connection Dim eCon As New ADODB.Connection Dim rs As New Recordset oCon.ConnectionString = Me.Adodc1.ConnectionString--連接SQL的 我用的Adodc得到連接字符串 eCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=false;Data Source=C:\Documents and Settings\zhangshuai\My Documents\MyWork\Shit.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'" eCon.Open oCon.Open ' rs.Open "Select * From Change", eCon, adOpenDynamic, adLockBatchOptimistic Set rs = eCon.Execute("Select * From [Sheet1$]") ' Debug.Print rs(0) If rs.EOF Then eCon.Close oCon.Close Exit Sub End If oCon.Execute "Select * From ZhangShTry" While Not rs.EOF oCon.Execute "Insert Into ZhangShTry(Code,Date,Time) Values('" & rs(0) & "',Convert(Varchar(50),'" & rs(1) & "',103),Convert(Varchar(50),'" & rs(2) & "',108))" rs.MoveNext Wend oCon.Close eCon.Close Exit Sub Click_Err: Debug.Print Err.Source, Err.Description eCon.Close oCon.Close 自己改,
我将数据插入方向倒过来(将数据表中的数据读出来,写到EXECEL里),但是出现了错! On Error GoTo Click_Err: Dim oCon As New ADODB.Connection Dim eCon As New ADODB.Connection Dim rs As New Recordset oCon.ConnectionString = Me.Adodc1.ConnectionString--連接SQL的 我用的Adodc得到連接字符串 eCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=false;Data Source=C:\Documents and Settings\zhangshuai\My Documents\MyWork\Shit.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'" eCon.Open oCon.Open ' rs.Open "Select * From Change", eCon, adOpenDynamic, adLockBatchOptimistic Set rs = oCon.Execute("Select * From ZhangShTry]") ' Debug.Print rs(0) If rs.EOF Then eCon.Close oCon.Close Exit Sub End If eCon.Execute "Select * From [Sheet1$]" While Not rs.EOF eCon.Execute "Insert Into [Sheet1$](Code,Date,Time) Values('" & rs(0) & "',Convert(Varchar(50),'" & rs(1) & "',103),Convert(Varchar(50),'" & rs(2) & "',108))" rs.MoveNext Wend oCon.Close eCon.Close Exit Sub Click_Err: Debug.Print Err.Source, Err.Description eCon.Close oCon.Close 出现错误如下: Microsoft JET Database Engine 标准表达式中数据类型不匹配。 Microsoft JET Database Engine 操作必须使用一个可更新的查询。 请问怎么解决啊?
保存成DTS然後Bak調用就可以了
Dim oCon As New ADODB.Connection
Dim eCon As New ADODB.Connection
Dim rs As New Recordset
oCon.ConnectionString = Me.Adodc1.ConnectionString--連接SQL的 我用的Adodc得到連接字符串
eCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=false;Data Source=C:\Documents and Settings\zhangshuai\My Documents\MyWork\Shit.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
eCon.Open
oCon.Open
' rs.Open "Select * From Change", eCon, adOpenDynamic, adLockBatchOptimistic
Set rs = eCon.Execute("Select * From [Sheet1$]")
' Debug.Print rs(0)
If rs.EOF Then
eCon.Close
oCon.Close
Exit Sub
End If
oCon.Execute "Select * From ZhangShTry"
While Not rs.EOF
oCon.Execute "Insert Into ZhangShTry(Code,Date,Time) Values('" & rs(0) & "',Convert(Varchar(50),'" & rs(1) & "',103),Convert(Varchar(50),'" & rs(2) & "',108))"
rs.MoveNext
Wend
oCon.Close
eCon.Close
Exit Sub
Click_Err:
Debug.Print Err.Source, Err.Description
eCon.Close
oCon.Close
自己改,
On Error GoTo Click_Err:
Dim oCon As New ADODB.Connection
Dim eCon As New ADODB.Connection
Dim rs As New Recordset
oCon.ConnectionString = Me.Adodc1.ConnectionString--連接SQL的 我用的Adodc得到連接字符串
eCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=false;Data Source=C:\Documents and Settings\zhangshuai\My Documents\MyWork\Shit.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
eCon.Open
oCon.Open
' rs.Open "Select * From Change", eCon, adOpenDynamic, adLockBatchOptimistic
Set rs = oCon.Execute("Select * From ZhangShTry]")
' Debug.Print rs(0)
If rs.EOF Then
eCon.Close
oCon.Close
Exit Sub
End If
eCon.Execute "Select * From [Sheet1$]"
While Not rs.EOF
eCon.Execute "Insert Into [Sheet1$](Code,Date,Time) Values('" & rs(0) & "',Convert(Varchar(50),'" & rs(1) & "',103),Convert(Varchar(50),'" & rs(2) & "',108))"
rs.MoveNext
Wend
oCon.Close
eCon.Close
Exit Sub
Click_Err:
Debug.Print Err.Source, Err.Description
eCon.Close
oCon.Close
出现错误如下:
Microsoft JET Database Engine 标准表达式中数据类型不匹配。
Microsoft JET Database Engine 操作必须使用一个可更新的查询。
请问怎么解决啊?