ACCESS表导入远程SQL时如何覆盖重复记录?
各位前辈:下面代码已经可以实现将ACCESS表导入SQL,但是我要重复导入的时候怎么才能覆盖重复记录呢?
_____________________________________________________________________
Private Sub qtxpmx()
Dim cnAccess As New ADODB.Connection
Dim cnSql As New ADODB.Connection
Dim rsAccess As New ADODB.Recordset
Dim rsSql As New ADODB.Recordset
'打开Access数据库的连接，具体的需要改一下
cnAccess.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\MDB.mdb;Persist Security Info=False "
rsAccess.CursorLocation = adUseClient
'获取Access里的SPLSDMX表需要上传的字段的记录，准备导出入sql
rsAccess.Open "SELECT SPLSD.DJBH, SPLSD.RQ, SPLSD.SDDM, SPLSD.QDDM, SPLSD.VPDM, SPLSD.YGDM, SPLSD.TJ, SPLSD.JZ, SPLSD.JZRQ, SPLSD.ZYMC, SPLSDMX.MXBH, SPLSDMX.SPDM, SPLSDMX.GG1DM, SPLSDMX.GG2DM, SPLSDMX.SL, SPLSDMX.CKJ, SPLSDMX.ZK, SPLSDMX.DJ, SPLSDMX.JE,SPLSDMX.HH, SPLSDMX.BYZD1, SPLSDMX.BYZD2, SPLSDMX.BYZD3, SPLSDMX.BYZD4, SPLSDMX.BYZD5, SPLSDMX.BYZD6,SPLSD.BZ FROM SPLSD INNER JOIN SPLSDMX ON SPLSD.DJBH = SPLSDMX.DJBH WHERE SPLSD.JSBZ= '1 ' AND SPLSD.JSDJ= '0 ' and SPLSD.JZRQ> =# " & CDate(Me.DTPicker1.Value) & " # and SPLSD.JZRQ <=# " & CDate(Me.DTPicker2.Value) & "# ", cnAccess, adOpenDynamic, adLockReadOnly
'连接sql
cnSql.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=XXX;Data Source=XXX.XXX.XXX.XXX;Initial Catalog=data_XXX "
'打开sql的QTXPMX表，准备导入数据
rsSql.Open "select * from QTXPMX ", cnSql, adOpenDynamic, adLockPessimistic
rsAccess.MoveFirst
While Not rsAccess.EOF
rsSql.AddNew
'给sql的记录集赋值
rsSql( "DJBH ") = rsAccess( "DJBH ")
rsSql( "MXBH ") = rsAccess( "MXBH ")
rsSql( "SPDM ") = rsAccess( "SPDM ")
rsSql( "GG1DM ") = rsAccess( "GG1DM ")
rsSql( "GG2DM ") = rsAccess( "GG2DM ")
rsSql( "SL ") = rsAccess( "SL ")
rsSql( "SL ") = rsAccess( "SL ")
rsSql( "CKJ ") = rsAccess( "CKJ ")
rsSql( "ZK ") = rsAccess( "ZK ")
rsSql( "DJ ") = rsAccess( "DJ ")
rsSql( "JE ") = rsAccess( "JE ")
rsSql( "HH ") = rsAccess( "HH ")
rsSql( "BYZD1 ") = rsAccess( "BYZD1 ")
rsSql( "BYZD2 ") = rsAccess( "BYZD2 ")
rsSql( "BYZD3 ") = rsAccess( "BYZD3 ")
rsSql( "BYZD4 ") = rsAccess( "BYZD4 ")
rsSql( "BYZD5 ") = rsAccess( "BYZD5 ")
rsSql( "BYZD6 ") = rsAccess( "BYZD6 ")
rsAccess.MoveNext
If rsAccess.EOF Then
MsgBox "传输完毕！传输任务已完成！ ", vbExclamation, "传输状态 "
End If
Wend
rsSql.UpdateBatch '批量更新记录集
Set rsAccess = Nothing
Set rsSql = Nothing
cnAccess.Close
Set cnAccess = Nothing
cnSql.Close
Set cnSql = Nothing
End Sub
[解决办法]
在添加AddNew之前,你先过滤下,若记录已经存在则不要添加了,直接修改,
若没有,则添加
[解决办法]
用这种方法，太差了！效率很成问题，特别是数据量大时。
insert into table1() select * from [数据源].table2 where 条件
一条sql就行。
