我的想法是把把B.mdb中的a表中的记录追加到给A.mdb的a表,这两个表的结构完全相同,而且要把相同的记录删除。我的做法如下:
1.把B.mdb中的a表中的记录追加到给A.mdb的a表;(下面代码通过。但是方法估计不好,看哪位dx能给出更好的方法;)
2.从A.mdb的a表中找出不重复的记录加到临时的表temTab中;(但是下面的代码执行出错,大家看看是哪写错了)
3.清空A.mdb的a表中的记录;
4.把临时的表temTab中记录加到.mdb的a表中;
5.删除临时表temTable; Dim myConn1 As ADODB.Connection, myConn2 As ADODB.Connection
Set myConn1 = New ADODB.Connection
Set myConn2 = New ADODB.Connection
myConn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\A.mdb;Persist Security Info=False"
myConn2.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\B.mdb;Persist Security Info=False"
myConn1.Open
myConn2.Open
Dim strSql As String
Dim rs1 As ADODB.Recordset, rs2 As ADODB.Recordset
Dim i As Integer
Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
rs1.CursorLocation = adUseClient
rs2.CursorLocation = adUseClient
strSql = "select * from a"
rs1.Open strSql, myConn1, adOpenDynamic, adLockBatchOptimistic
rs2.Open strSql, myConn2, adOpenDynamic, adLockBatchOptimistic
rs2.MoveFirst
Do While Not rs2.EOF
rs1.AddNew
For i = 0 To rs1.fields.Count - 1
rs1(i).Value = rs2(i).Value'把B.mdb中的a表中的记录付给A.mdb的a表
Next
rs2.MoveNext
Loop
rs1.UpdateBatch strSql = "select distinct * into temTab from a"
myConn1.Execute strSql'找出A.mdb的a表中不重复的记录组成一个表temTab*********************执行到这出错了
strSql = "delete * from a"
myConn1.Execute strSql'删除A.mdb的a表中的所有记录
strSql = "insert into a select * from temTab"
myConn1.Execute strSql'把表temTab的记录插入到A.mdb的a表中
strSql = "drop table temTab"
myConn1.Execute strSql'删除表temTab
1.把B.mdb中的a表中的记录追加到给A.mdb的a表;(下面代码通过。但是方法估计不好,看哪位dx能给出更好的方法;)
2.从A.mdb的a表中找出不重复的记录加到临时的表temTab中;(但是下面的代码执行出错,大家看看是哪写错了)
3.清空A.mdb的a表中的记录;
4.把临时的表temTab中记录加到.mdb的a表中;
5.删除临时表temTable; Dim myConn1 As ADODB.Connection, myConn2 As ADODB.Connection
Set myConn1 = New ADODB.Connection
Set myConn2 = New ADODB.Connection
myConn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\A.mdb;Persist Security Info=False"
myConn2.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\B.mdb;Persist Security Info=False"
myConn1.Open
myConn2.Open
Dim strSql As String
Dim rs1 As ADODB.Recordset, rs2 As ADODB.Recordset
Dim i As Integer
Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
rs1.CursorLocation = adUseClient
rs2.CursorLocation = adUseClient
strSql = "select * from a"
rs1.Open strSql, myConn1, adOpenDynamic, adLockBatchOptimistic
rs2.Open strSql, myConn2, adOpenDynamic, adLockBatchOptimistic
rs2.MoveFirst
Do While Not rs2.EOF
rs1.AddNew
For i = 0 To rs1.fields.Count - 1
rs1(i).Value = rs2(i).Value'把B.mdb中的a表中的记录付给A.mdb的a表
Next
rs2.MoveNext
Loop
rs1.UpdateBatch strSql = "select distinct * into temTab from a"
myConn1.Execute strSql'找出A.mdb的a表中不重复的记录组成一个表temTab*********************执行到这出错了
strSql = "delete * from a"
myConn1.Execute strSql'删除A.mdb的a表中的所有记录
strSql = "insert into a select * from temTab"
myConn1.Execute strSql'把表temTab的记录插入到A.mdb的a表中
strSql = "drop table temTab"
myConn1.Execute strSql'删除表temTab
--------------------------------
用循环插入的方法,如果数据量比较大,那么会很慢的,用下面的方法吧: Dim ConnB As New ADODB.Connection
ConnB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\B.mdb;Persist Security Info=False"
ConnB.CursorLocation = adUseClient
ConnB.Open
ConnB.Execute ("INSERT INTO [" & App.Path & "\A.mdb].A SELECT * From B ")
myConn1.Execute strSql'找出A.mdb的a表中不重复的记录组成一个表
temTab*********************执行到这出错了
--------------------------------------------
代码看起来没有错误,是不是A.mdb里已经存在temTab表了?运行的时候提示的是什么错误?
再有你上面提出的ConnB.Execute ("INSERT INTO [" & App.Path & "\A.mdb].A SELECT * From B ")不知道是否可以在vb中用?strSql = "select distinct * from a"执行这条语句时,若a表中存在备注型的字段,那么就会提示“字段太小而不能接受所要添加的数据数量,试着插入或粘贴较少的数据”错误,这个该怎么办?
ConnB.Execute ("INSERT INTO [" & App.Path & "\A.mdb].A SELECT * From B ")
可以在vb里用,你自己可以测试看