-
February 26th, 2007, 04:19 PM
#1
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
-
February 26th, 2007, 04:27 PM
#2
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.
-
February 26th, 2007, 04:36 PM
#3
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.
-
February 26th, 2007, 04:46 PM
#4
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.
-
February 27th, 2007, 12:13 AM
#5
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
-
February 27th, 2007, 02:25 AM
#6
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
-
February 27th, 2007, 07:41 AM
#7
Re: How do you build a query in MSAccess programatically ?
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
-
February 27th, 2007, 11:10 AM
#8
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|