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!



Downloads

Comments

  • Jordan shoes mentioned Gene to pay off the discredit, a disunion of Nike

    Posted by TaddyGaffic on 04/20/2013 07:12am

    The sole of shoes are made from high quality rubber material and come with the lining. One can easily search for Vans footwear in the various online stores where they are easily available at great discount prices. These come in almost an infinite amount of [url=http://markwarren.org.uk/property-waet.cfm]air max 90 uk[/url] color and are usually on the cheap side. Experts as well as the rights of employees in regards to the apple can annihilate the Nike Air Max For travel arrangements in developing abjection can be a acceptable accord added in comparison to they're prevention, nonetheless they do accommodate sports sports athletes while using high duke inside their profession. Bargain Nike Air Max 2009 aswell provides countless jobs inside the nations absolutely area it's hardly accessible to acquire a job. In Indonesia, for archetype nike air max 90, even acknowledging baby assets [url=http://markwarren.org.uk/goodbuy.cfm]nike free uk[/url] in barter for plan Air Max, however, Nike bargain shoes inside a aught bulk adaptation and utilisation of the circadian needs.. Nike Zoom Vapor VI Tour Men`s tennis shoe is a great shoe popular in the tennis courts. It has Fly wire upper for ultra support and light weight ability, it sits on a frame that gives extra heel support and contains a full-length durable rubber support and herring bone pattern. It also has extra traction for all surfaces of play.. Write a Shakespeare Sonnet without any effort! Express loving [url=http://markwarren.org.uk/goodbuy.cfm]nike free[/url] feelings in a sonnet in Shakespeare's own words in 15 minutes. The rhyme scheme of a Sonnet is a,b,a,b,c,d,c,d,e,f,e,f,g,g. Think of a theme like "love" "death" or "deceit", then choose your lines in the rhyme pattern and put a Sonnet together using the programme

    Reply
  • NIce example,.

    Posted by zaxonus on 12/05/2006 05:10am

    This little project was exactly what I was looking for. Namely an example allowing me to figure out how to connect my VC++ application to a Microsoft Access database. Now I still have to figure out how I can input Japanese characters in my table. But other than that it seems to work. Good

    Reply
  • problem

    Posted by narencool on 06/16/2005 05:09am

    i having the problem of naot locating the header files AFXWIN.h AFXDISP.h

    Reply
  • Why does it only works with acces 97?

    Posted by Legacy on 08/05/2003 12:00am

    Originally posted by: matthijs

    If I convert the file to an access xp or 2000 file I'll get an error. Why?

    • To run with Access 2003 generated mdb file...

      Posted by jmfraser on 06/04/2006 09:19pm

      I had this problem, too and I found the answer elsewhere in CodeGuru. Add the following line to the InitInstance function of your application: AfxGetModuleState()->m_dwVersion = 0x0601;

      Reply
    Reply
  • Password

    Posted by Legacy on 07/23/2003 12:00am

    Originally posted by: Alexi

    how to set a password while using create function in cdatabase for creating an access database

    • set password

      Posted by maunopulos on 09/28/2006 07:33am

      database.Create(lpszFile,dbLangGeneral,"pwd=*****"); 
      //
      database.Open(lpszFile,FALSE,FALSE,";PWD=*****");

      Reply
    Reply
  • How to get numeric and date values returned from the database?

    Posted by Legacy on 07/01/2003 12:00am

    Originally posted by: Eng Seng

    Well the (cast COlevariant to CString) code above works if the field returned is a string, but when the field is numeric or date, you're not able to cast it this way. So do we've to cast COlevariant -> Integer or Date -> CString? Anyone knows how? Please post the code here, thanks.

    Reply
  • CDatabase

    Posted by Legacy on 06/12/2003 12:00am

    Originally posted by: Rajesh

    how to set a password while creating cdatabase for mdb
    

    Reply
  • how to set a password while using create function in cdatabase for creating an access database

    Posted by Legacy on 06/12/2003 12:00am

    Originally posted by: Rajesh

    please help
    

    Reply
  • THX!!

    Posted by Legacy on 06/03/2003 12:00am

    Originally posted by: Isse

    THX MAN! Just what I needed to get started :D

    Reply
  • Excel database

    Posted by Legacy on 05/31/2003 12:00am

    Originally posted by: charu

    will the code be same if i have to read/write to Excel database?
    

    Reply
  • Loading, Please Wait ...

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: November 20, 2014 @ 2:00 p.m. ET / 11:00 a.m. PT Are you wanting to target two or more platforms such as iOS, Android, and/or Windows? You are not alone. 90% of enterprises today are targeting two or more platforms. Attend this eSeminar to discover how mobile app developers can rely on one IDE to create applications across platforms and approaches (web, native, and/or hybrid), saving time, money, and effort and introducing apps to market faster. You'll learn the trade-offs for gaining long …

  • Live Event Date: October 29, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT Are you interested in building a cognitive application using the power of IBM Watson? Need a platform that provides speed and ease for rapidly deploying this application? Join Chris Madison, Watson Solution Architect, as he walks through the process of building a Watson powered application on IBM Bluemix. Chris will talk about the new Watson Services just released on IBM bluemix, but more importantly he will do a step by step cognitive …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds