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

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:9t6dnWr1p8sPiaHdRVn-ug@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
>

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.

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.
> 
> 

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.
>>
>>
>>
> 

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.
>>
>>
>>
>