none
getting Microsoft.ACE.OLEDB to work

    Question

  • I am trying to configure my Visual Studio (Community edition 2015) to allow me to access an Excel spreadsheet to populate a dataset.  I have been trying to use a data adaptor to do this.

    From study to date I have downloaded and installed the Microsoft Access Database Engine 2010 Redistributable package from Microsoft website, and when working through the connection wizard my 'Test Connection works for earlier excel (xls) but not xlsx, although I cant complete the wizard.

    Trying from the C# coding window I am unable to compile (Error Could not resolve COM reference "4affc9a0-5f99-101b-af4e-00aa003f0f07" version 9.0. Object reference not set to an instance of an object.

    I am on Windows10, using VS2015 and Office 2016 (although I ad the same problems on Office 2013).

    Are there instructions on how to correctly install the necessary library, register components etc.  It seems wat should be a routine and simple task is overly complicated.

    Thanks in advance


    • Edited by Andy Rew Friday, July 01, 2016 12:19 PM
    Friday, July 01, 2016 11:22 AM

Answers

  • Hi Andy Rew,

    >> how the ODBC drivers that replaced Jet 4.0 (i..e ACE Drivers) are properly configured,

    As far as I know, the ODBC driver ofthen used to connect some small data files, such as Excel, Document and so on. And the ACE Driver is used to conenct Access database, which is a small database. So I think the ODBC driver is not replaced the ACE driver.

    >> how these should be added as references to the project

    If you want to connect to the data file or database in a project, we ofthen use the connection class which belongs to System.data assembly. We need not add extra driver to the project. For example, if you want to use ODBC to conenct to the Excel data file, please use OdbcConnection class and using Connection class to create connection with the Excel data. Some code snippet is similar with below.

    using System.Data.Odbc;
    
    OdbcConnection Connnection = new OdbcConnection();
      Connnection.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DBQ=" + itemFile;
      OdbcCommand cmd = new OdbcCommand();
      cmd.Connection = Connnection;
      cmd.CommandText = "SELECT * FROM [Sheet1$]";
      OdbcDataAdapter oda = new OdbcDataAdapter(cmd);
      DataTable dt = new DataTable();
      oda.Fill(dt);
      Connnection.Close();

    Best Regards,
    Weiwei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 08, 2016 5:37 AM
    Moderator

All replies

  • Hi Andy Rew,

    Could you please tell us the detailed steps about how do you connect to the xlsx file? Do you connect to the file through Tools menu -> Connect to Database in Visual Studio or connect to the file through C# code? And please also tell us the project type you are using.

    If you are connect to the xlsx file through Visual Studio Tools menu, please choose the "Microsoft ODBC Data Source (ODBC)" as Data source. We can click the Change button to change the Data Source type. 

    If you are using C# code, please refer to following link which introduce how to query and display excel data by using ASP.NET, ADO.NET, and Visual C# .NET. Hope that can help you.

    https://support.microsoft.com/en-sg/kb/306572

    Best Regards,
    Weiwei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, July 04, 2016 7:56 AM
    Moderator
  • I tried using the Connection Wizard, but I can't seem to connect to anything beyond a 2007 Excel (*.xls).

    I think my problem is in how the ODBC drivers that replaced Jet 4.0 (i..e ACE Drivers) are properly configured, and how these should be added as references to the project.

    I usually rely on keeping the computer updates as the easiest way to achieve this, but it doesn't seem to be working this time around.

    Thanks

    Thursday, July 07, 2016 11:29 AM
  • Hi Andy Rew,

    >> how the ODBC drivers that replaced Jet 4.0 (i..e ACE Drivers) are properly configured,

    As far as I know, the ODBC driver ofthen used to connect some small data files, such as Excel, Document and so on. And the ACE Driver is used to conenct Access database, which is a small database. So I think the ODBC driver is not replaced the ACE driver.

    >> how these should be added as references to the project

    If you want to connect to the data file or database in a project, we ofthen use the connection class which belongs to System.data assembly. We need not add extra driver to the project. For example, if you want to use ODBC to conenct to the Excel data file, please use OdbcConnection class and using Connection class to create connection with the Excel data. Some code snippet is similar with below.

    using System.Data.Odbc;
    
    OdbcConnection Connnection = new OdbcConnection();
      Connnection.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DBQ=" + itemFile;
      OdbcCommand cmd = new OdbcCommand();
      cmd.Connection = Connnection;
      cmd.CommandText = "SELECT * FROM [Sheet1$]";
      OdbcDataAdapter oda = new OdbcDataAdapter(cmd);
      DataTable dt = new DataTable();
      oda.Fill(dt);
      Connnection.Close();

    Best Regards,
    Weiwei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 08, 2016 5:37 AM
    Moderator