What is Ace OLEDB data provider
This OLE DB Provider is provided by Microsoft and is contained in the file ACEOLEDB.DLL. The Microsoft ACE OLEDB 12.0 provider can be used to connect to many data sources like MS Access, Foxpro, Excel, Sharepoint Etc.
This driver was first released with MS Office 2007 but that doesn’t mean that you cannot use this to connect with Pre-2007 office versions. It is very much possible to use the Microsoft.ACE.OLEDB.12.0 to connect to old .xls (Excel 97-2003) workbooks as well. And the best part is that driver is available in both 32 bit and 64 bit versions.
I will be covering this OLE DB Provider comprehensively in the next many articles as I did for Interop. My idea is to make this a one stop place for any kind of Excel Automation from VB.Net
Before we begin, let’s understand few more things about this Provider.
To connect to Excel, you will require a connection string. A connection string depends on which excel extention would you be connecting to. For example if you would like to connect to an .XLSX file then the string will look like
Extended Properties="Excel 12.0 Xml;HDR=YES";
Whenever you are in doubt, on which connection string to use, I would recommend visiting my favourite site for connection string which is http://www.connectionstrings.com/
Now let’s understand the different parameters of the connection string
- Provider : It is the main OLE DB provider that is used to open the excel sheet. Since we are using Ace OLEDB data provider, we will mention that.
- Data Source : It is the full path of the excel workbook you want to work with.
- Extended Properties (Optional) : Extended properties are optional and are required only if you may want to change the overall activity of the excel workbook. Let’s see some of the most common ones:
- HDR : It represents Header of the fields in the excel table. If you have fieldnames in the header of your worksheet, then you can specify HDR=YES. BTW, the default value is YES so if you do not specify HDR, it will be assumed that you have fieldnames in the header of your worksheet. And If you don’t have fieldnames in the header of your worksheet, you can specify HDR=NO which will take the columns worksheet as F1,F2,F3 and so on.
- ReadOnly : As the parameter name suggests, you can also open excel workbook in read-only mode by specifying ReadOnly=true. By Default this attribute is set to FALSE.
- MaxScanRows : Default value: 8. Excel needs to scan the rows before deciding the data types of the fields. MaxScanRows specifies the number of cells to be scanned before deciding the data type of the column. You can specify any value from 1 – 16 for 1 to 16 rows. You can also make the value to 0 so that it searches all existing rows before deciding the data type. NOTE For Experts: You can change the default behaviour of this property by changing the value of [HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows] which is 8 by default. Currently MaxScanRows is ignored, so you need only to depend on TypeGuessRows Registry value.
- IMEX : IMEX refers to Import Export mode. There are three possible values. IMEX=0, IMEX=1 and IMEX=2. This is used when you have mixed data in a column. To always use IMEX=1 is a safer way to retrieve data for mixed data columns. Consider the scenario that one Excel file might work fine cause that file’s data causes the driver to guess one data type while another file, containing other data, causes the driver to guess another data type. This can cause your app to crash.
Once your connection string is ready, the next thing that you need is a COMMAND that you need to execute a particular instruction. The instruction can be, reading from a sheet or cells or creating a new sheet etc. As I keep on adding more articles, I would be explaining this a bit more. Keep a lookout for the articles as they come in. If you are interested in automating Excel from VB.Net using Ace OLEDB data provider then I would recommend subscribing to the blog so that you get an alert as soon as the article hits the blog