Access DB Synchronisation

Encerrado Postado Mar 18, 2010 Pago na entrega
Encerrado Pago na entrega

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 [url removed, login to view]

If not exists [url removed, login to view] in field DataVendorID from table [url removed, login to view] 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 [url removed, login to view] and set [url removed, login to view] = FNA_FUND.FundName. [url removed, login to view] will be set automatically by Access (autonumber(. Set IsUsed = 0.

4.2 Insert a new record with the [url removed, login to view] that was just created in table TARGET.Information.

Set [url removed, login to view] = [url removed, login to view]

Set [url removed, login to view] = “USD”

Set LastUpdated = Now()

Set DataVendorName =”CS”

Set DataVendorID = [url removed, login to view] (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 [url removed, login to view] is inserted in TARGET.UserCheck2.ID.

Set [url removed, login to view] = -1

Set LastUpdated = Now()

Set DataVendorName =”CS”

Set DataVendorID = [url removed, login to view] (this effectively map both databases)

(All other fields are automatically set by default to their default values)

Create a log entry “New fund [url removed, login to view] successfully added”

5. If fund already exists in TARGET, update name if there is any name change

If [url removed, login to view] <> [url removed, login to view] Then

[url removed, login to view] = [url removed, login to view]

Set [url removed, login to view] = [url removed, login to view]

Create a log entry “Fund [url removed, login to view] was changed to [url removed, login to view]”

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 [url removed, login to view] where [url removed, login to view] = [url removed, login to view] and then to re-insert all performance figures from FNA_FUND_PERFORMANCE for the [url removed, login to view] is required as performance figures might change

Insert into [url removed, login to view] record where

[url removed, login to view] = [url removed, login to view]

[url removed, login to view] = SOURCE. FNA_FUND_PERFORMANCE .PerfMonth

Performance.Return= SOURCE. [url removed, login to view]

[url removed, login to view] = 0

[url removed, login to view] = 0

[url removed, login to view] = 0

LastUpdated = Now()

Create a log entry “Fund [url removed, login to view] 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.

Microsoft Access SQL Visual Basic

ID do Projeto: #637672

Sobre o projeto

18 propostas Projeto remoto Ativo em Apr 27, 2010

18 freelancers estão ofertando em média $199 nesse trabalho

sandymra

Please check PM!

$188 USD in 4 dias
(50 Comentários)
6.4
inosov

Hi, Please check PMB

$150 USD in 3 dias
(4 Comentários)
4.1
IanSmithISA

Good morning, I have PMed you a full proposal I am a UK/England based developer and I have been a programmer for almost 30 years. I have recently finished a long term contract with an insurance company where I Mais

$250 USD in 3 dias
(2 Comentários)
3.9
alekseymihalych

I'd like to work on this project. Need to operate with samples of databases.

$230 USD in 10 dias
(3 Comentários)
1.2
XCOREDEV

Hello I am an experienced developer with over 10 years programing experience. I have worked with Access since Access 95 to the current version 2007. I would like to be considered for this project. Regards

$250 USD in 20 dias
(1 Comentário)
0.0
daliahany

HI, I can do using VBA and SQL in an efficient and clean way. plz check samples of my work at the PM Regards, Dalia Hany

$240 USD in 4 dias
(0 Comentários)
0.0
applied646

I have over 20 years experience in the technologies that it appears would be used for your project. One requirement is that the project be done in Excel. I have over 20 years professional experience with the MSOffice s Mais

$300 USD in 10 dias
(0 Comentários)
0.0
RoLaAus

Ready to start immediately and will deliver a stable and fully functional solution that exceeds your expectations in a timely manner. Please check your PM for further details.

$200 USD in 7 dias
(0 Comentários)
0.0
activesoftwareco

Your project would be made easier if you fed data into a SQL Server database. If access is what you prefer to stay in, I can deliver a solution for you to sync data across 2 or as many access databases. In the event th Mais

$500 USD in 7 dias
(0 Comentários)
0.0
softwareeng001

I AM INTERESTED IN THIS PROJECT I WILL TRY MY BEST TO COMPLETE THIS PROJECT ON TIME.

$30 USD in 3 dias
(0 Comentários)
0.0
masamad2010

Dear Sir, I am interested in this project all of your terms & conditions. I am confident to perfect delivery about his project. Regards, Masamad2010

$200 USD in 7 dias
(0 Comentários)
0.0
sankalp70000

please let us know when we have to start the work for u asap .We r also available on Skype add us as sankalpsaxena7

$35 USD em 1 dia
(0 Comentários)
0.0
rmilgate

I hold a BS in Computer-Based Management Systems and have over 15 years job experience in the industry. I have designed/developed and implemented MS Access databases from MS Access 95 through MS Access 2007. I am extre Mais

$200 USD in 3 dias
(0 Comentários)
0.0
shahidwaryam

I have excellent professional skill.I can help you to make this project.

$60 USD in 8 dias
(0 Comentários)
0.0
self

Hello, check the PM please, thanks

$75 USD in 2 dias
(0 Comentários)
0.0
sachindrasangal

pl check pm....

$300 USD in 15 dias
(0 Comentários)
0.0