Skip to main content

How to run a Direct Query on an ArchivesSpace database with a Read Only account

Problem/Question​

A user wants to generate a custom report using a SQL query.

Resolution​

If you need to run a query directly on an ArchivesSpace database, you can use Microsoft Excel to pass the MySQL query without having to pre-load the tables.

Prerequisites​

You will need:

  • A Read-Only MySQL account and password (usually created by your hosting provider or if locally hosted your database administrator)
  • Microsoft Excel with Power Query installed
  • The ADO.NET Driver for MySQL (Connector/NET) installed on the local machine
  • You may need a local administrator account on your machine to install the supporting items listed above

Steps​

  1. Launch Microsoft Excel (you'll need at least a version that supports Power Query)

  2. Select the Data tab and then Get Data | From Database | From MySQL Database (Some versions of Excel have New Query | From Database instead)

    Excel Data Menu - From Database

  3. On the MySQL database screen that appears, select the Advanced options, and complete the form by entering:

    • Server IP
    • The database name (archivesspace)
    • A Command timeout in minutes value
    • The SQL select statement/query

    MySQL Database Connection Dialog

  4. Leave the default 'Include relationship columns' checked and click OK.

    MySQL Database Advanced Options

  5. The query results will load into Excel in the Power Query Editor window.

    Query Results in Excel

Additional Notes​

  • The query results can be refreshed at any time using the Refresh button in Excel
  • You can save the Excel workbook with the connection information for future use
  • Consider creating views in the database for frequently-used complex queries
  • Always test your queries with LIMIT clauses first to avoid overwhelming Excel with large result sets