Access DB Synchronisation

Objective: We receive an Access 2003 database SOURCE on a weekly basis. The database contains information about investment funds. We would like to synchronise these funds into another Access database TARGET that contains funds from multiple sources and has a different data model. The synchronisation is a one way synchronisation from SOURCE to TARGET where: (a) New funds will be added; (b) Existing funds will be updated. Technology: The synchronisation script has to be written as an Access 2003 database (called SYNC) using Access VBA and/or SQL. No other technology. The SYNC database will then be run as an automated task on the user’s PC. At every operation, a log entry (output to ASCII file) should be created Data Model: Database SOURCE contains 3 tables. • FNA_FUND contains all funds • FNA_FUND_PERFORMANCE contains monthly performance figures for a fund (1:n) • FNA_PEER_GROUP will not be used Database TARGET contains the following relevant tables: • MASTERNAME contains the fund names • Information contains additional information about each fund (1:1) • Performance contains all performance figures of each fund (1:n) Synchronisation Algorithm: The Access database SYNC will be started by an automated task on the user’s machine. The following algorithm should be launched as an autoexecuted task once the database is open 1. Ensure TARGET and SOURCE do exist. If not log out the error and abort. Make a copy of TARGET to a save location and add the current date to the file name (using variables). The name of the backup should be “Backup “&filename&”(“&date)&”)”. 2. Loop through each fund from SOURCE.FNA_FUND where LipperID >= 35’000’000 and LipperID < 36’000’000 (other funds with LipperIDs that are smaller or higher will not be considered) 3. Check whether fund in table SOURCE.FNA_FUND already exists in table [login to view URL] If not exists [login to view URL] in field DataVendorID from table [login to view URL] where DataVendorName=”CS”. Only check for records where the DataVendorName=”CS” (as other vendors might be coincidence use the same ID and we want to exclude other vendors). 4. If fund does not exist in TARGET, insert the fund in the TARGET database. 4.1 Insert a new record in [login to view URL] and set [login to view URL] = FNA_FUND.FundName. [login to view URL] will be set automatically by Access (autonumber(. Set IsUsed = 0. 4.2 Insert a new record with the [login to view URL] that was just created in table TARGET.Information. Set [login to view URL] = [login to view URL] Set [login to view URL] = “USD” Set LastUpdated = Now() Set DataVendorName =”CS” Set DataVendorID = [login to view URL] (this effectively map both databases) (All other fields are automatically set by default to their default values) 4.3 Insert a new record in TARGET.UserCheck2. Ensure [login to view URL] is inserted in TARGET.UserCheck2.ID. Set [login to view URL] = -1 Set LastUpdated = Now() Set DataVendorName =”CS” Set DataVendorID = [login to view URL] (this effectively map both databases) (All other fields are automatically set by default to their default values) Create a log entry “New fund [login to view URL] successfully added” 5. If fund already exists in TARGET, update name if there is any name change If [login to view URL] <> [login to view URL] Then [login to view URL] = [login to view URL] Set [login to view URL] = [login to view URL] Create a log entry “Fund [login to view URL] was changed to [login to view URL]” 6. Now that fund was created, update all performance figures (i.e. monthly performance data). Probably the best is to first delete all performance figures from the table [login to view URL] where [login to view URL] = [login to view URL] and then to re-insert all performance figures from FNA_FUND_PERFORMANCE for the [login to view URL] is required as performance figures might change Insert into [login to view URL] record where [login to view URL] = [login to view URL] [login to view URL] = SOURCE. FNA_FUND_PERFORMANCE .PerfMonth Performance.Return= SOURCE. [login to view URL] [login to view URL] = 0 [login to view URL] = 0 [login to view URL] = 0 LastUpdated = Now() Create a log entry “Fund [login to view URL] performance figures successfully updated 7. Now, the TARGET database should be copied to a safe location (please provide a variable where path can be entered) and then compacted, Access database SYNC can then close itself Other Requirements: Ensure that all pathes and filenames i.e. (i) SOURCE, (ii) TARGET, (iii) the logfile, (iv) the path where to save the backuped database are all stored in variables that can easily be changed Ensure that you implement every fund operation as a transaction with BEGIN TRANSACTION so that if any error happens one can roll back (I do not know if this is possible with external tables). Also if an error happens, this should be logged with the fund name for which the error happened. The script should then continue execution for the remaining funds.