Use Microsoft® Excel® to Query the Database (AV 2013)

1. Start a New Database Query.

  • Open a new Workbook in Excel®
  • Go to the Data menu ➤ Get External Data ➤ New Database Query (this path may slightly differ on different releases of Excel®).

2. Select the Data Source.

Next, we'll need to select the Data Source (i.e. Database) from which to search for data. The AcctVantage SQL Server should have already been set up. If it has not, please see the article on Setting Up the AcctVantage SQL Server.

  • Select AcctVantage SQL Server as the Data Source and click OK to proceed.
Select the Data Source.

3. Login to the AcctVantage SQL Server.

If you didn't enter credentials when setting up the User DSN then you'll be prompted to enter your login information at this time.

Your Username and Password are the same credentials that you use to login when using the AcctVantage Client Application.

After logging in, Microsoft® Query® will open. This is the application that will be used to query the AcctVantage Database.

Login to the AcctVantage SQL Server.

4. Select the table(s) to query from.

When Microsoft® Query® opens, you will see two things:

  1. The query window.
  2. A list of all tables in the AcctVantage Database.

List of Tables in the AcctVantage Database.

From the list of available tables select the table from the AcctVantage Database that you want to query and click Add Table. I'll be using the Client table in this example.

List of Tables in the AcctVantage Database.

5. Select the fields to be queried.

After the table(s) to be queried from are selected, next we need to decide which fields should be queried.

  • Double-click (or click & drag) fields from the Client table down into the "body" of the query window.
  • You can choose to sort the columns or enter more specific search criteria at this time as well.
  • When all of the desired tables/fields have been chosen click the Return Data button.

6. Where do you want to put the data?

Select a cell to put the data (this will be the top/leftmost cell of information) and click OK.

Where do you want to put the data?

7. The finished query.

The queried data will be placed in a table in the following format. This spreadsheet can now be saved, modified, etc...only limited by the functionality of Excel®!

The finished query.