Compare two files and Update or Add

Option Compare Database

Function Assurant_Add_New()

'Read Assurant_Load Table

Dim db As DAO.Database

Dim rs As DAO.Recordset

Set db = CurrentDb

'Set rs = New ADODB.Recordset

Set rs = db.OpenRecordset("select * from Assurant_Load2")

'DoCmd.GoToRecord , "data", acNewRec

rs.OpenRecordset

Exist_Flag = 0

With rs

While (Not .EOF)

TRACK_ID1 = rs.Fields("[TRACK_ID]")

ID1 = rs.Fields("[ID]")

Contract1 = rs.Fields("[Contract]")

Company_Name1 = rs.Fields("[Company_Name]")

Policy_ID1 = rs.Fields("[Policy_ID]")

Equipment_Value1 = rs.Fields("[Equipment_Value]")

Insurance_Company1 = rs.Fields("[Insurance_Company]")

Orig_Date1 = rs.Fields("[Orig_Date]")

Collateral_Class1 = rs.Fields("[Collateral_Class]")

Eff_Date1 = rs.Fields("[Eff_Date]")

Exp_Date1 = rs.Fields("[Exp_Date]")

Equipment_Code1 = rs.Fields("[Equipment_Code]")

Tracking_Class1 = rs.Fields("[Tracking_Class]")

Collateral_Description1 = rs.Fields("[Collateral_Description]")

Cancel_Date1 = rs.Fields("[Cancel_Date]")

Completed_Date1 = rs.Fields("[Completed_Date]")

Completed_By1 = rs.Fields("[Completed_By]")

Comments1 = rs.Fields("[Comments]")

Load_Date1 = rs.Fields("[Load_Date]")

'Reading Assurant_Worklist2 to verify the record exist***********

Dim db3 As DAO.Database

Dim rs3 As DAO.Recordset

Set db3 = CurrentDb

'Set rs = New ADODB.Recordset

Set rs3 = db3.OpenRecordset("select * from Assurant_Worklist2")

'DoCmd.GoToRecord , "data", acNewRec

rs3.OpenRecordset

With rs3

While (Not .EOF)

ID3 = rs3.Fields("[ID]")

TRACK_ID3 = rs3.Fields("[TRACK_ID]")

If TRACK_ID1 = TRACK_ID3 Then ' FLAG is Yes to Exist

Exist_Flag = 1

TRACK_IDFLAG1 = TRACK_ID1

IDFLAG1 = ID1

ContractFLAG1 = Contract1

Company_NameFLAG1 = Company_Name1

Policy_IDFLAG1 = Policy_ID1

Equipment_ValueFLAG1 = Equipment_Value1

Insurance_CompanyFLAG1 = Insurance_Company1

Orig_DateFLAG1 = Orig_Date1

Collateral_ClassFLAG1 = Collateral_Class1

Eff_DateFLAG1 = Eff_Date1

Exp_DateFLAG1 = Exp_Date1

Equipment_CodeFLAG1 = Equipment_Code1

Tracking_ClassFLAG1 = Tracking_Class1

Collateral_DescriptionFLAG1 = Collateral_Description1

Cancel_DateFLAG1 = Cancel_Date1

Completed_DateFLAG1 = Completed_Date1

Completed_ByFLAG1 = Completed_By1

CommentsFLAG1 = Comments1

Load_DateFLAG1 = Load_Date1

GoTo ContinueProcess

End If ' FLAG is Yes to Exist

If TRACK_ID1 <> TRACK_ID3 Then ' FLAG is Yes to Exist

Exist_Flag = 0

TRACK_IDFLAG1 = TRACK_ID1

IDFLAG1 = ID1

ContractFLAG1 = Contract1

Company_NameFLAG1 = Company_Name1

Policy_IDFLAG1 = Policy_ID1

Equipment_ValueFLAG1 = Equipment_Value1

Insurance_CompanyFLAG1 = Insurance_Company1

Orig_DateFLAG1 = Orig_Date1

Collateral_ClassFLAG1 = Collateral_Class1

Eff_DateFLAG1 = Eff_Date1

Exp_DateFLAG1 = Exp_Date1

Equipment_CodeFLAG1 = Equipment_Code1

Tracking_ClassFLAG1 = Tracking_Class1

Collateral_DescriptionFLAG1 = Collateral_Description1

Cancel_DateFLAG1 = Cancel_Date1

Completed_DateFLAG1 = Completed_Date1

Completed_ByFLAG1 = Completed_By1

CommentsFLAG1 = Comments1

Load_DateFLAG1 = Load_Date1

End If ' FLAG is Yes to Exist

.MoveNext

Wend

End With

ContinueProcess:

rs3.Close

If Exist_Flag = 1 Then

'UPDATE Assurant_Worklist2

Dim db2 As DAO.Database

Dim rs2 As DAO.Recordset

Set db2 = CurrentDb

'Set rs = New ADODB.Recordset

Set rs2 = db2.OpenRecordset("select * from Assurant_Worklist2")

'DoCmd.GoToRecord , "data", acNewRec

rs2.OpenRecordset

ID2 = rs2.Fields("[ID]")

TRACK_ID2 = rs2.Fields("[TRACK_ID]")

Contract2 = rs2.Fields("[Contract]")

Company_Name2 = rs2.Fields("[Company_Name]")

Policy_ID2 = rs2.Fields("[Policy_ID]")

Equipment_Value2 = rs2.Fields("[Equipment_Value]")

