K2 Tech Update | Tech Tip

Back to Previous Page Print

Creating ODBC Connections With Peachtree And Excel

Newer versions of Peachtree software allow users to establish Open Database Connectivity (ODBC) connections between the Peachtree database and external applications, such as Excel and Access. With an ODBC connection in place, users can link data from their Peachtree accounting application to external applications for reporting, analysis, fraud detection, and other purposes.

To establish an ODBC connection between Peachtree and external applications, you must first set up the ODBC environment; note that this is a one-time process. According to Peachtree, to do so you will need to perform the following steps.

  1. Select Start, Settings, Control Panel, Administrative tools, Data Sources (ODBC).
  2. Click Add.
  3. Select Pervasive ODBC Engine Interface.
  4. Click Finish.
  5. Enter a Data Source Name.
  6. In the Data base Name field, find and select your Peachtree Company database.
  7. Click OK twice.

When performing step 3, if you do not see "Pervasive ODBC Engine Interface," it is likely because you are using a 64-bit operating system. In these cases, visit the solution offered in the Peachtree online customer support system by clicking here.

Once the ODBC environment is established, you can make a connection from Peachtree to Excel, by first opening Excel. Then, from the Data tab of the Ribbon, choose From Other Sources, followed by From Microsoft Query to open the Choose Data Source dialog box pictured in Figure 1.

Fig. 1

Figure 1 - Selecting Peachtree Data Source from the Choose Data Source Dialog Box 

Upon clicking OK in the Choose Data Source dialog box, you will presented with the Logon to dialog box shown in Figure 2. In this dialog box, enter the User Name of Crystal, leave the Password blank, and click OK.
Fig. 2

Figure 2 - Logon to Dialog Box for Peachtree ODBC Query 

Next, you will be presented with the option of selecting the table and fields you wish to pull into Excel. In the example shown below in Figure 3, data from the Budget Details table is being extracted into Excel.
Fig. 3

Figure 3 - Extracting Budget Details Data via ODBC into Excel 

Upon clicking Next in the Query Wizard - Choose Columns dialog box presented in Figure 3, you will be asked if you want to filter and/or sort the data as part of the query. Generally, you should answer "no" to both of those questions. Upon doing so, you will be prompted as shown in Figure 4 as to whether you want to return the data to Excel or edit the query in Microsoft Query. In most cases, returning the data to Excel will be the appropriate choice; click Finish to complete the process.
Fig. 4

Figure 4 - Returning Data to Excel from an ODBC Query 

Lastly, Excel will present the Import Data dialog box shown in Figure 5. In this dialog box, indicate where and how you want the data to appear in Excel.
Fig. 5
Upon clicking OK in the Import Dialog box shown above, through the ODBC connection you established, Excel will link to the Peachtree database and import the data into the location you specified. Figure 6 displays how the data might appear once it is in Excel. Of course, once it is in Excel, you may manipulate any way you desire.
Fig. 6

Figure 6 - Peachtree Budget Data Imported into Excel via ODBC 

The ability to establish ODBC connections from Peachtree to other applications is a welcome addition to the many members of the Peachtree user family. Once the ODBC environment is established, as you can see here, the process for creating the connection is a relatively simple and easy one for most Peachtree users to complete

For a video presentation of this tip, visit www.tinyurl.com/k2tips58.

CONTINUING PROFESSIONAL EDUCATION FOR ACCOUNTING AND FINANCIAL PROFESSIONALS

K2 Enterprises | 1250 SW Railroad Avenue - Suite 240A | Hammond, LA 70403 | (985) 542-9390

© 2010 K2 Enterprises | Email Us| Site Map