vba - Check if value has changed when moving data -

i have built macro moves values inside 10 different tables 1 database another. takes unique identifier columns "nid" , checks see if exists in new database, if there no match moves data if there match , exists doesn't.

this macro working fine however, check if value exists , if check each column changes , if there changes value's move new value over. example password changes on original database updating on new database.

some of tables have 50 columns manually looping each 1 going long macro, wondering if there easier way this? if not how go looping them?

here macro 1 table:

public function update1()      'open source database     dim dsource database     set dsource = currentdb      'open dest database     dim ddest database     set ddest = dao.opendatabase("c:\users\simon\documents\sellerdeck 2013\sites\dest\actiniccatalog.mdb")      'open source recordset     dim rsource recordset     set rsource = dsource.openrecordset("address", dbopenforwardonly)      'open dest recordset     dim rdest recordset     set rdest = ddest.openrecordset("address", dbopendynaset)      'loop through source recordset     while not rsource.eof          'look record in dest recordset         rdest.findfirst "ncustomerid = " & rsource.fields("ncustomerid") & ""          'if not found, copy record         if 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("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 field collection of recordset both comparison , copy:

option compare database option explicit  public function update1()     'temp field     dim ffield field     dim bcopy boolean      'open source database     dim dsource database     set dsource = currentdb      'open dest database     dim ddest database     set ddest = dao.opendatabase("c:\users\simon\documents\sellerdeck 2013\sites\dest\actiniccatalog.mdb")      'open source recordset     dim rsource recordset     set rsource = dsource.openrecordset("address", dbopenforwardonly)      'open dest recordset     dim rdest recordset     set rdest = ddest.openrecordset("address", dbopendynaset)      'loop through source recordset     while not rsource.eof          'reset copy flag         bcopy = false          'look record in dest recordset         rdest.findfirst "ncustomerid = " & rsource.fields("ncustomerid") & ""          if rdest.nomatch             'if not found, copy record             rdest.addnew             bcopy = true         else              'if found, check differences             each ffield in rsource.fields                 if rdest.fields(ffield.name) <> rsource.fields(ffield.name)                     rdest.edit                     bcopy = true                     exit                 end if             next ffield             set ffield = nothing         end if          'if copy flag set, copy record - ignore errors         if bcopy             each ffield in rsource.fields                 if not (ffield.attributes , dbautoincrfield)                     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