Experiments with DAO in MS Excel

Workbook showing button to run main application

A simple application to open a MS Access mdb file to:

1. List the contained tables
2. List the fields of a selected table
3. Import the data into the workbook

The workbook contains macros and has a vba reference to Microsoft DAO 3.6 Object Library. My file is set up for Office 2003 on a 64 bit Windows 7 machine. It maybe necessary to change the reference if it cannot be found.

Main Dialogue box

Running the main application provides dialogue box with buttons to select the available options.

Open db shows a file dialogue box to select an mdb file to open. Sample mdb file can be found with my materials library. Once a file has been selected a dialogue box displays showing the tables inside the data base file.

A single table can then be selected. Then the fields belonging to that table can be displayed or the data in the table can be imported into the workbook. The data is not linked, it is copied into the workbook.

Tables found inside the mdf 

As such its not necessary to do this with Excel as it is able to import data directly from a MS Access table. However using linked data can be a problem if the data file moves, as it can be near impossible to update the links, and the spreadsheet typically needs to be re-built from scratch. Though newer versions of MS Excel this is apparently easier to do.

Any case there are benefits to using MS Access to store data and retrieve using DAO. For example retrieving section properties more directly by name  rather than by column number. Though it is possible to write complex formula and use Vlookup to get properties by name. (eg. get section properties like Ixx, Iyy). This is the approach used by my AS4600 cold-formed steel design spreadsheet. Using vlookup tables is fine if just dealing with a single section and single segment, but when dealing with an entire building with many elements then DAO is the simpler more efficient option. {Or some alternative data base.}

Fields belonging to the selected table

The worksheet can be found here: dbExperiments.xls

Other Approach to Viewing mdb Files in Office without MS Access
For a better view and querying of MS Access files look in the Microsoft office folder and find the main executable file for MS Query (MSQRY32.EXE for Office 2003). This uses .dqy files which are simple text files which can be edited with notepad or other plain text editor.

MS Query

ODBC Data Source Administrator

---

Revisions:
[19/06/2014] Original
[23/04/2016] Changed download links to MiScion Pty Ltd Web Store