Occasionally I have customers that need to query MS Excel or MS Access files for their Reports. Crystal Reports has always provided a DAO connector that allows you to read the classic XLS and MDB files directly. But starting in 2007 Microsoft introduced the XLSX and ACCDB formats and these can’t be read using the DAO connector. If you need to read one of these more recent formats then you will need to use either ODBC or OLEDB to connect. [Note – I try to avoid storing data in Excel. This article explains why.]

I have written several articles about setting up ODBC connections and the challenges of creating a 32-bit ODBC connections on a 64-bit PC. But I have been reminded by several folks that it is simpler to skip over ODBC and create the connection using OLEDB. OLEDB uses the same driver but doesn’t require that you go through the ODBC Administrator to create a Data Source.  [Note – one quick workaround for XLSX files is to do a “Save As” and select “Excel 97-2003 Workbook” format which allows you to continue using DAO. Most spreadsheets used to store data will work just as well in XLS format, unless you have more than 65,536 rows or more than 256 columns.]

To connect to an OLEDB connection open the database expert (or the “Data” step while creating a new report) and open the folder/node called “Create a new connection”. Below that you should see a node labeled OLEDB. Open that and you should be asked to select an OLEDB provider. If there is already another OLEDB connection open you can double click “Make a new Connection” right below the OLEDB node label to open the list of providers.

Look through the list of OLEDB providers until you see “Microsoft Office 12.0 Access Database Engine”. [Note – if you don’t see that provider listed then you need to download and install the driver. See this article for the download link.]

Highlight that provider and click “Next”. On the second screen you select either Access or Excel as the “Office Database Type”. Then click the button to the right of the “Data Source” box to browse to the folder that contains your file. Highlight your file and click “Finish” to make the connection. The connection should now display either the list of tables (MS Access) or the list of spreadsheet tabs (MS Excel) available to use in your report.