ODBC Database Access

 

In AA Console we added an export to Microsoft Access feature to make the database available outside of the application for reporting and other purposes.  This was done as a workaround to the fact that there isn’t an easy way to get to a Microsoft SQL Server CE database from external tools (no ODBC driver, in particular). Our newer products (PDQ Deploy Pro and PDQ Inventory) use SQLite instead, and this is one of the reasons.  There are many tools out there to use SQLite including ODBC drivers which will allow for Microsoft Access to directly connect to the database, but it does take a bit of configuration.  This quick how-to guides you through the process.

Step 1 – Download and Install the ODBC Driver

The most popular ODBC driver is an open source one kindly created and provided by a developer named Christian Werner.  You can download it from his website.  Grab the 32 or 64 bit version, as you need, and install.  It’s very quick and will add itself to the list of available drivers in the ODBC control panel.

Step 2 – Configure a Data Source

Open the ODBC Data Source Administrator tool from the control panel.

ODBC Administrator

Decide whether to create a data source as User, Machine, or File.  The only difference between the three is who can use them.  User for only the current user, Machine for all users on the machine, and File for anyone who has the file given to them (even on different computers as long as the driver is installed). We’ll use User for this how-to.

Click the Add… button and select SQLite 3 ODBC Driver.

SQLite 3 driver

Next, fill in the database information.  You only need the Data Source Name and Database Name, you can leave everything else default if you’re only looking to read the database for reporting purposes.

PDQ Inventory Data Source

The database name for PDQ Inventory is %ProgramData%\Admin Arsenal\PDQ Inventory\Database.db where %ProgramData% is either C:\ProgramData or C:\Documents and Settings\All Users\Application Data, depending on the version of Windows.  PDQ Deploy Pro uses the same location with, surprise surprise, PDQ Deploy Pro in place of PDQ Inventory.

The ODBC source is now ready to be used by your favourite reporting tool.  I’ll show you how to use Microsoft Access 2010 (the process is very similar for earlier versions).

Step 3 – Link to Microsoft Access 2010

Microsoft Access has two ways to use external data, either import or link.  Import, as the name implies, is a one time import of data which works well if want the data as Microsoft Access tables and don’t care about future chages (though you can re-import).  Linked tables always access the most current version of the data from the database and is probably what you want for reporting purposes and the method I’ll describe.

Create a new database or open an existing one and click the ODBC Database button on the External Data tab.

ODBC Database Button

In the Get External Data wizard, select Link to the data source by creating a linked table.

Link table resized 600

Select the data source you created in Step 2.

Select data source resized 600

Then select the tables you want to link.  If you know the tables you want, just select them, otherwise select all if you want to explore the data.

Select linked tables

You may be prompted to Select Unique Record Identifier for some of the tables. This is because some of the tables, for various technical reasons, don’t have a unique key defined. You can just click OK on those tables as the unique key is only needed if you’re going to be updating the data, not if you’re only going to be running reports.

Select unique record identifier

That’s it!  You now have direct access to the data for reporting and other nefarious purposes.  At the present time we don’t document the structure of the data, but hopefully the tables and columns are named well enough that you can get just what you want.

As always, if you have any questions or concerns just post them on our forums and we’ll help you as best we can.