Problem using "Microsoft.Jet.OLEDB.4.0" to acces .mdb-files from C++

[agrafov]

When I try to access "MS Access" .mdb database using "native" Microsoft.Jet.OLEDB.4.0 provider I'm experiencing a problem with deleting of multiple records from previously retrieved recordset.
Here is an example:
// first, I open database in very usual way using ATL-based classes (mostly generated by OLEDB consumer-template wizard in VS)
CDataSource db;
CDBPropSet dbinit(DBPROPSET_DBINIT);
...................................
dbinit.AddProperty(DBPROP_AUTH_PASSWORD, OLESTR("&quot);
dbinit.AddProperty(DBPROP_AUTH_USERID, OLESTR("Admin&quot);
dbinit.AddProperty(DBPROP_INIT_DATASOURCE, (LPOLESTR)szDBName);
dbinit.AddProperty(DBPROP_INIT_MODE, (long)16);
hr = db.Open(_T("Microsoft.Jet.OLEDB.4.0&quot, &dbinit);
if( SUCCEEDED(hr) )
{
hr = m_session.Open( db );
return hr;
}

// then I use generated accessor class
(something like class CProducts : public CCommand<CAccessor<CProductsAccessor> >)
to open a rowset as a result of some SQL statement:
CProducts rs;
rs.Open();

// When I iterate through received recordset, processing data and deleting records using some criteria (or just all of them) like:
while( S_OK == rs.MoveNext() )
{
..................... // do something here
rs.Delete();
}

I receive &quot;DB_S_ENDOFROWSET&quot; just right after the second deletion, even though there is a lot more records in the recordset - without such deletion inside a loop I can successfully traverse entire recordset (all records) with no problems at all.

When I open DataSource using &quot;MDASQL&quot; as a provider instead of &quot;Microsoft.Jet.OLEDB.4.0&quot; everything works just fine (as it is supposed to work).

Initially blamed msjetoledb40.dll module, but installing the latest MSJETOLEDB40 Service Pack SP8 did not solve the problem.

Did I do something wrong, or somebody else had such problem and knew good workaround?

Best regards,
Andrey

 

[LuckyLuke]

Have you tried calling rs.MoveFirst() before the loop?

 

[agrafov]

It is not relevant to call MoveFirst() before the loop because newly retrieved recordset &quot;is automatically positioned to the beginning&quot;, so the very first MoveNext() is equivalent to MoveFirst() in this case - it's clearly said in documentation and actually is a result of real test. And anyway, it doesn't change the behaviour at all. But the real problem is my example in the original post DOESN'T WORK ONLY if I use &quot;Microsoft.Jet.OLEDB.4.0&quot; provider - in case of &quot;MDASQL&quot; it works fine!!!
And, of course, if I remove rs.Delete() from the loop, it successfully goes through all records in a recordset (even if I use &quot;Microsoft.Jet.OLEDB.4.0&quot; provider).

Andrey

 

[LuckyLuke]

Sorry, I didn't read the part that if you remove rs.Delete() it works fine. I just recreated you sample and you are right, it goes wrong here as well after the second delete. I'm sorry, but I have no idea why this is going wrong.

What exactly are you trying to achieve? Are you simply trying to remove all records or something like that?

This seems to work:

while(S_OK == rs.Delete())
{
rs.MoveFirst();
}

I have to admit though that I never work with MDB files nor with the JET engine, so I don't know if this behaviour is 'wanted' ;-)

 

[agrafov]

>>What exactly are you trying to achieve?
Well, I'm trying to do some processing of records based on one criteria and remove them based on another criteria (different, but related in some way to the first one - kind of processing/archiving). And I do not want to break it into 2 separate pieces - processing and SQL-DELETE statements for some other design reason.

>>I have to admit though that I never work with MDB files nor with the JET engine, so I don't know if this behaviour is 'wanted'
The behavior should be the same for ANY type of file and ANY provider, because this behaviour of Delete() and MoveNext() is described exactly this way in the description of low level OLEDB interfaces(IRowset and IRowsetChange).