Cartesian join versus DAO?

[Paolo]

Hi,


Not sure if DAO is the answer, but I have done it using SQL strictly,
and was wondering if it would make more sense to do it in DAO and if
so, could someone give me some pointers.

Basically, I create a table of one record from a parent table using an
action query. Then I update the parent table using a cartesian join.
That is:

UPDATE tblParent, tblOnerecord
SET tblParent.Field = tblParent.Field+1
WHERE tblParent.Field >= tblOnerecord.Field

Then I update the one record table before finally inserting it back to
the parent table. I then drop the one record table. So now the parent
table is one record longer.

I got a response from someone that creating a table and then dropping
it is not a great idea. So I thought maybe there was a better solution
to this. This is kind of a repost as it has been bugging me.

Thanks,
Paolo

(e-mail address removed)

 

[John Nurick]

Hi Paolo,

I don't quite understand what you're doing, but suspect it would be
possible to replace tblOneRecord and the Cartesian join with a subquery
on tblParent. Maybe posting to microsoft.public.access.queries would get
you an answer along those lines (but show them your make-table query as
well as the update query).

Otherwise: if you've got an effective SQL solution there's seldom (IMHO)
anything to gain by replacing it with recordset operations, whether DAO
or ADO. But instead of creating and dropping the one-record table, why
not just update its record as required?

 

[Paolo]

Hi John,

Thanks for the comments. I wish I could be clearer about what I am
doing. Maybe Ill try posting it on microsoft.public.access.queries
with the make table query. Meantime, let me define a little bit better
what I am doing. I need to do three things:

1) Change all of the records on tblParent which which have a field
value greater than a field value in tblOneRecord

2) Update another field value in TblOneRecord

3) Append tblParent with TblOneRecord

So basically I am changing one field in TblOneRecord and another field
in tblParent

Is that clearer? If you think I can do it another way in SQL, then I
will give the subquery idea some thought.

Thanks,

Paolo

(e-mail address removed)

 

[John Nurick]

Hi Paolo,

I don't think I can add anything useful without knowing more of the
background. But I'm certain that you don't need to drop and re-create
tblOneRecord.