检查移动数据时,值已更改 [英] Check if value has changed when moving data
问题描述
我已经建立了移动值内约10个不同的表从一个数据库到另一个宏了。它需要一个唯一标识,所以说列NID和检查,看它是否已经存在于新的数据库中,如果没有匹配它移动的数据,如果有一个匹配,它已经存在,它没有。
本宏工作正常不过,我想它检查是否值已经存在,如果它要检查每列的任何变化,如果有变化的值的移动新值过。例如对新数据库的原始数据库更新密码更改。
某些表有多达50列,以手动循环每一个都将是一个很长的宏,我在想,如果有一个更简单的方法来做到这一点?如果不是我将如何去有关循环呢?
下面是我的宏为一个表:
公共功能UPDATE1()
开源数据库
昏暗d源数据库
设置d源= CurrentDb
打开DEST数据库
昏暗dDest数据库
设置dDest = DAO.OpenDatabase(C:\用户\西蒙\文件\ SellerDeck 2013 \网站\ DEST \ ActinicCatalog.mdb)
开源记录
昏暗RSOURCE作为记录
设置RSOURCE = dSource.OpenRecordset(地址,dbOpenForwardOnly)
打开DEST记录
昏暗rDest作为记录
设置rDest = dDest.OpenRecordset(地址,dbOpenDynaset)
通过源记录'循环
虽然没有rSource.EOF
在dest记录查找记录
rDest.FindFirstnCustomerID =与& rSource.Fields(nCustomerID)及
如果没有找到,复制纪录
如果rDest.NoMatch然后
rDest.AddNew
rDest.Fields(nCustomerID)= rSource.Fields(nCustomerID)
rDest.Fields(SNAME)= rSource.Fields(SNAME)
rDest.Fields(sLine2)= rSource.Fields(sLine2)
rDest.Fields(sLine4)= rSource.Fields(sLine4)
rDest.Fields(nCountryID)= rSource.Fields(nCountryID)
rDest.Fields(bValidInvoiceAddress)= rSource.Fields(bValidInvoiceAddress)
rDest.Fields(bValidDeliveryAddress)= rSource.Fields(bValidDeliveryAddress)
rDest.Fields(nStateID)= rSource.Fields(nStateID)
rDest.Fields(bExemptTax1)= rSource.Fields(bExemptTax1)
rDest.Fields(sExemptTax1Number)= rSource.Fields(sExemptTax1Number)
rDest.Fields(bExemptTax2)= rSource.Fields(bExemptTax2)
rDest.Fields(sExemptTax2Number)= rSource.Fields(sExemptTax2Number)
rDest.Fields(bPurge)= rSource.Fields(bPurge)
rDest.Fields(bChanged)= rSource.Fields(bChanged)
rDest.Fields(的NID)= rSource.Fields(的NID)
rDest.Fields(nTax1ID)= rSource.Fields(nTax1ID)
rDest.Fields(nTax2ID)= rSource.Fields(nTax2ID)
rDest.Fields(nResidential)= rSource.Fields(nResidential)
rDest.Fields(sCompanyName)= rSource.Fields(sCompanyName)
rDest.Fields(sLine1)= rSource.Fields(sLine1)
rDest.Fields(sLine3)= rSource.Fields(sLine3)
rDest.Fields(sPostal code)= rSource.Fields(sPostal code)
rDest.Fields(sEmailAddress)= rSource.Fields(sEmailAddress)
rDest.Fields(sFaxNumber)= rSource.Fields(sFaxNumber)
rDest.Fields(sFirstName)= rSource.Fields(sFirstName)
rDest.Fields(sFullName)= rSource.Fields(sFullName)
rDest.Fields(sLastName)= rSource.Fields(sLastName)
rDest.Fields(sMobileNumber)= rSource.Fields(sMobileNumber)
rDest.Fields(sSalutation)= rSource.Fields(sSalutation)
rDest.Fields(sTelephoneNumber)= rSource.Fields(sTelephoneNumber)
rDest.Fields(sTitle)= rSource.Fields(sTitle)
rDest.Update
结束如果
下一步源记录
rSource.MoveNext
蜿蜒
关闭DEST记录
rDest.Close
设置rDest =什么
关闭源记录
rSource.Close
设置RSOURCE =什么
关闭DEST数据库
dDest.Close
设置dDest =什么
关闭源数据库
dSource.Close
设置d源=什么
端功能
您可以使用记录的字段集合为比较和复制:
选项比较数据库
显式的选项
公共功能UPDATE1()
温度场
昏暗fField由于现场
昏暗BCOPY由于布尔
开源数据库
昏暗d源数据库
设置d源= CurrentDb
打开DEST数据库
昏暗dDest数据库
设置dDest = DAO.OpenDatabase(C:\用户\西蒙\文件\ SellerDeck 2013 \网站\ DEST \ ActinicCatalog.mdb)
开源记录
昏暗RSOURCE作为记录
设置RSOURCE = dSource.OpenRecordset(地址,dbOpenForwardOnly)
打开DEST记录
昏暗rDest作为记录
设置rDest = dDest.OpenRecordset(地址,dbOpenDynaset)
通过源记录'循环
虽然没有rSource.EOF
重置副本标志
BCOPY =假
在dest记录查找记录
rDest.FindFirstnCustomerID =与& rSource.Fields(nCustomerID)及
如果rDest.NoMatch然后
如果没有找到,复制纪录
rDest.AddNew
BCOPY = TRUE
其他
如果找到,比较两者的差异
对于每个fField在rSource.Fields
如果rDest.Fields(fField.Name)<> rSource.Fields(fField.Name)然后
rDest.Edit
BCOPY = TRUE
退出对于
结束如果
接下来fField
设置fField =什么
结束如果
如果拷贝标志设置,复制纪录 - 忽略错误
如果BCOPY然后
对于每个fField在rSource.Fields
如果不是(fField.Attributes而dbAutoIncrField)然后
在错误恢复下一页
rDest.Fields(fField.Name)= rSource.Fields(fField.Name)
对错误转到0
结束如果
接下来fField
设置fField =什么
rDest.Update
结束如果
下一步源记录
rSource.MoveNext
蜿蜒
关闭DEST记录
rDest.Close
设置rDest =什么
关闭源记录
rSource.Close
设置RSOURCE =什么
关闭DEST数据库
dDest.Close
设置dDest =什么
关闭源数据库
dSource.Close
设置d源=什么
端功能
I have built a macro already that moves values inside about 10 different tables from one database to another. It takes a unique identifier so say columns "nid" and checks to see if it already exists in the new database, if there is no match it moves the data if there is a match and it already exists it doesn't.
This macro is working fine however, I would like it to check if the value already exists and if it does to check each column for any changes and if there are changes to the value's to move the new value over. For example password changes on the original database updating on the new database.
Some of the tables have up to 50 columns so manually looping each one is going to be a very long macro, I was wondering if there was an easier way to do this? If not how would I go about looping them?
Here is my macro for one table:
Public Function update1()
'Open source database
Dim dSource As Database
Set dSource = CurrentDb
'Open dest database
Dim dDest As Database
Set dDest = DAO.OpenDatabase("C:\Users\simon\Documents\SellerDeck 2013\Sites\dest\ActinicCatalog.mdb")
'Open source recordset
Dim rSource As Recordset
Set rSource = dSource.OpenRecordset("Address", dbOpenForwardOnly)
'Open dest recordset
Dim rDest As Recordset
Set rDest = dDest.OpenRecordset("Address", dbOpenDynaset)
'Loop through source recordset
While Not rSource.EOF
'Look for record in dest recordset
rDest.FindFirst "nCustomerID = " & rSource.Fields("nCustomerID") & ""
'If not found, copy record
If rDest.NoMatch Then
rDest.AddNew
rDest.Fields("nCustomerID") = rSource.Fields("nCustomerID")
rDest.Fields("sName") = rSource.Fields("sName")
rDest.Fields("sLine2") = rSource.Fields("sLine2")
rDest.Fields("sLine4") = rSource.Fields("sLine4")
rDest.Fields("nCountryID") = rSource.Fields("nCountryID")
rDest.Fields("bValidInvoiceAddress") = rSource.Fields("bValidInvoiceAddress")
rDest.Fields("bValidDeliveryAddress") = rSource.Fields("bValidDeliveryAddress")
rDest.Fields("nStateID") = rSource.Fields("nStateID")
rDest.Fields("bExemptTax1") = rSource.Fields("bExemptTax1")
rDest.Fields("sExemptTax1Number") = rSource.Fields("sExemptTax1Number")
rDest.Fields("bExemptTax2") = rSource.Fields("bExemptTax2")
rDest.Fields("sExemptTax2Number") = rSource.Fields("sExemptTax2Number")
rDest.Fields("bPurge") = rSource.Fields("bPurge")
rDest.Fields("bChanged") = rSource.Fields("bChanged")
rDest.Fields("nID") = rSource.Fields("nID")
rDest.Fields("nTax1ID") = rSource.Fields("nTax1ID")
rDest.Fields("nTax2ID") = rSource.Fields("nTax2ID")
rDest.Fields("nResidential") = rSource.Fields("nResidential")
rDest.Fields("sCompanyName") = rSource.Fields("sCompanyName")
rDest.Fields("sLine1") = rSource.Fields("sLine1")
rDest.Fields("sLine3") = rSource.Fields("sLine3")
rDest.Fields("sPostalCode") = rSource.Fields("sPostalCode")
rDest.Fields("sEmailAddress") = rSource.Fields("sEmailAddress")
rDest.Fields("sFaxNumber") = rSource.Fields("sFaxNumber")
rDest.Fields("sFirstName") = rSource.Fields("sFirstName")
rDest.Fields("sFullName") = rSource.Fields("sFullName")
rDest.Fields("sLastName") = rSource.Fields("sLastName")
rDest.Fields("sMobileNumber") = rSource.Fields("sMobileNumber")
rDest.Fields("sSalutation") = rSource.Fields("sSalutation")
rDest.Fields("sTelephoneNumber") = rSource.Fields("sTelephoneNumber")
rDest.Fields("sTitle") = rSource.Fields("sTitle")
rDest.Update
End If
'Next source record
rSource.MoveNext
Wend
'Close dest recordset
rDest.Close
Set rDest = Nothing
'Close source recordset
rSource.Close
Set rSource = Nothing
'Close dest database
dDest.Close
Set dDest = Nothing
'Close source database
dSource.Close
Set dSource = Nothing
End Function
You can use the field collection of the recordset for both comparison and copy:
Option Compare Database
Option Explicit
Public Function update1()
'Temp field
Dim fField As Field
Dim bCopy As Boolean
'Open source database
Dim dSource As Database
Set dSource = CurrentDb
'Open dest database
Dim dDest As Database
Set dDest = DAO.OpenDatabase("C:\Users\simon\Documents\SellerDeck 2013\Sites\dest\ActinicCatalog.mdb")
'Open source recordset
Dim rSource As Recordset
Set rSource = dSource.OpenRecordset("Address", dbOpenForwardOnly)
'Open dest recordset
Dim rDest As Recordset
Set rDest = dDest.OpenRecordset("Address", dbOpenDynaset)
'Loop through source recordset
While Not rSource.EOF
'Reset copy flag
bCopy = False
'Look for record in dest recordset
rDest.FindFirst "nCustomerID = " & rSource.Fields("nCustomerID") & ""
If rDest.NoMatch Then
'If not found, copy record
rDest.AddNew
bCopy = True
Else
'If found, check for differences
For Each fField In rSource.Fields
If rDest.Fields(fField.Name) <> rSource.Fields(fField.Name) Then
rDest.Edit
bCopy = True
Exit For
End If
Next fField
Set fField = Nothing
End If
'If copy flag is set, copy record - ignore errors
If bCopy Then
For Each fField In rSource.Fields
If Not (fField.Attributes And dbAutoIncrField) Then
On Error Resume Next
rDest.Fields(fField.Name) = rSource.Fields(fField.Name)
On Error Goto 0
End If
Next fField
Set fField = Nothing
rDest.Update
End If
'Next source record
rSource.MoveNext
Wend
'Close dest recordset
rDest.Close
Set rDest = Nothing
'Close source recordset
rSource.Close
Set rSource = Nothing
'Close dest database
dDest.Close
Set dDest = Nothing
'Close source database
dSource.Close
Set dSource = Nothing
End Function
这篇关于检查移动数据时,值已更改的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!