CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 8 of 8
  1. #1
    Join Date
    May 2002
    Posts
    1,798

    How do you build a query in MSAccess programatically ?

    I have an MSAccess table Test3 that has the following columns:

    Column Name Data Type Nullable Default Primary Key
    ID INTEGER(9) No - 1
    LNAME CHAR(255) No - -
    FNAME CHAR(255) No - -
    ADDRESS CHAR(255) No - -
    EMAIL CHAR(255) No - -
    HIREDATE DATE Yes - -

    I am using VC7.0 on WinXPPro.

    Once the database has been opened, I have attempted to use the following code to build a query:
    Code:
    		// PERFORM A QUERY
    		CString csSQL;
    
    		csSQL = _T("SELECT ID, LNAME, HIREDATE from TEST3;");
    		m_db.ExecuteSQL(csSQL);
    This compiles and runs ok but no query appears in the MSAccess database.

    What am I missing here?

    Mike
    mpliam

  2. #2
    Join Date
    Jun 2002
    Posts
    1,417

    Re: How do you build a query in MSAccess programatically ?

    that only executes the query -- you have to call a different function to fetch the results. I have no idea what m_db object is, but since it has an execute method it probably also has fetch methods.

  3. #3
    GCDEF is offline Elite Member Power Poster
    Join Date
    Nov 2003
    Location
    Florida
    Posts
    12,635

    Re: How do you build a query in MSAccess programatically ?

    Is there some reason you don't want to use a CRecordset? You seem to be making this ODBC stuff a lot harder than it needs to be.

  4. #4
    Join Date
    Sep 2004
    Location
    Holland (land of the dope)
    Posts
    4,123

    Re: How do you build a query in MSAccess programatically ?

    ExecuteSQL is (as it says) for executing SQL. This means commands like DELETE, INSERT, DROP . .etc .. Commands that do no return anything.

    You want some return, in this case a recordset. Use a CRecordset to fill it with data.

    Code:
    CRecordset clRs (&m_db);
    CString clQuery = "SELECT some_fields FROM some_table_or_some_query";
    
    if (clRs.Open (AFX_DB_USE_DEFAULT_TYPE, clQuery))
    {
        // use clRs.GetFieldValue () to retrieve the data from each selected field
    
        clRs.Close ();
    }
    ....but no query appears in the MSAccess database.
    As you see, you don't make a query in Access, but you make Access bring the result of the query to you
    Last edited by Skizmo; February 26th, 2007 at 04:51 PM.

  5. #5
    Join Date
    Aug 2001
    Posts
    507

    Re: How do you build a query in MSAccess programatically ?

    -- What Skizmo says is right.

    However if you still need to build a query of ur own, and have to run it

    than u set m_strFilter variable of the CRecordset object to what ever query you want to run.

    Then you run Requery() function of the CRecordset object.

    Enjoy
    If you found my reply to be useful, please dont hesitate to rate it.


    DO NOT kick the Axe if it doesnt fall on your foot.

    Salman

  6. #6
    Join Date
    May 2002
    Posts
    1,798

    Re: How do you build a query in MSAccess programatically ?

    Skizmo - Your example works very nicely. Pretty much what I was looking for.

    Here's the exact code I used:
    Code:
    // CDatabase m_db;
    
    		CRecordset clRs (&m_db);
    		CString clQuery = _T("SELECT ID, LNAME, HIREDATE FROM TEST3;");
    		CString strValue;
    		if (clRs.Open (AFX_DB_USE_DEFAULT_TYPE, clQuery))
    		{
    
    			// use clRs.GetFieldValue () to retrieve the data from each selected field
    			clRs.MoveFirst();
    			clRs.GetFieldValue(0, strValue); printf("|%s|\n", strValue);
    			clRs.GetFieldValue(1, strValue); printf("|%
    s|\n", strValue);
    			clRs.GetFieldValue(2, strValue); printf("|%s|\n", strValue);
    
    
    			clRs.Close ();
    		}
    One concern however is the accompany debug output:
    Warning: ODBC Success With Info, Driver's SQLSetConnectAttr failed
    State:IM006,Native:0,Origin:[Microsoft][ODBC Driver Manager]

    Optional feature not implemented
    State:S1C00,Native:106,Origin:[Microsoft][ODBC Microsoft Access Driver]

    Warning: Driver does not support requested concurrency.
    Optional feature not implemented
    State:S1C00,Native:106,Origin:[Microsoft][ODBC Microsoft Access Driver]

    Warning: Driver does not support requested concurrency.
    Warning: Setting recordset read only.
    Sounds like I am missing something, but it works anyway.

    salman -

    ...set m_strFilter variable of the CRecordset object to what ever query you want to run. Then you run Requery()
    If I understand you correctly, trying the following code invariably crashes:
    Code:
    		CRecordset clRs (&m_db);
    		clRs.m_strFilter.Format("Select ID, LNAME, HIREDATE FROM TEST3;");
    		clRs.Requery();
    Thank you for your comments.

    Mike
    Last edited by Mike Pliam; February 27th, 2007 at 02:29 AM.
    mpliam

  7. #7
    GCDEF is offline Elite Member Power Poster
    Join Date
    Nov 2003
    Location
    Florida
    Posts
    12,635

    Re: How do you build a query in MSAccess programatically ?

    Quote Originally Posted by Mike Pliam
    Skizmo - Your example works very nicely. Pretty much what I was looking for.

    Here's the exact code I used:
    Code:
    // CDatabase m_db;
    
    		CRecordset clRs (&m_db);
    		CString clQuery = _T("SELECT ID, LNAME, HIREDATE FROM TEST3;");
    		CString strValue;
    		if (clRs.Open (AFX_DB_USE_DEFAULT_TYPE, clQuery))
    		{
    
    			// use clRs.GetFieldValue () to retrieve the data from each selected field
    			clRs.MoveFirst();
    			clRs.GetFieldValue(0, strValue); printf("|%s|\n", strValue);
    			clRs.GetFieldValue(1, strValue); printf("|%
    s|\n", strValue);
    			clRs.GetFieldValue(2, strValue); printf("|%s|\n", strValue);
    
    
    			clRs.Close ();
    		}
    One concern however is the accompany debug output:


    Sounds like I am missing something, but it works anyway.

    salman -


    If I understand you correctly, trying the following code invariably crashes:
    Code:
    		CRecordset clRs (&m_db);
    		clRs.m_strFilter.Format("Select ID, LNAME, HIREDATE FROM TEST3;");
    		clRs.Requery();
    Thank you for your comments.

    Mike
    MSDN is your friend.

    http://msdn2.microsoft.com/en-us/lib...az(VS.80).aspx

  8. #8
    Join Date
    May 2002
    Posts
    1,798

    Re: How do you build a query in MSAccess programatically ?

    GCDEF -

    Thank you. Good reference. I should have looked there first.

    Mike
    mpliam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured