VB Express programming migration from Access database to SQL Express database RRS feed

  • Question

  • Converting data from Access to SQL Express is not a problem with the converter provided by microsoft.

    My problem is to modify a VB Express program created for an Access Database with visual studio, to be able to use an SQL Express database.

    • When you create a VB program to use Access, Visual Studio generates an OLE DB code (no choice)
    • When you create a VB Program to use SQL Server, Visual Studio generates a code that is specific to SQL Server.(no choice)

    Now, I would like to modify the program I have created for Access to use it with SQL Server.
    • Changing the database type from Access to Sql server in the connexion string doesn't change the generated code from OLE DB to SQL!
    • What is needed is to use the OLE DB interface to SQL Server (SQLOLDB provider).
    I have tried to manually change the connexion string :
    connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\aeem.mdf";Integrated Security=True;Provider="SQLOLEDB";Connect Timeout=30;Application Name=Microsoft.Jet.OLEDB.4.0;User Instance=True"
    but the provider "SQLOLEDB" is not recognized.

    What can I do now to fix this problem?
    I think that an easy transition from Access to SQL Server should be high in th list of requirements fo a future VB/visual studio release.

    F. Palangié
    Saturday, October 24, 2009 2:42 PM


  • I finnaly managed to find the solution by myself . The problem was with the connexion string I had manually modified.
    The correct string is :
     connectionString="Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;INITIAL CATALOG=aeem;Trusted_connection=yes; "
    In a future release visual studio should support the SQLOLEDB provider and be able to generate automatically  this connection string
    F. Palangié
    • Marked as answer by Zhipeng Lee Thursday, October 29, 2009 2:32 AM
    Sunday, October 25, 2009 6:45 AM