By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,110 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Select qry works - delete qry fails

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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" <ms@nospam.comcast.net> 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

Nov 12 '05 #2

P: n/a
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.


Nov 12 '05 #3

P: n/a
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.



Nov 12 '05 #4

P: n/a
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.



Nov 12 '05 #5

P: n/a
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.



Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.