Board index » Visual Studio » Flushing Deletes in a CRecordSet?

Visual Studio380
Hi all,



I'm using CRecordSets to do updates on a MS Access database. There are 2

tables in the DB with one of the columns of table 1 being a foreign key

to table 2 (this is enforced by MS Access by specifying the

Relationships of the tables).

When I do:

- table1 delete all records with column 1 value ID x

- table2 delete record with primary key ID x

I get an CDBException (originating in MS Access) stating that I can't

delete the record in table 2 because table 1 still has links to that

record. If I just put a breakpoint after the deleting of the records in

table1 and then continue running I don't get the error.

So it seems that MS Access doesn't process my Deletes in a synchonised

way. Can this be? Is there a way to enforce this synchronisation? (I

tried putting a BeginTrans and CommitTrans around the deleting of the

records in table1, but even CommitTrans doesn't seem to be synchronised.)



crimson13


-

crimson13 <rvblokkersREMOVE@REMOVEhotmail.com>wrote in message news:<#hxwM2SsEHA.820@TK2MSFTNGP12.phx.gbl>...

Why not get rid of Access' relationships (unless you need them for

some other purpose)?



Paul.

-

Paul S. Ganney wrote:

LOL, I think I will have to ;-)

It seemed like a 'natural' thing when the DB layout was created and it

would be good (it helps maintain DB consistency) if the deleting was

done in a synchronised way. But the only alternative I currently found

was a Sleep(500) after the deletes of the entries in table1, so that's

no solution at all.



crimson13



-

All,



NO - do not delete the relationships! Remember - it is called RDBMS for a

good reason - note the R and it position!



What we are seeing in this situation is the side effects of the Jet

lazy-writes/caching mechanism. Updates done in one connection may not get

written to the database for some time, hence other connection may have to

wait - consider the following links:

http://support.microsoft.com/default.aspx?scid" rel="nofollow" target="_blank">support.microsoft.com/default.aspx=kb;en-us;186278

http://support.microsoft.com/default.aspx?scid" rel="nofollow" target="_blank">support.microsoft.com/default.aspx=kb;en-us;240317



Crimson - make sure that you have only one conection to the database, and

then make sure that both of the CRecordSet objects are using that one and

only connection. Specifically, you must use an explicit CDatabase object

that is open and used for each of the updates.



Your assessment of the Sleep(500) is accurate - In general, if the program

works only after placing a few Sleep(500) calls here and there, then one

must consider the approach as a fundamental design flaw.



regards

Roy Fine





"Paul S. Ganney" <paul.ganney@hey.nhs.uk>wrote in message



-

Roy Fine wrote:

wow, thanks a lot for this reply



our current code is like:

CRecordSet1* rec1 = new CRecordSet1(NULL);

CRecordSet2* rec2 = new CRecordSet2(NULL);

rec1->Open();

rec2->Open();

So the flaw in the code would be the NULL parameter in the constructors

of the record sets. I'll try right away to create the CDatabase myself

(I think that is currently done by means of overwriting

GetDefaultConnect in our CRecordSet derived classes) and use that in the

constructor. That should ensure the one unique connection to the db.

Again, thanks :-)



crimson13

-

crimson13 wrote:



yep, that did it

I now create a CDatabase and open it myself, then use this in the

constructor of the sets. Works to way it should :-)



crimson13

-