ODBC server

To connect to the Axon DataStore, you can create a Microsoft Open Database Connectivity (ODBC) server. ODBC is a C programming language interface that lets applications access data from a variety of database management systems (DBMSs). ODBC is a low-level, high-performance interface designed specifically for relational datastores.

After creating an ODBC server, you can link it to Microsoft Excel or Microsoft Access to view the data.

Before creating an ODBC server, contact your Axon representative to receive the following information:

  • Server name
  • Database name
  • Login information (username and password)

Once you have the above information, take these steps:

  1. Create a data source
  2. Connect to SQL server
  3. Test the data source
  4. After creating and connecting to the server, you can link it to Microsoft Excel or Microsoft Access.

Create a data source

The first step in creating an ODBC server is to create a data source. To do this from a Windows machine, take these steps:

  1. Select the Windows icon in your start/task bar.
  2. Type "ODBC."
    • After selecting the Windows icon, you will not see the search bar, but it will appear once you begin typing.
  3. Select ODBC Data source (32 bit).
    • This program is part of the Windows operating system and does not need to be downloaded.
  4. On the User DSN tab, select Add.
  5. Select ODBC Driver 17 for SQL Server.
  6. Select Finish.
    • You must use version 17 or greater. If you do not see this option, download and install the driver here.
    The image shows a step-by-step process for setting up an ODBC data source in Windows. The numbered steps highlight key actions, starting with opening the Start menu and searching for ODBC (steps 1 and 2). The user selects ODBC Data Sources 32-bit (step 3) and navigates to the User DSN tab in the ODBC Data Source Administrator window (step 4). The user then clicks Add and selects an ODBC driver, such as ODBC Driver 17 for SQL Server (step 5). The final step (step 6) is clicking Finish to proceed with setting up the data source.

Connect to the SQL server

Once you create a data source as explained above, you need to connect the data source to the SQL server.

  1. When you select Finish after creating the data source, a new window will appear. Enter a name for the data source (free-form text), description (free-form text), and the server name. Select Next.
    • Contact your Axon representative to receive the server name for your agency.
  2. Select the With SQL Server authentication using a login ID and password entered by the user
  3. Enter your username and password.
    • Contact your Axon representative to receive this login information.
  4. Select the Change the default database to option, enter your database name, and select Next. The rest of the entries in this section do not need to be adjusted.
    • Contact your Axon representative to receive the database name for your agency.
  5. Nothing on this screen should be adjusted. Select Finish.
    Warning

    Always use strong encryption.

    Do NOT blindly trust the server certificate (this forces the client to verify the identity of the TLS certificate received from the server)

    The image shows a step-by-step process for creating a new data source to SQL Server using the ODBC setup wizard. Step 1 involves entering a name and description for the data source and specifying the SQL Server to connect to. Step 2 highlights selecting an authentication method, with options including Windows authentication, Azure Active Directory authentication, or SQL Server authentication. Step 3 involves entering login credentials if SQL Server authentication is selected. Step 4 focuses on selecting a default database and additional settings. Step 5 is the final step, clicking Finish to complete the configuration.

Test the data source

When you select Finish after setting up the SQL server connection, a new window will appear and display a configuration summary. Select Test Data Source.
The image shows the ODBC Microsoft SQL Server Setup window displaying the configuration details for a new ODBC data source. It includes information such as the driver version, data source name, server, database, encryption settings, authentication method, and other connection properties. A yellow highlight emphasizes the Test Data Source button, which allows users to verify the connection before finalizing the setup.

Test completed successfully

If your test passes, select OK. The data source testing window will close, and you will again see the ODBC Data Administrator application. Your new data source will now display on the User DSN tab.
The image shows the SQL Server ODBC Data Source Test window confirming a successful connection, with messages indicating that encryption was enforced. The ODBC Data Source Administrator window is also displayed, showing the configured data source named Agency training, highlighted in yellow. This data source is using ODBC Driver 17 for SQL Server. The OK button is available in both windows, allowing the user to finalize the setup.

