none
Is it possible to sync two fields on different tables in MS Access? If so, how? RRS feed

Answers

  • Hello,

    If using VB.NET or C# this can be done but there must be a key field (see example in DataRelation class at bottom of page)ed into a DataSet then setup the relationship using a DataRelation. If there is a problem with this method then please supply more details i.e. what language is being used, structure of both tables etc.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Wednesday, April 10, 2013 11:05 AM
  • Do you have a unique identifiers and proper relationships with these two tables? And how the data get modified through a UI or from Database itself?

    Synchronization of your data can be either by Push or Pull.

    Push -> This will raise a notification and ensure that when your source column got changed, Target column need to update. This can be achieved using Triggers concepts of most RDBMS. But unfortunately Ms-Access does not support this. As a work around, the best you can probably do is put this sort of logic into the UI’s entry point. A sample code snippet,

    UPDATE [Table1] AS DataDestination, [Table2] AS Source,
    SET DataDestination.Col2 = Source.[Col2]
    WHERE (((DataDestination.Col1)=[Source].[Col1]))

    Pull ->

    Option-One

    Write a windows service or a kind of scheduler with C#/VB using ado.net which will run in a specific interval of time and will verify cell by cell changes of two columns and update it.

    Option-Two

    Database Replication using Microsoft Jet 4.0 could be option for you too if you are using Access 2000 or higher.


    Lingaraj Mishra

    Friday, April 12, 2013 6:17 AM

All replies

  • Hello,

    If using VB.NET or C# this can be done but there must be a key field (see example in DataRelation class at bottom of page)ed into a DataSet then setup the relationship using a DataRelation. If there is a problem with this method then please supply more details i.e. what language is being used, structure of both tables etc.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Wednesday, April 10, 2013 11:05 AM
  • Do you have a unique identifiers and proper relationships with these two tables? And how the data get modified through a UI or from Database itself?

    Synchronization of your data can be either by Push or Pull.

    Push -> This will raise a notification and ensure that when your source column got changed, Target column need to update. This can be achieved using Triggers concepts of most RDBMS. But unfortunately Ms-Access does not support this. As a work around, the best you can probably do is put this sort of logic into the UI’s entry point. A sample code snippet,

    UPDATE [Table1] AS DataDestination, [Table2] AS Source,
    SET DataDestination.Col2 = Source.[Col2]
    WHERE (((DataDestination.Col1)=[Source].[Col1]))

    Pull ->

    Option-One

    Write a windows service or a kind of scheduler with C#/VB using ado.net which will run in a specific interval of time and will verify cell by cell changes of two columns and update it.

    Option-Two

    Database Replication using Microsoft Jet 4.0 could be option for you too if you are using Access 2000 or higher.


    Lingaraj Mishra

    Friday, April 12, 2013 6:17 AM