Access or Excel DAO in Crystal Reports 2013- Tutorial

Access or Excel DAO in Crystal Reports 2013: Video Lesson

            This video lesson, titled “Access or Excel DAO,” shows you how to create a data connection in a report using Access or Excel DAO in Crystal Reports 2013. This video lesson on using Access or Excel DAO in Crystal Reports 2013 is from our complete Crystal Reports training, titled “Mastering Crystal Reports Made Easy v.2013.”

Access or Excel DAO in Crystal Reports 2013: Overview

            You can use Access or Excel DAO in Crystal Reports 2013 to connect an Access database or Excel workbook to a report. If you click the small plus sign next to the “Access/Excel (DAO)” folder in the “Create New Connection” section of the “Database Expert,” you will be presented with the “Access/Excel (DAO)” dialog box. You use this dialog box to create a data connection to a Microsoft Access file, a Microsoft Excel file, or one of many other file types using DAO (Data Access Objects) as the connection type versus an ODBC (Open Database Connectivity) connection.

            DAO is an object that works with Microsoft’s Jet database engine used in Microsoft Office. ODBC is simply a standard that allows any application to communicate and manipulate a variety of different database applications by using a standardized set of SQL (Structured Query Language) statements. SQL is the language used by all relational database applications to extract information from database tables.

            Because you can use DAO with many types of database/spreadsheet objects, you can also use this connection type to create a connection that use DAO for data access with Access, dBase III – dBase 5, Excel 3 – 8, an HTML import file, Lotus WK1, Lotus WK3 – 4, Paradox 3.x – 5.x, or a simple text database file.

            To use Access or Excel DAO in Crystal Reports 2013 to make a connection, click the ellipsis mark button at the right end of the “Database Name:” text box to launch the “Open” dialog box. In the “Open” dialog box, use the “Look in:” drop-down at the top of the dialog box to navigate to the folder that contains the database file (or spreadsheet file) to use as the data source. Click the file in the list of available files, and then click “Open” to set the selected file as the data source for the report.

Access or Excel DAO in Crystal Reports 2013- Tutorial: A picture of the “Access/Excel (DAO)” dialog box in Crystal Reports 2013.

            Next, use the “Database Type:” drop-down to choose the file type of the selected data object. To create a secured connection to the data file, check the “Secure Logon:” checkbox, which enables the next four fields that you can use to enter this information. Microsoft Access, in particular, supports both file-level and user-level security on its databases, individually, and in combination. If you are trying to use a Microsoft Access database that has both types of security applied, use the DAO (or OLE DB) dialog box to set the data source. You cannot use the ODBC connection type, as ODBC cannot support multiple passwords.

            If there is a database password applied to the selected file, type the password into the “Database Password:” text box. This would be the file-level security password. In the “Session UserID:” text box, type the user name used for your user-level access to the database, if needed. In the “Session Password:” text box, enter the password for your user-level “Session UserID:” security identification. Click the ellipsis mark button at the right end of the “System Database Path:” text box to launch the “Open” dialog box that allows you to select the Access security file (.mdw) associated with the current database, if applicable. When done, click the “Finish” button to set the data source.

Access or Excel DAO in Crystal Reports 2013: Instructions

1. To use Access or Excel DAO in Crystal Reports 2013 to make a connection, click the small plus sign next to the “Access/Excel (DAO)” folder to create the initial connection, or double-click the “Make New Connection” icon under that folder to create a new connection if there are already pre-existing connections of that type established.
2. Click the ellipsis (…) button at the right end of the “Database Name:” box to open the “Open” dialog box.
3. In the “Open” dialog box, use the “Look in:” drop-down at the top of the dialog box to navigate to the folder that contains the file to use as the data source.
4. Click the desired file name in the list of available files, and then click “Open” to set the selected file as the data source for the report.
5. Use the “Database Type:” drop-down to select what type of data object the file that you just selected is.
6. To create a secured connection to the data file, check the “Secure Logon:” checkbox to enable the next four fields, which you can use to set the secure access information.
7. If there is a database password applied, type the password into the “Database Password:” text box.
8. In the “Session UserID:” text box, type the user name for user-level access to the database, if needed.
9. In the “Session Password:” text box, enter the password for your user-level “Session UserID:” security identification.
10. Click the ellipsis mark button at the right end of the “System Database Path:” text box to launch the “Open” dialog box that allows you to select the Access security file (.mdw) associated with the current database, if applicable.
11. Once you have set any necessary options in this dialog box, click “Finish.”