locked
Periodically consolidating several MS Access databases into a MS SQL Express database RRS feed

  • Question

  • Hi,

     

    I am new to MS Sync Framework. Just wanted to check if my scenario fits into it and how I can go ahead with its implementation.

     

    I  have 50 nodes. Each nodes has same set of MS Access databases (databases are small in size). Each database (mdb file) in each node has  5 tables and there is some relation among them. I want to consolidate this all these nodes databases into a single MS SQL database on server. Further I want to provide a generic functionality to query this MS SQL database to outside world.

     

    Is there any way/technique in which I can consolidate MS Access database to SQL such that, if in future there comes a requirement to change some parts of Access database (adding/deleting column, change in table relationship, etc. ) then I will not have to change my code and just by mere changing configuration files I will be able to get the required consolidated MS SQL database? Further, this consolidation is going to happen periodically say after every 1 min.

     

    I have mentioned the names of the database, just to more specific in my query.

    Sunday, June 13, 2010 12:28 AM

Answers

  • you can check out sample providers here: http://code.msdn.microsoft.com/sync

    you can use SSMA to periodically consolidate your data but not necessarily migrate your Access database application to use SQLServer.

    But reading your post again, it may not be the absolute solution specifically if you want the sync interval of 1 min.

    Please note also that Sync Fx is not a "continous replication" type of solution similar to SQL Replication. In Sync Fx, you have to explicitly invoke the synchronization. So in your case, you may have to build a timer in your application to invoke the sync given a particular interval (in your case 1min).

    Sunday, June 13, 2010 9:13 AM

All replies

  • to answer your question briefly, you may use Sync Framework if you are fine with building your own Access provider. Currently, Sync Fx out-of-the-box does not come with a provider for Access databases. secondly, Sync Fx doesnt support managing schema changes as well (adding/removing columns, table relationships, etc...)so you'll have to manage with your own code as well.

    have you looked at SSMA? chk http://www.microsoft.com/sqlserver/2005/en/us/migration-access-learning.aspx

    Sunday, June 13, 2010 5:24 AM
  • Thanks for the reply.... I will check how feasible is it to make custom Access provider. Please point me to some links for that if you have them.

    Also, how will SSMA be useful to me? I dont want to migrate to MS SQL. All my legacy databases are in MS Access and I want to consolidate them on server. So thought of using SQL on server instead of MS access.

    -Piyush

    Sunday, June 13, 2010 8:19 AM
  • you can check out sample providers here: http://code.msdn.microsoft.com/sync

    you can use SSMA to periodically consolidate your data but not necessarily migrate your Access database application to use SQLServer.

    But reading your post again, it may not be the absolute solution specifically if you want the sync interval of 1 min.

    Please note also that Sync Fx is not a "continous replication" type of solution similar to SQL Replication. In Sync Fx, you have to explicitly invoke the synchronization. So in your case, you may have to build a timer in your application to invoke the sync given a particular interval (in your case 1min).

    Sunday, June 13, 2010 9:13 AM
  • Thanks for the reply. I will go through the details.

    It looks that even you are not that comfortable with using MS Sync for my requirement. Will it be possible for you to suggest me something else to meet my requirements?  I am still investigating, so can afford to incorporate new technologies.

    -Piyush

    Monday, June 14, 2010 7:15 AM
  • the biggest piece of work in your scenario is writing the MS Access sync provider. Not a walk in the park given that the two custom database providers you could probably reference (SQL Express and Oracle providers) are utilizing triggers and stored procedures to maintain metadata which is not as easy to port as is to MS Access.

    also, as mentioned, Sync Fx does not provide robust support for schema changes (add/delete columns, indexes, etc...).

    is your requirement just for upload/consolidation at the server side? will you be doing incremental uploads or always full upload?

    Monday, June 14, 2010 7:43 AM