Microsoft Query

An Excel spreadsheet can be an easy way to query and display information dynamically linked from the Acctivate database.   A Microsoft Query allows you to view information in an easy to update spreadsheet that can be used to update records and re-import into Acctivate or can be saved and refreshed to view real-time information from Acctivate.

The basic steps to setup a Microsoft Query or Microsoft Excel Query are below, these may vary slightly depending on your version of Microsoft Excel.

  1. First, you must Establish the ODBC Database Connection.
    • Users who are hosted may may need to contact the hosting provider for assistance.
  2. Next, create your Microsoft Excel Query.

In Excel:

  1. Open Excel
  2. Select the Data tab
  3. Choose Get Data > From Other Sources > From Microsoft Query 

Choose Data Source:

  1. Select the Acctivate Data Source
  2. Un-check Use Query Wizard make sure the option Is Not Checked
  3. Click OK
    •  
  4. Enter SQL Server Login information
  5.  Click  “Options” to expand the default database options
  6. Select the drop down for Database and choose your Acctivate Database
  7. Click OK

Add Database Views and Select Fields

You are now ready to select the Views and Fields you want to return to Excel.  You can run Excel Queries from any workstation that has a database connection configured.

To access individual data views:

  1. Start a New Database Query by clicking the Data tab, then choosing From Other Sources > From Microsoft Query
  2. Select the Acctivate data source, then click “Ok” to open the Microsoft Query window
  3. Here, you can select individual tables to return data from. Select dbo from the Owner drop down list to see only tables and views pertaining to your company data.
    add tables
  4. Select a data table from the list, then click “Add” to add it to the query. In this example, we will use the Customer view.
  5. Once the data view has been added to the query, you can click and drag fields into the Microsoft Query window. Clicking and dragging a field name to the Query window will return all data contained in that field within a particular data view.
    microsoft query 2
  6. You can filter the query by any of the selected fields. These options are similar to the report filters in Acctivate. Click Next when you are done.
  7. When ready, click the open door icon to return query data to Excel.
    return data to excel
  8. Select the worksheet and cell where you would like to place the imported data and click OK.
    import data in excel
  9. To edit the query, right click on a cell within the data range, then click Table > Edit Query… to open the Microsoft Query window again.
    edit query
  10. Once you have created your query and saved the spreadsheet, the data will be refreshed each time you open this file in Excel. You can also click the Refresh button on the Data tab to reload the data from Acctivate.
Posted in Database, Reports - Last modified on April 4, 2019Yana Amaratana
Still need help?
Search all content on this site, contact support at 817-870-1311, or create a support ticket.