Share via

ConnectionString for connecting to Office 365 ODBC driver (ODBCJT32.DLL)

Anonymous
2020-06-16T14:49:35+00:00

What is the proper connection string to connect to Excel files? 

Before you answer, don't recommend installing an earlier version as that is not feasible in my current enterprise environment.

The installed driver is the only one registered on desktops and servers.

There are similar queries such as this: https://answers.microsoft.com/en-us/msoffice/forum/all/connection-string-for-office-365/f1e91c24-27ad-4d50-b91c-260d584ee063 that always suggest to install another driver. This is not a proper solution, we already have the necessary drivers installed, we only need it to work without having to change the connectionstring every 5 years.

[Move from Excel/ Other/Other/unknow /Microsoft 365 Apps or Office 365 for business]

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-06-16T20:20:33+00:00

    No, you don't understand. I'm not trying to import data into Excel from Access or similar scenarios. I'm looking for the connectionstring value to make the connection. Over the years, the connectionstring has changed from version to version and also the data provider changed from JET to ACE similar to the following connectionstring samples:

    from:

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

    to:

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myOldExcelFile.xls;Extended Properties="Excel 8.0;HDR=YES";

    to:

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

    Now with the advent of O365, things have changed once again. Whoever came up with this idea must have worked at Oracle, I suppose.

    You can usually find example for particular versions of Excel, however, someone has failed to share that information with the developer community so here I am, hoping to get lucky.

    7 people found this answer helpful.
    0 comments
  2. Anonymous
    2020-06-16T23:51:55+00:00

    Except I don't want to install different drivers. I don't have control over that. (Read original post) I'm in an enterprise operation where I don't have control over such things. I need to try to use the existing drivers that are already installed and registered. If I had access and control to the server, and my own machine, I wouldn't be looking for an answer here.

    6 people found this answer helpful.
    0 comments
  3. Anonymous
    2020-06-16T18:43:28+00:00

    Hi mgmoody,

    As per your description, please refer to Overview of connecting to (importing) data, not all the external data source requires ODBC driver. We only need to use the specific ODBC driver when the specific external data source requires that, such as the Microsoft SQL ODBC driver to connect to Microsoft SQL Server database. Additionally, some ODBC driver can be downloaded free, but the data source need payment to use. Therefore, to use ODBC driver, it depends on which external data source want to use.

    Additionally, here is article you may refer this Access ODBC driver or OLEDB provider outside Office Click-to-Run applications

    I appreciate your time and understanding.

    Best Regards

    Waqas Muhammad

    1 person found this answer helpful.
    0 comments
  4. Anonymous
    2020-06-17T13:26:17+00:00

    Hi mgmoody,

    Thanks for writing back with us. Since, the ODBC driver connection value is usually used in the VBA code so the VBA could connect to the DB without asking for permission. As we are under Office 365 business category and very less knowledge for string or VBA code and I’m help you to move your thread into Microsoft programming category for further dedicated help.

    Highly appreciate your time and understanding.

    Best Regards

    Waqas Muhammad

    0 comments
  5. Anonymous
    2020-06-16T22:27:59+00:00

    Hi mgmoody,

    Thanks for sharing updates with us for clarification. After search the connection String with ACE.OLEDB.12.0 you can install the Microsoft.ACE.OLEDB.12.0 Provider using the following setup. Microsoft Access Database Engine 2010 Redistributable https://www.microsoft.com/en-us/download/details.aspx?id=13255 once installed, you can modify the connection string.

    Also, here I find one article you may refer this under connection to Excel  Connection String

    I appreciate your time and understanding.

    Best Regards

    Waqas Muhammad

    0 comments