Insurance_Company2 = rs2.Fields("[Insurance_Company]")

Orig_Date2 = rs2.Fields("[Orig_Date]")

Collateral_Class2 = rs2.Fields("[Collateral_Class]")

Eff_Date2 = rs2.Fields("[Eff_Date]")

Exp_Date2 = rs2.Fields("[Exp_Date]")

Equipment_Code2 = rs2.Fields("[Equipment_Code]")

Tracking_Class2 = rs2.Fields("[Tracking_Class]")

Collateral_Description2 = rs2.Fields("[Collateral_Description]")

Cancel_Date2 = rs2.Fields("[Cancel_Date]")

Completed_Date2 = rs2.Fields("[Completed_Date]")

Completed_By2 = rs2.Fields("[Completed_By]")

Comments2 = rs2.Fields("[Comments]")

Load_Date2 = rs2.Fields("[Load_Date]")

Dim StrSQL As String

DoCmd.SetWarnings False

StrSQL = "UPDATE Assurant_Worklist2 SET Company_Name = '" & Company_Name1 & "' WHERE TRACK_ID = '" & TRACK_IDFLAG1 & "' "

DoCmd.RunSQL StrSQL

StrSQL = "UPDATE Assurant_Worklist2 SET Policy_ID = '" & Policy_ID1 & "' WHERE TRACK_ID = '" & TRACK_IDFLAG1 & "' "

DoCmd.RunSQL StrSQL

StrSQL = "UPDATE Assurant_Worklist2 SET Equipment_Value = '" & Equipment_Value1 & "' WHERE TRACK_ID = '" & TRACK_IDFLAG1 & "' "

DoCmd.RunSQL StrSQL

StrSQL = "UPDATE Assurant_Worklist2 SET Insurance_Company = '" & Insurance_Company1 & "' WHERE TRACK_ID = '" & TRACK_IDFLAG1 & "' "

DoCmd.RunSQL StrSQL

StrSQL = "UPDATE Assurant_Worklist2 SET Orig_Date = '" & Orig_Date1 & "' WHERE TRACK_ID = '" & TRACK_IDFLAG1 & "' "

DoCmd.RunSQL StrSQL

StrSQL = "UPDATE Assurant_Worklist2 SET Collateral_Class = '" & Collateral_Class1 & "' WHERE TRACK_ID = '" & TRACK_IDFLAG1 & "' "

DoCmd.RunSQL StrSQL

StrSQL = "UPDATE Assurant_Worklist2 SET Eff_Date = '" & Eff_Date1 & "' WHERE TRACK_ID = '" & TRACK_IDFLAG1 & "' "

DoCmd.RunSQL StrSQL

StrSQL = "UPDATE Assurant_Worklist2 SET Exp_Date = '" & Exp_Date1 & "' WHERE TRACK_ID = '" & TRACK_IDFLAG1 & "' "

DoCmd.RunSQL StrSQL

StrSQL = "UPDATE Assurant_Worklist2 SET Equipment_Code = '" & Equipment_Code1 & "' WHERE TRACK_ID = '" & TRACK_IDFLAG1 & "' "

DoCmd.RunSQL StrSQL

StrSQL = "UPDATE Assurant_Worklist2 SET Collateral_Description = '" & Collateral_Description1 & "' WHERE TRACK_ID = '" & TRACK_IDFLAG1 & "' "

DoCmd.RunSQL StrSQL

StrSQL = "UPDATE Assurant_Worklist2 SET Cancel_Date = '" & Cancel_Date1 & "' WHERE TRACK_ID = '" & TRACK_IDFLAG1 & "' "

DoCmd.RunSQL StrSQL

DoCmd.SetWarnings True

rs2.Close

End If

If Exist_Flag = 0 Then ' FLAG is NO to Exist

'INSERT INTO Assurant_Worklist2

Set db2 = CurrentDb

'Set rs = New ADODB.Recordset

Set rs2 = db2.OpenRecordset("select * from Assurant_Worklist2")

'DoCmd.GoToRecord , "data", acNewRec

rs2.OpenRecordset

StrSQL = "INSERT INTO Assurant_Worklist2 (TRACK_ID, Contract, Company_Name, Policy_ID, Equipment_Value, Insurance_Company, Orig_Date, Collateral_Class, Eff_Date, Exp_Date, Equipment_Code, Tracking_Class, Collateral_Description, Cancel_Date, Completed_Date, Completed_By, Comments, Load_Date) " & _

" VALUES ('" & TRACK_IDFLAG1 & "', '" & ContractFLAG1 & "', '" & Company_NameFLAG1 & "', '" & Policy_IDFLAG1 & "', '" & Equipment_ValueFLAG1 & "', '" & Insurance_CompanyFLAG1 & "', '" & Orig_DateFLAG1 & "', '" & Collateral_ClassFLAG1 & "', '" & Eff_DateFLAG1 & "', '" & Exp_DateFLAG1 & "', '" & Equipment_CodeFLAG1 & "', '" & Tracking_ClassFLAG1 & "', '" & Collateral_DescriptionFLAG1 & "', '" & Cancel_DateFLAG1 & "', '" & Completed_DateFLAG1 & "', '" & Completed_ByFLAG1 & "', '" & CommentsFLAG1 & "', '" & Load_DateFLAG1 & "' );"

DoCmd.SetWarnings False

DoCmd.RunSQL StrSQL

DoCmd.SetWarnings True

rs2.Close

End If ' FLAG is NO to Exist

.MoveNext

Wend

End With

rs.Close

End Function