CDaoRecordset & SQL execution???

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 :)
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sllsglAuthor Commented:
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 ;)
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!!

sllsglAuthor Commented:
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 :)

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.