Join two worksheets using Microsoft.ACE.OLEDB.12.0

Today, I am going to demonstrate how to join two Excel worksheets using the Access database engine – Microsoft.ACE.OLEDB.12.0. The environment I am using is Excel 2007 on Windows Vista.

1. Open a new Excel workbook. In Sheet1, enter the data as shown below: image

2. In Sheet2, enter the data as shown below:
image

3. Add the VBA code in Sheet3 to join the data from Sheet1 and Sheet2.
image
Tips: Press Alt + F11 to get to the Visual Basic editor.

4. Run the macro in Sheet3.
image

5. You should see something like the screen shot below.
image

A few things to note if you are not able to get the above example to work:

  • Make sure the connection string is prefixed with “OLEDB;”.
  • Make sure INNER JOIN is specified instead of just JOIN.
  • Make sure spaces are inserted in the appropriate places.

Whilst working with Microsoft.ACE.OLEDB.12.0, I have encountered an issue where the data provider reads saved data from file instead of live data from memory. In my next blog post, I will present a solution to the issue.

–oo00oo–

For more info, please refer to the following links:
Data Programming with Microsoft Access 2010
Microsoft.ACE.OLEDB.12.0 doesn’t want to read my “live” .xlsb datas, only the last saved one, to the countrary of Microsoft.Jet.OLEDB.4.0 …. is this a bug ?