Recordset.Filter

[smo7k]

I'm using Direct DAO to access Access 2007 database like
http://msdn.microsoft.com/en-us/library/cc811599.aspx

However, when I use the Filter property on a recordset, it does not work.
Here is a sample of the code

Shortcut table has a column field name "bar_number",the "bar_number" is a
Number Type. This table contains 5 rows, and 2 rows has "bar_number" = 1.

DAO::DatabasePtr pDbPtr = NULL;
// Open the database
pDbPtr = pEngine->OpenDatabase(bstrConnect);
if (pDbPtr)
{
// Prepare SQL query.
_bstr_t query = "SELECT * FROM Shortcut";

// Excecute the query and create a record set
DAO::RecordsetPtr pRS = NULL;

// this recordset will contain 5 rows, only 2 rows have
bar_number = 1
pRS = pDbPtr->OpenRecordset(query,
_variant_(DAO::dbOpenDynaset));

// try to apply the filter, we should only have 2 rows that
have bar_number= 1
_bstr_t filter = "bar_number = 1";
pRS->Filter = filter;

int rowCount = 0;
while (!pRS->AdoNSEOF)
{


cout<<bstr_t(pFields->GetItem("bar_number")->GetValue()) << endl;

pRS->MoveNext();
rowCount++;
}

cout<< ": Total Row Count: "<<rowCount<<endl; // shows 5 <-
wrong

Do I need to do anything after I apply the filter? I tried to call Requery
after, but it doesn't work. When I changed the query to

_bstr_t query = "SELECT * FROM Shortcut where bar_number =1";

and don't use Filter, and it works. But I do want to know why the Filter
property doesn't work.

Any ideas?

Thanks.

[Paul S. Ganney]

You do need to requery the recordset - the filter is used as part of
the SELECT statement so is used in forming the recordset, not in
processing it.

After
pRS->Filter = filter;

Put
pRS->Requery();

This should work.

Paul.

[smo7k]

I tried that and it didn't work.