Improve company productivity with a Business Account.Sign Up


CDaoRecordset & SQL execution???

Posted on 1998-04-14
Medium Priority
Last Modified: 2013-11-20
I have already had a CDaoRecordset TableSet using Access97 in VC++5 (WinNT).  How can I execute a SQL SELECT statement in this tableset?  I know that it is impossible to execute one in a tableset so can anyone guide me (in detail) on how to setup another Dynaset 'mirror' of the original tableset database so that I am able to execute SQL in?

Also, after executing a SQL statement, where can I access the extracted records data?   I simply wants to display & print the result after SQL execution, how should I do it? (If possible, using Access97 to do the display & print task, but must be able to be controlled in my application.)

Thanks alot :)
Question by:sllsgl
  • 2
  • 2
LVL 23

Accepted Solution

naveenkohli earned 150 total points
ID: 1317689
Hi sllsgl,
I don't see any reason that you can not execute SELECT with ur DaoRecordSet.

CDaoRecordSet::Open ("*.mdb", SELECT * from yourdatatable,..) would do this for you. You can see the syntax in help.

As for as seeing and printing the data goes, thats not straight forward with DaoRecordset.
Follwing is a snipet from my code which i use to get parameters of a drawing object. The field value is returned in COleVariant. Then you have to translate that variable depending on the type returned by the GetFieldValue () function. e.g in the code below i am getting the field value of first filed in my datatable m_HoleParams.
The StrVARIANT () function translate the field value to CString format which i can use to print the results. I have also included a part of the translation code.
I hope this shuld solve your problem.
If you need complete code for this translation and stuff, let me know. I will be more than happy to help you out.
Have Fun Buddy.......

COleVariant varValue;

m_HoleParams->GetFieldValue (short (0), varValue);

// Depending on the type of hole, stuff the parameter
// dialog box entries

strValue = DaoTranslate::StrVARIANT (varValue);
DaoTranslate::StrVARIANT(const COleVariant& var)
      CString strRet;
      strRet = _T("A3DS");
            case VT_EMPTY:
            case VT_NULL:
                  strRet = _T("NULL");
            case VT_I2:
            case VT_I4:
            case VT_R4:
            case VT_R8:
            case VT_CY:
                  strRet = COleCurrency(var).Format();
            case VT_DATE:
                  strRet = COleDateTime(var).Format(_T("%m %d %y"));
            case VT_BSTR:
                  strRet = V_BSTRT(&var);
            case VT_DISPATCH:
                  strRet = _T("VT_DISPATCH");
            case VT_ERROR:
                  strRet = _T("VT_ERROR");
            case VT_VARIANT:
                  strRet = _T("VT_VARIANT");

//not complete code

Author Comment

ID: 1317690
The database is already opened upon starting the program, do I have to close it first then open again with the SELECT SQL Statement?  
    m_pSet->Close();                             // Is there other param?
    m_pSet->Open("*.mdb", SQLStr);    

Does that means the data fields currently under my m_pSet handle is now the extracted records' data?   The current recordset is already the selected records database, no need to perform a "ReQuery"?

To move from record to record, I still have to move record manually to extract one field after the other, is that correct?  Or do I have an alternative to read the whole record in one go?

Thanks alot for your prompt help  ;)
I will grade u as soon as I have tried them out :)

PS. I will appreciate if u can email me your complete code for reference ;)
LVL 23

Expert Comment

ID: 1317691
Regarding reading the whole data at one time....hmm.... thats not possible.
int size = m_pSet->GetFieldCount ();
for (int i=0; i < size; i++){
 m_pSet->GetFieldInfo (short (i), COleVariant value);

You don't have to close the data base. Just requery it. But make sure to check if you can requery it.

if( !m_pSet->CanRestart( ) ) {
            TRACE ("Can't Restart the data base!\n");
else {
      m_pSet->Requery( );

One thing is very imp. in Dao database use.
If your app. is DLL then make sure that you terminate the Dao worksoace before you ExitInstance () of DLL.

AfxDaoTerm ();

I can send you the complete code tomm. after making changing some stuff in that. Its proprietry code, so we have to take care of some technical issues in that.

Have FUn Buddy!!


Author Comment

ID: 1317692
I am now able to create a new dynaset to store the extracted records using the below:-
    CMsgSet result(m_pSet->m_pDatabase);  // Create another recordset from my original
    result.Open(dbDynaSet, SQLStr);   // SQLStr is my SQL SELECT/WHERE statement
    ...... processing records ....
    result.Close();       // After I have read all records

Do u think I am right?  I am able to extract the records I wanted.

A shocking news from my superior is that in WinNT, Access97 database is assigned a Administrator Privillege now, but after I have completed my project, he wants to assign the database to some user's privillege where only users with a valid password can get access to it.

 I never thought of that when I created the program, what should I do to allow his implementation?  I know something about passing a password in the Open() functions if the database is protected but my applications automatically opens the database access upon starting, I don't know where should I provide the password?  I don't seems to find any Open() functions besides the one I have added as stated above.  If I cannot provide the password, the database would have denied any access & my program would have crashed.

Help ... Help ... ;)  

Also, he seems to prefer ODBC very much as compared to DAO, stating that using ODBC, the program will not have to be changed when in the future he is going to replace Access database with Orcale Database.  Is he correct?  And is ODBC really simpler?

He seems wanted me to convert my DAO application to an ODBC ones, can I do that, how should I do it, is it very complex so much so that it will be easier to just rewrite the whole app?

Thanks for any info :)


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article, I'll describe -- and show pictures of -- some of the significant additions that have been made available to programmers in the MFC Feature Pack for Visual C++ 2008.  These same feature are in the MFC libraries that come with Visual …
Introduction: Ownerdraw of the grid button.  A singleton class implentation and usage. Continuing from the fifth article about sudoku.   Open the project in visual studio. Go to the class view – CGridButton should be visible as a class.  R…
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
In the video, one can understand the process of resizing images in single or bulk. Kernel Bulk Image Resizer is an easy to use tool for resizing large number of images. One can add and resize multiple images with this tool in single go. The video sh…

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question