Select qry works - delete qry fails

[ms]

Why does this select query return the correct records but when I make it a
delete query I get a msgbox with "Could not delete from specified
tables".

SELECT BMIDLog.*
FROM stageBMIDLog INNER JOIN BMIDLog ON (BMIDLog.BattID = stageBMIDLog.BattID)
AND (BMIDLog.VehicleID = stageBMIDLog.VehicleID) AND (BMIDLog.TotalChgAhs =
stageBMIDLog.TotalChgAhs)

DELETE BMIDLog.*
FROM stageBMIDLog INNER JOIN BMIDLog ON (BMIDLog.BattID = stageBMIDLog.BattID)
AND (BMIDLog.VehicleID = stageBMIDLog.VehicleID) AND (BMIDLog.TotalChgAhs =
stageBMIDLog.TotalChgAhs)

Thanks

[Bradley]

I suspect it is because you've joined table together... I don't think you
can't delete records this way.

You should delete from one table and set up the relationships so the delete
will cascade to any associated tables.
--
Bradley
Software Developer www.hrsystems.com.au
A Christian Response www.pastornet.net.au/response

"ms" <[e-mail]> wrote in message
news:9t********************@comcast.com...

Why does this select query return the correct records but when I make it a
delete query I get a msgbox with "Could not delete from specified
tables".

SELECT BMIDLog.*
FROM stageBMIDLog INNER JOIN BMIDLog ON (BMIDLog.BattID = stageBMIDLog.BattID) AND (BMIDLog.VehicleID = stageBMIDLog.VehicleID) AND (BMIDLog.TotalChgAhs = stageBMIDLog.TotalChgAhs)

DELETE BMIDLog.*
FROM stageBMIDLog INNER JOIN BMIDLog ON (BMIDLog.BattID = stageBMIDLog.BattID) AND (BMIDLog.VehicleID = stageBMIDLog.VehicleID) AND (BMIDLog.TotalChgAhs = stageBMIDLog.TotalChgAhs)

Thanks

[ms]

Thanks Bradley, but won't confuses me is, why does this Delete query work then?
What makes a join query in a delete statement work?

DELETE stageChargeLog.*
FROM ChargeLog INNER JOIN stageChargeLog ON
([ChargeLog].[STTIME]=[stageChargeLog].[STTIME]) AND
([ChargeLog].[STDATE]=[stageChargeLog].[STDATE]) AND
([ChargeLog].[BattID]=[stageChargeLog].[BattID]);
Bradley wrote:

I suspect it is because you've joined table together... I don't think you
can't delete records this way.

You should delete from one table and set up the relationships so the delete
will cascade to any associated tables.

[ms]

I also followed Access Help (Delete records from one table or tables in a
one-to-one (and one-to-many) relationship) using the query design window and
still it works as a select but fails as a Delete. No information, no help,
nothing. Just says can't delete.
This is what the query design query is in SQL:

DELETE stageBMIDLog.*
FROM stageBMIDLog, TMPstageBMIDLog
WHERE TMPstageBMIDLog.TotalChgAhs=[stageBMIDLog].[TotalChgAhs] AND
TMPstageBMIDLog.VehicleID=[stageBMIDLog].[vehicleid] AND
TMPstageBMIDLog.BattID=[stageBMIDLog].[battid];
ms wrote:

Thanks Bradley, but won't confuses me is, why does this Delete query
work then? What makes a join query in a delete statement work?

DELETE stageChargeLog.*
FROM ChargeLog INNER JOIN stageChargeLog ON
([ChargeLog].[STTIME]=[stageChargeLog].[STTIME]) AND
([ChargeLog].[STDATE]=[stageChargeLog].[STDATE]) AND
([ChargeLog].[BattID]=[stageChargeLog].[BattID]);
Bradley wrote:

I suspect it is because you've joined table together... I don't think you
can't delete records this way.

You should delete from one table and set up the relationships so the
delete
will cascade to any associated tables.

[ms]

I figured this out - enough to get my job done:
The delete query with a JOIN works when using .Execute from code vs. from the
object window.
Don't know why though.
Thanks,
mark

ms wrote:

I also followed Access Help (Delete records from one table or tables in
a one-to-one (and one-to-many) relationship) using the query design
window and still it works as a select but fails as a Delete. No
information, no help, nothing. Just says can't delete.
This is what the query design query is in SQL:

DELETE stageBMIDLog.*
FROM stageBMIDLog, TMPstageBMIDLog
WHERE TMPstageBMIDLog.TotalChgAhs=[stageBMIDLog].[TotalChgAhs] AND
TMPstageBMIDLog.VehicleID=[stageBMIDLog].[vehicleid] AND
TMPstageBMIDLog.BattID=[stageBMIDLog].[battid];
ms wrote:

Thanks Bradley, but won't confuses me is, why does this Delete query
work then? What makes a join query in a delete statement work?

DELETE stageChargeLog.*
FROM ChargeLog INNER JOIN stageChargeLog ON
([ChargeLog].[STTIME]=[stageChargeLog].[STTIME]) AND
([ChargeLog].[STDATE]=[stageChargeLog].[STDATE]) AND
([ChargeLog].[BattID]=[stageChargeLog].[BattID]);
Bradley wrote:

I suspect it is because you've joined table together... I don't think
you
can't delete records this way.

You should delete from one table and set up the relationships so the
delete
will cascade to any associated tables.

[ms]

I figured this out - enough to get my job done:
The delete query with a JOIN works when using .Execute from code vs. from the
object window.
Don't know why though.
Thanks,
mark

ms wrote:

I also followed Access Help (Delete records from one table or tables in
a one-to-one (and one-to-many) relationship) using the query design
window and still it works as a select but fails as a Delete. No
information, no help, nothing. Just says can't delete.
This is what the query design query is in SQL:

DELETE stageBMIDLog.*
FROM stageBMIDLog, TMPstageBMIDLog
WHERE TMPstageBMIDLog.TotalChgAhs=[stageBMIDLog].[TotalChgAhs] AND
TMPstageBMIDLog.VehicleID=[stageBMIDLog].[vehicleid] AND
TMPstageBMIDLog.BattID=[stageBMIDLog].[battid];
ms wrote:

Thanks Bradley, but won't confuses me is, why does this Delete query
work then? What makes a join query in a delete statement work?

DELETE stageChargeLog.*
FROM ChargeLog INNER JOIN stageChargeLog ON
([ChargeLog].[STTIME]=[stageChargeLog].[STTIME]) AND
([ChargeLog].[STDATE]=[stageChargeLog].[STDATE]) AND
([ChargeLog].[BattID]=[stageChargeLog].[BattID]);
Bradley wrote:

I suspect it is because you've joined table together... I don't think
you
can't delete records this way.

You should delete from one table and set up the relationships so the
delete
will cascade to any associated tables.