Retrieving, Writing, and Creating Data in a Microsoft Access File (CDaoDatabase and CDaoRecordset)

Environment: Compiled and tested on VC++ 5.0 and 6.0 running Windows 2000/NT/XP

Introduction

I’ve been looking all over CodeGuru and third-party Web sites across the net, hoping to find some sort of information about how to retrieve and write tables with data using the CDao* classes. My searches unsuccessfully ended without a single clue about how to achieve this task. What we are going to do here is create/write/retrieve data from a Microsoft Access file (.MDB)—quickly, dynamically, and simply. Our example is based on a dialog-based application.

Setting Up Our Interface

To set up our database interface, the first thing we have to do is include afxdao.h in our project StdAfx.h file. So, insert

[#include afxdao.h] (with those brackets)

in the StdAfx header file.

We are going to use two classes: CDaoDatabase and CDaoRecordset. Anywhere in your program, you need to construct those two objects, like so:

CDaoDatabase database;
CDaoRecordset recordset(&database);

Creating Our Database File

If we were dynamically creating an Access file, we wouldn’t be using the CDaoDatabase::Open(); command because Create(); automatically opens and connects to the database for us.

CString lpszFile = "C:\\Database.mdb";
database.Create(lpszFile);

Opening an Existing File

database.Open(lpszFile);

Excellent. Now we’ve got our database connection up and running, and we are ready to store some data in it for us to read next. Now here I’ll take a break and explain how the tables work and their architecture. This will become very useful if you intend to work with the database in the future!

A table is sort of a data sheet. You can have multiple tables in a database file, as many as you’d like. Each table is built from columns and rows; the corresponding value between a row and a column is called a field. By matching the row number and the column name, you cross the field value and then you can use it. Here is a schematic picture of what a table looks like:

In this example, you can see that we have crossed row #3 with a column called “Name.” We have gotten the field that crosses those two parameters and gotten the person’s name—in our case, Nick. The number of the rows is called the Index and it represents the order of entries by incremental value (ie +1).

Now, back to our subject. We first need to create a table in our database file. How do we do that, you ask? We create a SQL executable string and tell our database object to execute it; for example:

CString SqlCmd = "CREATE TABLE MyTable
                  (Name VARCHAR(20),Age VARCHAR(3));";
database.Execute(SqlCmd);

This SQL command creates a new table in our database access file, called MyTable. It has two columns; one is Name and the second is Age. The Name column data limit is 20 characters and the age is 3 characters for each field. Simple, isn’t it?

Note: You can only create each table name ONCE per file unless you delete it by calling Execute(); with the proper SQL string.

Adding Data to Our Newly Created Entries

We will feed our database file with the names of two people along with their proper ages. To do so, we must open our database with our CDaoRecordset object. When we first created it, we supplied a pointer to our CDaoDatabase object. Now they are both connected. To open our Recordset and start writing data, we do the following:

recordset.Open(AFX_DAO_USE_DEFAULT_TYPE,
               "SELECT * FROM MyTable", 0);
database.Execute("INSERT INTO MyTable(Name);");
database.Execute("INSERT INTO MyTable(Age);");

recordset.AddNew();
recordset.SetFieldValue("Name","Chris");
recordset.SetFieldValue("Age","13");
recordset.Update();
recordset.MoveNext();
recordset.AddNew();
recordset.SetFieldValue("Name","Joe");
recordset.SetFieldValue("Age","20");
recordset.Update();

Okay, here’s a quick rundown on what’s happening here, although it’s quite self-explanatory. Here are the steps involved:

  1. Open the Recordset object with the SQL command “SELECT * FROM MyTable”. This means that the Open function returned the table “MyTable” with all of its data accessable, either to write or retrieve.
  2. Call Execute() with a SQL command to insert two new columns in the table.
  3. Call AddNew(), thereby allowing us to add a new field of data into the file.
  4. Call SetFieldValue twice, every time for each column on the same row.
  5. Set the Name and Age and called Update() to update the database file.
  6. Call MoveNext() to move the Recordset pointer to the next empty row; we have repeated the process of adding new fields.

That is it for adding new data to our file, but what if we want to read it back into a CString object?

This part is trickier. It still is very simple and understandable, but we use a bit of casting, as we call it.

The CDaoRecordset::GetFieldValue() takes two parameters. One is the column name we want to retrieve info from; it corresponds with the current placement of the Recordset pointer that you can move freely by using the following functions:

::Seek();
::MoveFirst();
::MoveNext();
::MovePrev();
::MoveLast();

and couple more I won’t mention here.

Back to where we were. The second parameter is a COleVariant object. COleVariant is a COM-based object. Our field data would be stored in this object, but the tricky part is to convert it into a readable CString value or any string variable you want. This is how we do it:

COleVariant olevar;

Assuming our CDaoRecordset object is open, we call:

while(!recordset.IsEOF());
{
    olevar.ChangeType(VT_BSTR, NULL);
    recordset.GetFieldValue("Name",olevar);
CString strData = (LPCSTR)olevar.pbstrVal;
// Code for inserting data into a listbox, for example
recordset.MoveNext();
}

ChangeType() changes the COleVariant object into a BSTR (ugly and messy string type). Then we type cast the string value of olevar using the pbstrValue method, store it into our CString object, and do whatever we want with it. This will continue until the pointer of Recordset reaches the end of the table.

Violà! We’ve got it. Simple, wasn’t it? I sure hope it helps people who are looking for a simple and quick way to work with the powerful database engines that MSVC++/Microsoft has to offer.

Enjoy! And I’ll make sure to have a working source and demo workspace available as soon as I get the time!

More by Author

Get the Free Newsletter!

Subscribe to Data Insider for top news, trends & analysis

Must Read