数据库比较

Else OldParam = rstTableOld.GetRows（1） 对于n = 0到rstTableNew.Fields.Count - 1 如果NewParam（n，0）= OldParam（n，0）然后 tx = 1 否则 如果IsNull（NewParam（n，0））和IsNull（OldParam（n，0））然后GoTo 200 rstParamChange.AddNew rstParamChange！SectorName = rstKonf.Fields（" SectorName"） rstParamChange！日期=日期 - 1 rstParamChange！id1 = NewParam（0,0） rstParamChange！id2 = NewParam（1,0） rstParamChange！id3 = NewParam（2,0） rstParamChange！id4 = NewParam（3,0） rstParamChange！id5 = NewParam（4,0） rstParamChange！Table = TableNew rstParamChange！Parameter = rstTableNew.Fields（n）.Name rstParamChange！new = NewParam（n，0） rstParamChange！old = OldParam（n，0） rstPar amChange.Update rstParamChange.Requery 结束如果 $ 下一页n&br > 结束如果 100 Loop $ 下一页 下一页 结束Sub 解决方案 我提前道歉，因为我还没读过通过你的代码详细了解。但我想首先回答几个问题。 你能发布表定义吗？我想我们需要了解索引，特别是。 您是在尝试查找整个记录是否相同，或者您是否有兴趣识别具有相同关键字段但是具有相同关键字段的记录在其他领域有所不同吗？ 您是否尝试过使用随Access提供的向导来构建查找重复查询？ 我要重新发布您的带有标签的代码，以提高可读性。我已经强调代码中的一些If / ElseIf条件似乎没有意义。你可以检查一下吗？ 展开 | 选择 | Wrap | 行号 我提前道歉，因为我还没有详细阅读你的代码。但我想首先回答几个问题。 你能发布表定义吗？我想我们需要了解索引，特别是。 您是在尝试查找整个记录是否相同，或者您是否有兴趣识别具有相同关键字段但是具有相同关键字段的记录在其他领域有所不同吗？ 您是否尝试过使用随Access提供的向导来构建查找重复查询？ 我要重新发布您的带有标签的代码，以提高可读性。我已经强调代码中的一些If / ElseIf条件似乎没有意义。你可以检查一下吗？ 展开 | 选择 | Wrap | 行号 也许我需要找另一个脚本来执行此操作......？！ Hi everyone! I have some questions, maybe someone can help me...I write script for table comparing, but it works wery slovly. There is the script, can anyone give some tip how can I make this process faster and if it is possible in VB. The idea is to compare two tables and if there is some distinction, then is written in third table. Some tables contains more than 200 000 rows and each row contain approximatelly 30 parameeters (columns).************************************************** *******************************************Option Compare Database Sub Parametru_Izmainas()Dim bssSiemens As DatabaseDim rstTableNew As Recordset, rstTableOld As Recordset, rstParamChange As RecordsetDim rstKonf As RecordsetDim n As Long, tx As Integer, a As IntegerDim fname1 As String, fname2 As String, fname3 As String, fname4 As StringDim TableNew As Variant, TableOld As String, TableList As VariantDim strCriteria As String, strCriteriaSect As StringDim NewParam As Variant, OldParam As VariantSet bssSiemens = CurrentDbSet rstParamChange = bssSiemens.OpenRecordset("Izmainas", dbOpenDynaset)Set rstKonf = bssSiemens.OpenRecordset("Konfiguracija", dbOpenDynaset) For a = 2 To 5''If a = 1 Then'' TableList = Array("Bsc") ElseIf a = 2 Then TableList = Array("Btsm") ElseIf a = 3 Then TableList = Array("Bts") ElseIf a = 4 Then TableList = Array("AdjC") ElseIf a = 5 Then TableList = Array("Chan")End If For Each TableNew In TableListTableOld = TableNew & "Old"Set rstTableNew = bssSiemens.OpenRecordset(TableNew)Set rstTableOld = bssSiemens.OpenRecordset(TableOld, dbOpenDynaset)If rstTableNew.RecordCount = 0 Then GoTo 300rstTableNew.MoveFirstfname1 = rstTableNew.Fields(0).Namefname2 = rstTableNew.Fields(1).Namefname3 = rstTableNew.Fields(2).Namefname4 = rstTableNew.Fields(3).Namefname5 = rstTableNew.Fields(4).NameDo While rstTableNew.EOF = False NewParam = rstTableNew.GetRows(1) id1 = NewParam(0, 0) id2 = NewParam(1, 0) id3 = NewParam(2, 0) id4 = NewParam(3, 0) id5 = NewParam(4, 0) If a = 2 Then strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" & id2 ElseIf a = 2 Then strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" & id2 & " AND [" & fname3 & "]=" & id3 ElseIf a = 4 Then strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" & id2 & " AND [" & fname3 & "]=" & id3 & " AND [" & fname4 & "]=" & id4 ElseIf a = 4 Then strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" & id2 & " AND [" & fname3 & "]=" & id3 & " AND [" & fname4 & "]=" & id4 & " AND [" & fname5 & "]=" & id5 Else strCriteria = "[" & fname1 & "]=" & id1 End If strCriteriaSect = "[bsc]=" & id1 & " AND [btsm]=" & id2 & " AND [bts]=" & id3 rstKonf.FindFirst strCriteriaSect rstTableOld.FindFirst strCriteria If rstTableOld.NoMatch Then rstParamChange.AddNew rstParamChange!Date = Date - 1 rstParamChange!id1 = id1 rstParamChange!id2 = id2 If a = 3 Then rstParamChange!id3 = id3 If a = 4 Then rstParamChange!id3 = id3 If a = 4 Then rstParamChange!id4 = id4 If a = 5 Then rstParamChange!id3 = id3 If a = 5 Then rstParamChange!id4 = id4 If a = 5 Then rstParamChange!id5 = id5 rstParamChange!Table = TableNew rstParamChange.Update rstParamChange.Requery GoTo 100 Else OldParam = rstTableOld.GetRows(1) For n = 0 To rstTableNew.Fields.Count - 1 If NewParam(n, 0) = OldParam(n, 0) Then tx = 1 Else If IsNull(NewParam(n, 0)) And IsNull(OldParam(n, 0)) Then GoTo 200 rstParamChange.AddNew rstParamChange!SectorName = rstKonf.Fields("SectorName") rstParamChange!Date = Date - 1 rstParamChange!id1 = NewParam(0, 0) rstParamChange!id2 = NewParam(1, 0) rstParamChange!id3 = NewParam(2, 0) rstParamChange!id4 = NewParam(3, 0) rstParamChange!id5 = NewParam(4, 0) rstParamChange!Table = TableNew rstParamChange!Parameter = rstTableNew.Fields(n).Name rstParamChange!new = NewParam(n, 0) rstParamChange!old = OldParam(n, 0) rstParamChange.Update rstParamChange.Requery End If 200 Next n End If 100Loop 300NextNext aEnd Sub 解决方案 I apologise in advance, as I haven''t read through your code in detail yet. But I would like to get a couple of questions answered first.Can you post the table definition? I think we''ll need to know about indexes, especially. Are you trying to find whether entire records are identical, or are you interested in identifying records which have the same key field(s) but are different in other fields? Have you tried using the Wizard supplied with Access, to build a "find duplicates query"? I''m going to re-post your code with tags around it for readability. I''ve underlined some of the If/ElseIf conditions in the code which don''t seem to make sense. Can you check them please? Expand|Select|Wrap|Line NumbersI apologise in advance, as I haven''t read through your code in detail yet. But I would like to get a couple of questions answered first.Can you post the table definition? I think we''ll need to know about indexes, especially. Are you trying to find whether entire records are identical, or are you interested in identifying records which have the same key field(s) but are different in other fields? Have you tried using the Wizard supplied with Access, to build a "find duplicates query"? I''m going to re-post your code with tags around it for readability. I''ve underlined some of the If/ElseIf conditions in the code which don''t seem to make sense. Can you check them please? Expand|Select|Wrap|Line NumbersMaybe I need to find another script to do this...?! 这篇关于数据库比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！