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.mdb file, 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 Customers table. 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 (*.mdb)
• Enter:

Data Source Name: Northwind

Description: Sample database from Microsoft Access

• Press the Select... button, and navigate to the Northwind.mdb database 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 Customer Number
• 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:

DbLabels(Customer_Number)

• Drag a variable node to the diagram, name it Customer Table, and define it as:

DbTable(Customer_Number, Customer_Field)

• Evaluate Customer_Table

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:

  CopyIndex(DbTable(Customer_Number,"CustomerID"))

This will now serve as our row index.

• Change the definition of Customer_Field to:

  index tmp := DbLabels(Customer_Number) do subset(tmp <> "CustomerID")

• Change the definition of Customer_Table to

  DbTable(Customer_Number, Customer_Field) [@Customer_Number = @CustomerID]

• Re-evaluate Customer_Table. You should see a two-D table having indexes: CustomerID and Customer_Fields

  

See Also

• DbQuery
• Querying Access database from Analytica 64
• Database functions