我想从Access 导出数据到DB2, 比如说Access表中有3条数据,我要导入DB2,并且要删除。 但是插入3行可以成功,删除只能删除1行,并且第一行删除3遍(因为有3条数据)。 代码如下:恳请各位高手指点一二,小弟感激不尽,谢谢!Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sql As String
Dim I As Integer
Set db = CurrentDb
'delete the record with the reloaded key seg -> seq -> ct
sql = "select distinct ctry_cd,tax_cd,tax_type_cd from " & strDataTblName
Set rs = db.OpenRecordset(sql)
For I = 0 To rs.RecordCount - 1
'x1
sql = "delete * from " & TAX_RULE_SEG
sql = sql & " where ctry_cd='" & rs("ctry_cd") & "'"
sql = sql & " and tax_cd='" & rs("tax_cd") & "'"
sql = sql & " and tax_type_cd='" & rs("tax_type_cd") & "'"
db.Execute sql
sql = "delete * from " & TAX_RULE_SEQ
sql = sql & " where ctry_cd='" & rs("ctry_cd") & "'"
sql = sql & " and tax_cd='" & rs("tax_cd") & "'"
sql = sql & " and tax_type_cd='" & rs("tax_type_cd") & "'"
db.Execute sql
'x2
sql = "delete * from " & TAX_REPRT_SEG
sql = sql & " where ctry_cd='" & rs("ctry_cd") & "'"
sql = sql & " and tax_cd='" & rs("tax_cd") & "'"
sql = sql & " and tax_type_cd='" & rs("tax_type_cd") & "'"
db.Execute sql
sql = "delete * from " & TAX_REPRT_SEQ
sql = sql & " where ctry_cd='" & rs("ctry_cd") & "'"
sql = sql & " and tax_cd='" & rs("tax_cd") & "'"
sql = sql & " and tax_type_cd='" & rs("tax_type_cd") & "'"
db.Execute sql
'key
sql = "delete* from " & TAX_CTL
sql = sql & " where ctry_cd='" & rs("ctry_cd") & "'"
sql = sql & " and tax_cd='" & rs("tax_cd") & "'"
sql = sql & " and tax_type_cd='" & rs("tax_type_cd") & "'"
db.Execute sql
rs.MoveNext
Next
rs.Close
'add the content into DB
''insert into tax_ctl
sql = "select distinct ctry_cd, tax_cd, tax_type_cd from " & strDataTblName
Set rs = db.OpenRecordset(sql)
For I = 0 To rs.RecordCount - 1
sql = "insert into " & TAX_CTL & " ("
sql = sql & " ctry_cd,tax_cd,tax_type_cd,batch_ci,batch_no,ver_no"
sql = sql & ",create_id,create_ts,last_mnt_act,last_mnt_id,last_mnt_ts"
sql = sql & ") values ("
sql = sql & "'" & rs("ctry_cd") & "'"
sql = sql & ",'" & rs("tax_cd") & "'"
sql = sql & ",'" & rs("tax_type_cd") & "'"
'if there is incomplete batch, fill into tax_ctl, otherwise, fill null to tax_ctl
Dim strBatchID As String
strBatchID = GetIncompleteBatch(strDataTblName, rs("ctry_cd"), rs("tax_cd"), rs("tax_type_cd"))
If strBatchID = "" Then
sql = sql & ",null"
sql = sql & ",null"
Else
sql = sql & ",'" & Left(strBatchID, 3) & "'"
sql = sql & "," & Mid(strBatchID, 4, Len(strBatchID))
End If
sql = sql & ",1"
sql = sql & ",'ATPSKXG','2010-03-19 17:14:00.000000','I','ATPSKXG','2010-03-19 17:14:00.000000'"
sql = sql & ")"
db.Execute sql
rs.MoveNext
Next
rs.Close
Dim rs As DAO.Recordset
Dim sql As String
Dim I As Integer
Set db = CurrentDb
'delete the record with the reloaded key seg -> seq -> ct
sql = "select distinct ctry_cd,tax_cd,tax_type_cd from " & strDataTblName
Set rs = db.OpenRecordset(sql)
For I = 0 To rs.RecordCount - 1
'x1
sql = "delete * from " & TAX_RULE_SEG
sql = sql & " where ctry_cd='" & rs("ctry_cd") & "'"
sql = sql & " and tax_cd='" & rs("tax_cd") & "'"
sql = sql & " and tax_type_cd='" & rs("tax_type_cd") & "'"
db.Execute sql
sql = "delete * from " & TAX_RULE_SEQ
sql = sql & " where ctry_cd='" & rs("ctry_cd") & "'"
sql = sql & " and tax_cd='" & rs("tax_cd") & "'"
sql = sql & " and tax_type_cd='" & rs("tax_type_cd") & "'"
db.Execute sql
'x2
sql = "delete * from " & TAX_REPRT_SEG
sql = sql & " where ctry_cd='" & rs("ctry_cd") & "'"
sql = sql & " and tax_cd='" & rs("tax_cd") & "'"
sql = sql & " and tax_type_cd='" & rs("tax_type_cd") & "'"
db.Execute sql
sql = "delete * from " & TAX_REPRT_SEQ
sql = sql & " where ctry_cd='" & rs("ctry_cd") & "'"
sql = sql & " and tax_cd='" & rs("tax_cd") & "'"
sql = sql & " and tax_type_cd='" & rs("tax_type_cd") & "'"
db.Execute sql
'key
sql = "delete* from " & TAX_CTL
sql = sql & " where ctry_cd='" & rs("ctry_cd") & "'"
sql = sql & " and tax_cd='" & rs("tax_cd") & "'"
sql = sql & " and tax_type_cd='" & rs("tax_type_cd") & "'"
db.Execute sql
rs.MoveNext
Next
rs.Close
'add the content into DB
''insert into tax_ctl
sql = "select distinct ctry_cd, tax_cd, tax_type_cd from " & strDataTblName
Set rs = db.OpenRecordset(sql)
For I = 0 To rs.RecordCount - 1
sql = "insert into " & TAX_CTL & " ("
sql = sql & " ctry_cd,tax_cd,tax_type_cd,batch_ci,batch_no,ver_no"
sql = sql & ",create_id,create_ts,last_mnt_act,last_mnt_id,last_mnt_ts"
sql = sql & ") values ("
sql = sql & "'" & rs("ctry_cd") & "'"
sql = sql & ",'" & rs("tax_cd") & "'"
sql = sql & ",'" & rs("tax_type_cd") & "'"
'if there is incomplete batch, fill into tax_ctl, otherwise, fill null to tax_ctl
Dim strBatchID As String
strBatchID = GetIncompleteBatch(strDataTblName, rs("ctry_cd"), rs("tax_cd"), rs("tax_type_cd"))
If strBatchID = "" Then
sql = sql & ",null"
sql = sql & ",null"
Else
sql = sql & ",'" & Left(strBatchID, 3) & "'"
sql = sql & "," & Mid(strBatchID, 4, Len(strBatchID))
End If
sql = sql & ",1"
sql = sql & ",'ATPSKXG','2010-03-19 17:14:00.000000','I','ATPSKXG','2010-03-19 17:14:00.000000'"
sql = sql & ")"
db.Execute sql
rs.MoveNext
Next
rs.Close
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货