Exact Financials

General ODBC functions

If you can access the Exact database using the Excel Add-in, you can also do this directly using Microsoft Query. Microsoft Query is a Microsoft product and the basic principle of this product is described here.

With Microsoft Query, you can quickly retrieve tables and fields from the database. The data can be used for mailings or standard reporting. This chapter explains in short how this can be achieved. For a more detailed description refer to your ODBC manual and / or Microsoft Excel manual.

Import External Data and Exact

Here you can find an extensive example showing the way you can quickly ask for the ledger account table. In the same way, you can ask for other data, e.g. the accounts receivable older than a certain number of days. It is also possible to send this data to a so-called Pivot table. See Pivot table in your Excel manual.

1. First select [Data, Import External Data, New Database Query...].
2. A screen called 'Choose data source' will pop up. Here you can select your user-defined ODBC connection to Exact Financials database or you can make a new connection by choosing <New datasource>. If you create a new ODBC connection you will have to fill in the correct parameters as described in your ODBC manual.  Also decide if you want to use the Wizard to create your query by enabling / disabling 'Use the Query Wizard to create/edit queries'
3. Log into the Exact database by using your username and password.
4. A list of the database tables is shown:
   
   
    
5. Individual fields can be added by expanding the table by clicking on the + and moving the columns or tables to the right:
   
   
   
   For a description of the fields in the database, see the [Data dictionary help documentation].
    
6. Click 'Next' to add the filter criteria:
   
   
   
   In the example above, only ledger records where adm_nr equals 621 will be shown, ie all g/l accounts of company 621 will be shown.
    
7. After clicking 'Next', you can choose how the records should be sorted:
   
   
   
    
8. After clicking 'Next', you can return the results directly to Excel or you can view or edit the query in Microsoft Query:
   
   
    
9. When returning data directly to Excel, the results are:

   

10. Now you have your general ledger account table in Excel. You can adjust the list by activating a cell in the list followed by clicking the right mouse button. Now you can edit your query using Edit query or extract the data out of the database again using Refresh data. Please note that if you choose the latter option, the subtotals are also refreshed.

Combining ODBC and the Excel Add-in for Exact

Using the Excel Add-in for Exact, the ledger account table defined in the preceding chapter can be extended very easily. To do this, you have to select the function acc_bal in the first column next to the list. Here you can enter values as presented in the list of the preceding chapter.

Further on, you can copy this field to all other fields in this column until the last line in the list is reached. Now you have a dynamic overview of your balance sheet.

Excel Add-in Manual > Contents > General ODBC functions