Test failed

If your test fails, select OK.

You will return to the final window in the SQL server connection workflow (step 5 in the previous section). Select Back three times until you reach the first window in this workflow (step 1 in the previous section).

Repeat steps 1-5 from the previous section and again test the data source until the test passes and your new data source appears in the ODBC Data Administrator application.
The image shows an SQL Server ODBC Data Source Test window indicating a failed connection attempt, with a highlighted message stating TESTS FAILED due to a login failure for a specified user. Below this, two additional windows display the ODBC setup process. One window prompts the user to enter a data source name and server, while the other contains advanced configuration options such as encryption, translation settings, and logging. An arrow connects these windows, indicating the setup process leading to the test failure.

Link ODBC server to Microsoft Excel

Once you have created an ODBC server, you can link it to Microsoft Excel and access the Axon DataStore. To create this link, follow these steps:

  1. Open a blank workbook in Microsoft Excel.
  2. Go to the Data tab.
  3. Select Get Data > From Other Sources > From ODBC.
  4. Reveal the list of data sources, choose your ODBC server, and select OK.
  5. Enter your username and password associated with that Axon DataStore. You can leave the Credential connection string properties (optional) field blank.
  6. Select Connect.
    The image shows a step-by-step process for connecting Microsoft Excel to an ODBC data source. Step 1 begins with opening Excel. Step 2 involves navigating to the Data tab and selecting From Other Sources. Step 3 highlights selecting From ODBC under the available options. Step 4 shows choosing an ODBC data source, in this case, Agency Training. Step 5 prompts the user to enter authentication details. Step 6 is the final step, clicking Connect to establish the connection and access the data.
  7. The navigator will open where you can select the arrow beside axon, dw or raw, depending on which data you want to populate in Excel.
  8. After a data preview appears, select Load.
  9. The data will populate in Excel.
    The image shows the final steps of importing ODBC data into Microsoft Excel. Step 7 involves selecting a table, such as Arrests, from the Navigator window, which displays available database tables. Step 8 highlights the Load button, which imports the selected data into Excel. Step 9 shows the successfully imported data displayed in an Excel table, confirming that the connection and data retrieval process were completed successfully.

Link ODBC server to Microsoft Access

Once you have created an ODBC server, you can link it to Microsoft Access and access the Axon DataStore. To create this link, follow these steps:

  1. Open a blank database in Microsoft Access.
  2. Go to the External Data tab.
  3. Select New Data Source > From Other Sources > From ODBC.
  4. Choose Import the source data into a new table in the current database and select OK.
  5. Select the Machine Data Source tab in the explorer window that appears and choose the ODBC server connection.
  6. Enter your username and password for with that Axon DataStore and select OK.
    The image shows a step-by-step process for importing data into Microsoft Access using an ODBC database connection. Step 1 begins by opening Microsoft Access. Step 2 involves navigating to the External Data tab and selecting ODBC Database under From Other Sources. Step 3 highlights the option to create a new blank database. Step 4 prompts the user to choose between importing data into a new table or linking to the data source. Step 5 involves selecting the configured ODBC data source, named Agency Training. Step 6 requires entering login credentials in the SQL Server Login window to establish the connection.
  7. Select which views to import into the Microsoft Access database.
    • You can also save your password for future ease.
    • Once you have selected all views you want to import, select OK.
  8. It may take some time for the views to import. Once the import is complete, you can save the import steps and close out of the import process.
  9. Double-click a view in the left panel to display the data for that view.
    The image shows the final steps of importing data into Microsoft Access using an ODBC database connection. Step 7 involves selecting tables from the Link Tables window, where multiple tables from the connected database are listed. Step 8 highlights the option to save import steps for future use. Step 9 shows the successfully imported table displayed in Access under All Access Objects, confirming that the data is now available for use.