2970410 - MS Excel and MS Access connection missing in Crystal Reports

1. In Crystal Reports, create a new report.
2. In the "Database Expert", under "Create New Connection", it does not list the connection "Access/Excel (DAO)"
   
   Where is the Access and Excel connection?
   

Cause

• To create a report based on MS Excel and MS Access in previous version of Crystal Reports, it used to have the connection type: "Access/Excel (DAO)"
• This connection was using the 32bit Microsoft Data Access Objects (DAO), and Microsoft JET Engine, which have been deprecated by Microsoft.
• Crystal Reports 2016 and below are 32bit application, and can still use the Microsoft DAO technology to connect to Excel and Access, as a legacy connection type.
• Crystal Reports 2020 is a 64bit application, and therefore it is no longer possible to connect to Excel or Access using DAO, since there is no 64bit version of this Microsoft Technology. This is why the option "Access/Excel (DAO)" is not available in Crystal Reports 2020.

Resolution

• To report of a MS Excel or MS Access in Crystal Reports, use one of the following connection type:
  • ODBC
  • OLEDB
    
  1. Launch the Microsoft ODBC Data Source Administrator (64bit), from: C:\Windows\System32\odbcad32.exe
     
  2. In the Microsoft ODBC Data Source Administrator (64bit), under the tab "System DSN", click "Add. "
     
  3. Select the ODBC Driver corresponding to the type of data source you want to report from:
     
     - For MS Excel, select : Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)
     - For MS Access, select : Microsoft Access Driver (*.mdb, *.accdb)
     
     Note
     If you do not see the ODBC Drivers, then it is either because you have a 32bit version of MS Office installed, or do not have MS Office installed. To download and install the 64bit version of the Microsoft Office ODBC drivers, go to the Microsoft website, and search for: "Microsoft Access Database Engine"
     
  4. Then click "Finish"
     
  5. Add an ODBC DSN Name and select the MS Excel or MS Access document you want to report from.
     
  6. Click "OK" to create the ODBC DSN
     
  7. In Crystal Reports, create a new report.
     
  8. In the "Database Expert", expand "Create New Connection", and double click on "ODBC (RDO)"
     
  9. Select the ODBC DSN created in the previous steps to report from Excel, or Access.
     
     NOTE: For MS Excel, if the Excel sheets are not visible, then:
     1. Right click on the "Connection", and in the contextual menu, select "Options"
     2. In the "Options" window, check the option "System Tables", and click OK
     3. Back to the "Database Expert" window, right click on the connection, and select "Refresh"
     4. The MS Excel sheets will now display.
     • To use an OLEDB connection
     1. In Crystal Reports, create a new report.
     2. In the "Database Expert", expand "Create New Connection", and double click on "OLE DB (ADO)"
     3. Select the Microsoft Office Access Database Engine Provider.
        
        Note
        If you do not see the OLEDB Provider, then it is either because you have a 32bit version of MS Office installed, or do not have MS Office installed.
        To download and install the 64bit version of the Microsoft Office ODBC driver and OLEDB Provider, go to the Microsoft website, and search for:
        "Microsoft Access Database Engine"
     4. Change the Office Database type to: Excel, or Access
     5. Select the MS Excel, or MS Access file.
     6. Leave the User ID and Password blank.

     Keywords

     CR , KBA , BI-RA-CR , Problem

     Product

     SAP Crystal Reports 2020