The simply way to consolidate two bases...

[jeanpri]

Hello,

For the moment I have no idea of how to consolidate two .mdb bases...

This is what I want to achieve:
- A first .mdb base will be on a server, it is the "online" version
- A dozen users will have his own version of the base, the "offline" version, to work on it anywhere.
- On the offline version there is a "consolidate" button to merge the offline base data with the online version (new lines and modified lines will be added from one version to the another version). In this way everybody can have the same base version.

What is the simply way to consolidate theses bases ? (I know how to code in VBA if it is the best solution)



Thanks !

Jean

[June7]

I don't believe there is a simple way. I went through a similar issue. I tried to find 3rd party software that would accomplish synchronization and gave up - maybe you will have better luck there. If you are using Autonumber primary keys you will probably find this synchronization virtually impossible. I had to completely eliminate autonumber primary keys and base relationships on a real unique IDs. Our situation is simplified in that the 'field' files have a life that is limited by the construction season. At end of summer files are submitted to main office and records are appended to the master file. They will be all new records, no updates of existing data in the master. End of season reports are generated and the db probably sits untouched until next season.

In your case, I expect you want to import new records as well as update existing more frequently. This requires 2 sql actions to be executed - an INSERT SELECT where not already existing and UPDATE - for each table affected. If relationships are established in the Relationship builder set to enforce referential integrity, the sequence (which tables must be appended first) of appending new records will be dictated by the relationships.

You have a significant challenge ahead.

[jeanpri]

Hello, thanks for your answer !

It's seem not easy...

Why I can't use the "TransferDatabase" function with a macro? I have only one table to synchronize...

[June7]

I think TransferDatabase will only import the table, not append or update. Review http://office.microsoft.com/en-us/ac...001226306.aspx

Should be able to select the method as an action in macro or use DoCmd.TransferDatabase in VBA

[jeanpri]

Thanks. I found a tutorial here: http://www.databasejournal.com/featu...-Databases.htm

It's seems fitted to my application.