What is the default value of IMEX in OLEDB?

Question

string strConn = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=\"Excel 12.0 Xml;HDR=Yes;IMEX=1\";";

I search on google, and got no results. I think the default value is 2, but I am not sure.

Anyone can help me? Thanks.

Answer 1

While I didn't find information on a default, the comment from @Bao helps explain the IMEX setting. According to Microsoft Knowlege Base Article 194124 the IMEX parameter stands for IMport EXport mode. Here's a quote from the article:

The possible settings of IMEX are:

 0 is Export mode
 1 is Import mode
 2 is Linked mode (full update capabilities)

In plain English, you might use IMEX=0 for Writes, IMEX=1 for Read-Only, and IMEX=2 for Modifications / Updates (Read, Change, Save Changes).

I notice that this question is tagged for Excel, but the Microsoft.ACE.OLEDB Provider allows connections to many types of data sources supported by the Access Database Engine including Access (*.accdb), Excel (*.xlsx), SharePoint Lists, etc. The IMEX parameter will impact these other types of data sources in similar ways.

---

Update August 2017. Additional comments on IMEX Extended Properties as mentioned in another document ADO.NET Connection String Syntax (docs.microsoft.com) under heading "Connecting to Excel":

In the [example] connection string, the Extended Properties keyword sets properties that are specific to Excel. "HDR=Yes;" indicates that the first row contains column names, not data, and "IMEX=1;" tells the driver to always read "intermixed" data columns as text.

I believe that the previous KB article still applies and the IMEX setting is more generally applied as export/import/linked to other non-excel data sources in data source connection strings. Also note that the KB article indicates that the "ImportMixedTypes" setting may be changed via registry - this leads me to believe that this second article should be mostly ignored in favor of the KB to explain the IMEX setting. The related registry settings are documented for Access Connectivity Engine (ACE) Excel Driver @ Windows Registry Settings for External Data Sources - Initializing the Microsoft Excel Driver.

Answer 2

Per the article below, IMEX values of 0 and 2 do the same thing; they use ImportMixedTypes=MajorityType. That is the default if you don't specify IMEX=1 in your extended properties.

So the default IMEX behavior is MajorityType, and this can be overridden by using IMEX=1 in your connection string along with the registry setting ImportMixedTypes=Text.

http://www.instantpages.ltd.uk/ADODB_WP.htm