How to migrate dataset designer code from access to sqlserver RRS feed

  • Question

  • Hi all,

    I have a system with access database and I want to upsize to sql server database what I need that migrate the dataset designer file to compatible with sql server code and no need to to retyping queries, try to understand me.


    Friday, December 1, 2017 6:41 AM

All replies

  • One of the fallacies of using classes generated by Visual Studio is that they are tied to the initial data source e.g. in your case ms-access. In the designer System.Data.OleDb is used and can't be changed while for SQL-Server System.Data.SqlClient is used.

    Also, for OleDb/ms-Access to SqlClient/SQL-Server there is more functionality e.g. add a new row, hit save with ms-access and the new primary key does not come back while with SqlClient/SQL-Server you get the new primary key on save.

    The only thing common between the two high level is the TableAdapter/DataSet/BindingSource components.

    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. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, December 1, 2017 1:50 PM
  • Unfortunately there is no tool that will enable you convert the designer generated objects from one database to another. You will need to recreate them manually.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, December 1, 2017 2:10 PM
  • And this is the drawback in using wizards with using the dataset and datatable. You are locked into whatever the database technology is, and you cannot scale out  to another technology.

    You should be using an abstraction away from the underlying database technology so that any code that needs to do CRUD never sees the underlying DB technology code and could care less about use the DB technology being used.

    The abstraction is achieved by using the DTO pattern.

    Friday, December 1, 2017 8:58 PM