Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2008
    Posts
    6

    Unanswered: ms Access Syncronization

    Hello everyone,

    I have this challenge in my HR Application. See below:

    My company has remote construction sites across the country where Employees activities need to be tracked but, these sites are not in anyway physically connected with one another and they want us to deploy my HR Application in some of these sites.

    THis is the challenge:
    We need the Employee Data in Site A to be syncronized with a copy of Employee Data for Site A that is in the Headoffice. The reason we have to syncronize is because, the Headoffice could add/edit Employee for the Site A, and and also those in the Remote Site A could also add/edit the copy of their own Employee Data

    My Approach to the Challenge:
    I have suggested that I will deploy the HR Application in each of these sites and keep a copy of each (instance) site in the Headoffice. Therefore, when Employee Data is changed in Site A for example, a copy of it is sent to Headoffice and someone in Headoffice will use Synronization tool to update the data.

    But unfortunately, i have not come across a synronization tool that can only update the changes in the two files.

    Is there a solution that you can suggest to help me out.

    Warm regards,

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    assuming you have a link between the two locations
    store a last edited date in each location
    store a last synchronised date in either or both locations
    run a batch process, say over night which copies everything from A to B which has a last edited after the last synchronisation, and vice versa
    devise a strategy to to resolve any conflicts where both locations may have changed the same data element.
    store our last synchronised date.

    better yet create an audit log identfying what element has actually been changed. send that audit log to the other location and run it there.

    in the past ive created the SQL as the audit log itself
    eg
    insert into employees (my,column,list) values (my,data,list)
    update employees set mycolumn=newvalue where ID=blah
    update rates set hourlyrate = x.xx where emptype = yyyyy
    and so on.
    you export the file.. then after the export has completed (AND the import succeeded or backed up then delete the audit log.
    the log fiel idea works if there is chance that you have two locations which can change stuff.. byut it like the other approach turns to a crock if two people change data.. you have to decide which is the master data.

    I wouldn't keep separate copies of the data at head office.. keep a single copy of data but identify what site they are on in that data so HR only update one set of data.

    the 'best' alternative woudl be to have a linked single system where all locations are connected directly to the same single datasource and cut out all the opportunity for cockups by synchronising between two separate systems
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Agreed... especially with that last sentence.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Oct 2008
    Posts
    6

    Thumbs up MS Access Syncrozation

    Thanks for your suggestions. I would have loved to go with your last suggestion but presently there are no internet link in most of these locations because they are out of town and rural.

    This is the reason im considering using Replication and Syncronization menu in MS Access to syncronise the database(Back end file). I dont know if it is a tool that is less prone to error or if it is reasonable.

    I have a scenario, here it:
    I want to go ahead and install the application in these locations and someone could take file and zip so that it could be sent to Head Quarter as an attachement via email, so that some in the HQ will run the syncronization so that the file will be updated. Also, if the file at the HQ also changes, they could also send the file back so that the remote location will be update.

    My fear:
    I dont know if this tool is less prone to error. I need someone that has used it to suggest if it is a reasonable approach. Because, with what i have read (article) about the Replication Menu, it sounds good but i want professionals to tell that it is a good approach.

    Thank you very much for your suggestion once again, it is highly appreciated.

    Samson

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    there are no internet link in most of these locations because they are out of town and rural
    someone could take file and zip so that it could be sent to Head Quarter as an attachement via email
    Now how would that e-mail be sent if there is no internet link?

    I would not be confident at all with using replication to handle the data over multiple sites like you want to do.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by samomo
    This is the reason im considering using Replication and Syncronization menu in MS Access to syncronise the database(Back end file). I dont know if it is a tool that is less prone to error or if it is reasonable.

    I have a scenario, here it:
    I want to go ahead and install the application in these locations and someone could take file and zip so that it could be sent to Head Quarter as an attachement via email, so that some in the HQ will run the syncronization so that the file will be updated. Also, if the file at the HQ also changes, they could also send the file back so that the remote location will be update.

    My fear:
    I dont know if this tool is less prone to error. I need someone that has used it to suggest if it is a reasonable approach. Because, with what i have read (article) about the Replication Menu, it sounds good but i want professionals to tell that it is a good approach.

    Thank you very much for your suggestion once again, it is highly appreciated.

    Samson
    If you plan to use MSAccess Replication, plan on some possible late nights working, especially if it's a fairly large application. For MSAccess replication:

    1. Replication creates an ungodly large positive/negative "autonumber" (in all the tables) to synchronize the records and is highly prone to errors (you'll have autonumbers in the range of like -234,343,221 to 432,322,123!!) One of our competitors used replication (which our company eventually took over and I ended up maintaining) and they had a very difficult time keeping a less than 1% discrepancy rate (required) on their data totals due to the difficulty with these large autonumbers and maintaining relationships in the tables for consistent data totals. There were many times when "orphaned" records were produced. I ended up re-creating the whole mdb (without replication) just to maintain consistent data totals (which is a pain in itself to convert all the very large non-static autonumbers to static autonumbers.) After removing the whole replication process, we then used citrix instead to have the user's connect remotely to our server. If you do use replication, the best advice I can give you (other than to simply not use it) is to keep the number of relational tables you have to a minimum to avoid orphaned records.

    2. If someone might have a slow dialup internet connection, turn-off auto-synchronization and create a manual process (unless again, the dataset is small.) It's unbearably slow to auto-synchronize large recordsets (and also coding changes) to slow internet connections (and even some fast connections.) Even with small datasets, a small hiccup in the connection when someone is synchronizing their data to the "master" can be disasterous on your data!! (although if you're intent on using replication and I haven't convinced you otherwise, preferably emailing a "spawned" mdb versus synchronizing it over the internet "might" be a better option but then you're defeating the purpose of the whole synchronization process and better off emailing datasets to import/export to a non-replicated mdb.)

    3. Make plenty of backups, especially on the "Master" mdb (BEFORE "spawning/replicating" it.) The whole process of maintaining a "master/slave" mdb's for the replication process can be cumbersome and there are many times when I had to deal with re-creating the master (from my backup) because of some error or another with creating the "slave nodes" from the master. "Slave" mdb's easily get corrupted and will again, cause problems when synchronizing code changes on slow connections (especially on table design changes!) Regardless of the type of "node" option you select for replication. Again, hiccups during any of the synchranization process will be your worst enemy. And I wouldn't suggest using emails to send data/coding updates for a replicated mdb (large recordset sizes can produce some large attachments) - and you still must deal with the large autonumbers which will cause you many headaches. Again, you're much better off simply sending emails with datasets to import/export into a non-replicated mdb versus a replicated mdb (although this can be a pain to update the relational records and maintain data integrity unless you create a good import/export process - not forgetting to mention duplicated record checking.) I tried this in leui of replication and it worked "fairly" well but it wasn't ideal (but better than replication.)

    MSAccess Replication in my opinion is a failed attempt at Microsoft's creation to develop a system to "synchronize" from a "master" mdb to other mdb's at different locations. You'll find that not a lot of developers use replication because of all the problems maintaining it. It is simply (again, in my opinion), not a good option for a large mdb which may have continuous updates to the code. Especially the large "autonumbers" it creates to "try" and maintain relationships between tables and especially when having to synchronize table design changes! I personally, would avoid it - there are much easier methods (ie. using citrix or even remote desktop connection or any other methods where users connect to a network without any kind of "spawning/sychronization" processes from a "master" mdb file). If you've never worked with replication before, plan on again, spending some late nights working. I have yet to see an MSAccess replication process work without flaws which produced some late nights for the developers. In theory it may sound appealing but in real-world application, it can be a nightmare to work with.
    Last edited by pkstormy; 10-11-08 at 10:18.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Oct 2008
    Location
    New Jersey
    Posts
    1

    Web Solution

    Hi,

    The other replies were interesting and have value. However, each is prone to error and you can't afford that in any production application.

    I suggest, for this application, that you create a web based app that can be updated over the inter/intra net.

    I have run into similar situations because users can only access one domain. This restricts access to your application.

    I have recently used a product called alpha5 V9 from a company called alphasoftware.

    Just a thought.

    Good luck,

    Mike

  8. #8
    Join Date
    Oct 2008
    Posts
    6

    ms access syncronization

    Hi Mabingle,

    My fear exactly. The reliable solution is still an internet application as you suggested but for now, there is no internet in these locations. Even to send a file to the Headquarter, a staff would have to take the file to any nearby cybercafe in town.

    Please, the alpha5 v9 that you have used, did it meet your needs and are you suggesting i use this software in the interim?

    Thank you for your comment, its greatly appreciated.

    Samson

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    are these sites in range of a cellphone antenna
    if so has the organisation thought of using a cellphone 'broadband' dongle?
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Oct 2008
    Posts
    6
    healdem,

    Not all the site have cellphone antenna, they are very few of them.

  11. #11
    Join Date
    Oct 2008
    Posts
    6
    Thank for your suggestion. But you did not suggest a to me what can help me out of this.

    Best regards,

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depends where you are, but Ive found very few sites where there isn't a reasonable cellphone signal in the UK. you may need to be selective in which cellphone provider you use, but so far I've found precious blank spots.

    outside work, we have had serious problems when tied to Vodafone... but thats when we are trying to connect to the network some 5..6 miles out to sea, when the only signal we could get was Orange.....
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by mabingle
    Hi,

    The other replies were interesting and have value. However, each is prone to error and you can't afford that in any production application.

    I suggest, for this application, that you create a web based app that can be updated over the inter/intra net.

    I have run into similar situations because users can only access one domain. This restricts access to your application.

    I have recently used a product called alpha5 V9 from a company called alphasoftware.

    Just a thought.

    Good luck,

    Mike
    That might depend on the complexity of the application. If the application and logic is fairly complex, it might take longer and be at a higher overall cost if done via web.

    We had the same situation where we discussing web versus using citrix or remote desktop. Given the cost, development time, and hiring a possible staff of web developers, we chose citrix (for the users) and remote desktop (for the developers) to open a session and connect to the mde frontend on the server. It worked very well.

    Now at the UW were I work, we also use citrix to connect and run the mde file on the server. It works pretty good although a dish-type connection over citrix can hiccup every now and then when it's cloudy outside.
    Last edited by pkstormy; 10-14-08 at 21:04.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by mabingle
    Hi,

    The other replies were interesting and have value. However, each is prone to error and you can't afford that in any production application.

    I suggest, for this application, that you create a web based app that can be updated over the inter/intra net.

    I have run into similar situations because users can only access one domain. This restricts access to your application.

    I have recently used a product called alpha5 V9 from a company called alphasoftware.

    Just a thought.

    Good luck,

    Mike
    That might depend on the complexity of the application. If the application and logic is fairly complex, it might take longer and be at a higher overall cost if done via web.

    We had the same situation where we discussing web versus using citrix or remote desktop. Given the cost, development time, and hiring a possible staff of web developers, we chose citrix (for the users) and remote desktop (for the developers) to open a session and connect to the mde frontend on the server. It worked very well.

    Now at the UW were I work, we also use citrix to connect and run the mde file.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •