2970419 - Error: 'Access/Excel(DAO) connection is no longer supported...' when refreshing a report in Crystal Reports

• Error: 'Access/Excel(DAO) connection is no longer supported...'
• Report based on Excel or Access fails to refresh in Crystal Reports.
• Could successfully refresh the report in previous version of Crystal Reports. 
• When refreshing a report connecting to MS Excel, or MS Access in Crystal Reports, it fails with the error:
        
     "Failed to load database information.
     "Details: Access/Excel(DAO) connection no longer supported,
       use another alternative if possible, e.g. ODBC/JDBC."
  
  Followed by:
  
     "Failed to load database information."
        
    
  
  
  

• SAP Crystal Reports 2020
      
• MS Excel 2013
• MS Excel 2016
• MS Excel 2019
      
• MS Access 2013
• MS Access 2016
• MS Access 2019

1. In Crystal Reports 2016 or below, create a new report.
2. In the Database Expert, under "Create New Connection" double click on "Access/Excel (DAO)"
3. Select the MS Excel, or MS Access file to report from.
4. Refresh the report, and it successfully display data. 
5. Save the report.
6. In Crystal Reports 2020, open the same report, and refresh it.
         
   It generates the error: "Failed to load database information. Details: Access/Excel(DAO) connection no longer supported,..."

• The report connect to Excel or Access using Microsoft Data Access Objects (DAO), which used Microsoft JET Engine.
• This 32bit only connectivity technology have been deprecated by Microsoft.
• Previous versions of Crystal Reports are 32bit application, and can still use 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 the Microsoft Technology.

• To report of a MS Excel spreadsheet, or MS Access in Crystal Reports, use one of the following connection type:
• ODBC
• OLEDB
         
    
• To use an ODBC Connection:
           
1. Launch Microsoft ODBC Data Source Administrator 64bit, from: C:\Windows\System32\odbcad32.exe
                    
2. In the Microsoft ODBC Data Source Administrator, under the tab "System DSN", click "Add..."
              
3. Select the ODBC Driver corresponding to the type of document 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, and click "OK"
                   
6. In Crystal Reports, open your report based on MS Excel or MS Access.
      
7. Under the menu "Database", select "Set Datasource Location"
         
8. In the "Replace with" section, expand "Create New Connection", and double click on "ODBC (RDO)"
      
9. Select the ODBC DSN created in the previous steps to report from MS Excel or MS 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. Select the desired MS Excel sheet.
     
10. Once connected, in the section "Replace with", select the new ODBC connection, and in the section "Current Data Source", select the current connection, and click the button "Update"
         
11. Once the connection is updated, click "Close"
        
12. Back to the report, save the report.

• To use an OLEDB connection

1. In Crystal Reports, open your report based on MS Excel or MS Access.

2. Under the menu "Database", select "Set Datasource Location"

3. In the "Replace with" section, expand "Create New Connection", and double click on "OLE DB (ADO)"

4. 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"

5. Change the Office Database type to: Excel, or Access

6. Select the MS Excel, or MS Access file.

7. Leave the User ID and Password blank.

8. Once connected, in the section "Replace with", select the new OLEDB connection, and in the section "Current Data Source", select the current connection, and click the button "Update"

9. Once the connection is updated, click "Close"

10. Back to the report, save the report.

CR, deprecated JET, CR2020 , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem