Skip to content
  • There are no suggestions because the search field is empty.

Query the Acctivate Database in Excel

This article explains an easy way to load an Acctivate Database Query in Excel.

The Microsoft Power Query is an easier way to quickly create a report directly in Excel to access your Acctivate QuickBooks Inventory Management data.

Prerequisite:

Find your Database Server Name (i.e., Microsoft SQL Server Instance)
  1. Launch the Acctivate Database Maintenance app from the Windows Start Menu on any workstation that has Acctivate installed.
  2. Open the Database Sources window from the Database menu
  3. Click "Acctivate" and take note of the Server Name on the right-side of the window.
    • The Server Name will be formatted as SERVERNAME\ACCTIVATE if you are hosting your database on your network.
    • If Alterity is hosting your database, it will have a port number, like data-central.acctivate.com,12345
  4. You won't need a password if you're a Windows admin on the server running Acctivate. Otherwise, you'll need to obtain your SQL Server credentials from your IT/server support team. If you're running our default "\ACCTIVATE" instance, you can also ask our support team for assistance.

ServerName

In Excel:

  1. Switch to the Data tab
  2. Click Get Data > From Database > From SQL Server Database

    ExcelGetData
  3. Enter the Server name that you found in Prerequisite step 1c above.
    1. Be sure to enter the computer name AND instance (e.g., SERVERNAME\ACCTIVATE) for servers on your network.
    2. The Server Name AND Port number must be entered for hosted databases. Excel requires them to be separated with a colon (e.g., data-central.acctivate.com:12345
  4. Leave the Database (optional) blank and click OK
  5. You'll be prompted for your User name and Password if you're not connected as a Windows Admin. This was mentioned in Prerequisite step 4 above. Enter your credentials and click Connect.
  6. The Navigator should now load, showing you the full list of Acctivate data views that can be loaded. We recommend that you avoid the tables beginning with "tb" prefix. For example, ✅"Customer" not ❌"tbCustomer"
  7. Select the item you want to load into Excel (e.g., Customer) and click Load
  8. This will load the entire table with all columns. You can double-click on the Query in the Queries & Connections panel on the right-side to make adjustments.
  9. Here's some tips and tricks to clean up your query.
    1. Select Columns: Hold down CTRL to select multiple columns to be removed. You can also just select the columns you want to keep, then click "Remove other columns"
    2. Date Formatting: Right-click on Date columns and use the Transform menu to Format as Date only or Month, etc.
    3. Sort Columns: Click the arrow button on the Column title and choose Sort Ascending or Descending. You can sort on multiple columns sequentially.
    4. Keep Top Rows: Once sorted, you can use the Keep Top Rows option to only display the Top 100 records or any number desired.
  10. Once your data is loaded, use the Excel built-in formatting tools like "Accounting" for currency fields and various formats for Date fields.
  11. Click Refresh on the Query tab to make sure your data is up-to-date. Excel will also prompt you to refresh your data when opening the Excel file in the future.