DbQuery/Step-by-Step querying Microsoft Access
This mini-tutorial takes you through a step-by-step process of connecting to and querying data from a Microsoft Access database using DbQuery.
Find the Database
Microsoft Access 2000 and later includes a sample database called Northwind that we will use for this exercise. First, locate this database on your computer and view it in Access:
- Start Microsoft Access
- Select File → Open..., and find the
Northwind.mdbfile, usually at:
C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb
- If you don't have a local copy, the database can be downloaded from Microsoft Templates at this link
- After opening in Access, it may display an introduce dialog. Click through this and examine the list of Tables. Open the
Customerstable. We'll query this table from Analytica.
Set up a Data Source
Here you will set up a DSN for the Northwind database, which will allow you to connect to it by name from DbQuery. To do this, follow these steps:
- Start → Control Panel → Administrative Tools → Data Sources (ODBC)
- Click the System DSN tab.
- Press Add..., select Microsoft Access Driver (
Data Source Name: Northwind
Description: Sample database from Microsoft Access
- Press the Select... button, and navigate to the
Northwind.mdbdatabase file identified earlier. Select it.
- Press OK to complete the Data Sources dialog.
Now we have a data source named
Northwind that we can connect to using DbQuery. With this datasource, we can query any of the tables in the Northwind database.
Set up the Analytica model
Now you will create the query in an Analytica model.
- Start Analytica
- Drag an index node to the diagram, title it
- Set its definition to:
DbQuery("DSN=Northwind", sql: "select * from Customers")
- Evaluate it.
At this point, if you've done things correctly, you'll see an index from 1 to 91 (or however many records are present in your own Customers table).
- Drag another index node to the diagram, name it
Customer Field, define it as:
- Drag a variable node to the diagram, name it
Customer Table, and define it as:
The data is now imported (as shown below).
Reindexing the Table
For convenience, we may want to use the
CustomerID column of the table as the row index. To do this, we'll re-index the data.
- Create a new index node, name it
CustomerID, defined as:
This will now serve as our row index.
- Change the definition of
index tmp := DbLabels(Customer_Number) do subset(tmp <> "CustomerID")
- Change the definition of
DbTable(Customer_Number, Customer_Field) [@Customer_Number = @CustomerID]
Customer_Table. You should see a two-D table having indexes:
Enable comment auto-refresher