locked
Synchronizing remote MS Access databases using VS2010 RRS feed

  • Question

  • I have an existing VB6 application which permits two or more users to work with local relicated MS Access databases. At start-up and shut down the local database synchronize with the master database (if the network permits dialogue) using

    mDb1.Synchronize etc.

    I am trying to port the application over to VS2010 and I cannot find anything equivalent to permit occasional synchronizing of various replicated MS Access databases with the master database (again, if the network permits contact between the two machines). Has anyone found a way of doing this for Access databases (not SQL Server databases)?

    Works fine in VB6 but VS2010 has me beaten at the moment.

    Saturday, November 6, 2010 10:47 PM

Answers

  • Yes, you can use DAO in .NET via COM interop. You can add a reference to DAO (Project...Add Reference...select the COM tab and then select Microsoft DAO 3.6 Object Library from the list).

    The object references will change a bit as a result of COM interop but otherwise much of the code will be the same. The Object Browser and Intellisense should help you out with resolving any references.

    There is no native functionality in .NET for performing Microsoft Access Synchronization/Replication so you will need to stick with COM DAO or ADO/JRO.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Proposed as answer by Cor Ligthert Saturday, November 20, 2010 8:47 AM
    • Marked as answer by Liliane Teng Monday, November 22, 2010 2:10 AM
    Saturday, November 20, 2010 5:06 AM
  • In addition to Paul, 

    Don't forget to put in top of your program after setting the reference

    Imports Dao

    , many things comes than available with intellisense (if you type something the class, object, event...... is showed)


    Success
    Cor
    • Marked as answer by Liliane Teng Monday, November 22, 2010 2:11 AM
    Saturday, November 20, 2010 8:50 AM

All replies

  • If you can do it with VB6 you can do it almost forever with VB10.

    I've no code for you to synchronize but in this tip you can see how to use Adodb and AdoNet combined.

    http://www.vb-tips.com/CreateMDB.aspx


    Success
    Cor
    Sunday, November 7, 2010 8:02 AM
  • Hi Cor,

    Thanks for your advice. I agree that it is hard to believe that what you could do in VB6 turns out to be so difficult in VS2010. I have looked at the tip site that you gave above but I do not see the relevance to what I'm trying to achieve - namely being able to synchronize a modified replica database with its design master database (with both MS Access databases) and using VS2010.

    If you or anyone else is able to further enlighten me, I would be very grateful.

    Regards

    Sunday, November 7, 2010 10:37 AM
  • John,

    Probably if you show the VB6 code it is much easier for somebody to get an answer for you.

    And then not more then 10 rows otherwise there are very few who will look at it.

     


    Success
    Cor
    Sunday, November 7, 2010 11:58 AM
  • You said that your VB6 application worked with local Access database files.  Then you said that you are trying to connect to them remotely using VS2010.  That is a very different problem.  You have barely described the problem.

     Add/Modify Connection (Microsoft Access).

    What provider are you using?  What is your connection string that you are using?  Which version of Access?  Are you trying to make the connection programatically or by using the Connection Wizard?  My favorite.  What does "not work" mean in your scenario?  Exceptions?  Failures?  What?

    If you wish a specific answer, then providing information about your specific scenario helps.  General questions get general answers.  If you disagree, take a look at the statistics on this thread.  A whole lot of view, but very few replies.

    http://www.connectionstrings.com/access

    Hope this helps.

    Rudy  =8^D


    Mark the best replies as answers. "Fooling computers since 1971."

    http://rudedog2.spaces.live.com/default.aspx

    Sunday, November 7, 2010 3:07 PM
  • I don't believe that replication functionality would be any different under VB.NET than it was in VB 6.0, since you are using either DAO or JRO.

    A small snippet of your VB 6.0 code might help so we can convert it to VB.NET.

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, November 9, 2010 1:49 PM
  • Hello John,

    Thanks for your post.

    What's the situation on your side? Could you please provide more information? If so, you may get more better and quicker support. Thanks.

    Best regards,
    Liliane
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please mark the replies as answers if they help and unmark them if they provide no help. Welcome to the All-In-One Code Framework! If you have any feedback, please tell us. Thanks
    Friday, November 12, 2010 8:26 AM
  • Several of the responders have asked for more information (sorry I am new to this forum game and request your tolerance)

    The successful snippet of VB 6 code which I am trying to duplicate (in VB 10) is as follows:

       ' set up K drive as \\JOHN\C\Accounts


       lngHandle = dhAddConnection1("\\JOHN\C\Accounts", "", "K:")
        
       'Check to see if user can see K (which is actually \\JOHN\C\Accounts)
       ' if no then just use local replica as mDb1
       ' if yes then set mDb1 to local replica and synchronize it with the master
      
       If Len(Dir("K:\New Accounts_2002 Design Master.mdb")) = 0 Then
         

          ' Cannot see K drive therefore just use local replica

          Set mDb1 = DBEngine.Workspaces(0).OpenDatabase("C:\Accounts\Replica of New Accounts_2002 Design Master.mdb")

       Else

         ' Can see K drive and therefore use local replica and synchronize it with Master.

          Set mDb1 = DBEngine.Workspaces(0).OpenDatabase("C:\Accounts\Replica of New Accounts_2002 Design Master.mdb")

        ' Sends changes made in each replica to the other.
        mDb1.Synchronize "\\JOHN\C\Accounts\New Accounts_2002 Design Master.mdb", _
            dbRepImpExpChanges

       
       End If

    Note that in VB 6 I believe I was using DAO technology whereas now, in VB 10, I am trying to use .Net technology. Is DAO technology still available in VB 10? If so then how do I access it? (Having said that I don't want to go back to old technology which might die in the near future anyway. Hence why I am trying to use .Net.)

    I am using a home network and sometimes the machine where the master .mdb file is located is switched off and hence the other networked machines cannot see it. When thay can see it then I want the local replica's to synchronize with the master .mdb. Hope this information helps.

    I'll look forward to any help you can offer.

    Regards

    John

     

    Saturday, November 20, 2010 4:46 AM
  • Yes, you can use DAO in .NET via COM interop. You can add a reference to DAO (Project...Add Reference...select the COM tab and then select Microsoft DAO 3.6 Object Library from the list).

    The object references will change a bit as a result of COM interop but otherwise much of the code will be the same. The Object Browser and Intellisense should help you out with resolving any references.

    There is no native functionality in .NET for performing Microsoft Access Synchronization/Replication so you will need to stick with COM DAO or ADO/JRO.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Proposed as answer by Cor Ligthert Saturday, November 20, 2010 8:47 AM
    • Marked as answer by Liliane Teng Monday, November 22, 2010 2:10 AM
    Saturday, November 20, 2010 5:06 AM
  • In addition to Paul, 

    Don't forget to put in top of your program after setting the reference

    Imports Dao

    , many things comes than available with intellisense (if you type something the class, object, event...... is showed)


    Success
    Cor
    • Marked as answer by Liliane Teng Monday, November 22, 2010 2:11 AM
    Saturday, November 20, 2010 8:50 AM