Select qry works - delete qry fails

  • Permalink
  • submit to reddit
  • Email
  • Follow


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

0
Reply ms3400 (28) 2/25/2004 3:20:32 AM

See related articles to this posting

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
>


0
Reply bradley9026 (125) 2/25/2004 5:10:26 AM


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.

0
Reply ms3400 (28) 2/25/2004 6:36:18 PM

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

0
Reply ms3400 (28) 2/25/2004 8:16:28 PM

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

0
Reply ms3400 (28) 2/25/2004 11:28:39 PM

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

0
Reply ms3400 (28) 2/25/2004 11:29:12 PM
comp.databases.ms-access 42156 articles. 10 followers. Post

5 Replies
61 Views

Similar Articles

[PageSpeed] 19


  • Permalink
  • submit to reddit
  • Email
  • Follow


Reply:

Similar Artilces:

SELECT works but UPDATE fails. ?
This statement fails update ded_temp a set a.balance = (select sum(b.ln_amt) from ded_temp b where a.cust_no = b.cust_no and a.ded_type_cd = b.ded_type_cd and a.chk_no = b.chk_no group by cust_no, ded_type_cd, chk_no) With this error: Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'a'. But this statement: select * from ded_temp a where a.balance = (select sum(b.ln_amt) from ded_temp b where a.cust_no = b.cust_no ...

Select qry dialog box
I have a select qry that prompts for an employees name when you open the qry. Is there a way to place a drop down in the dialog box that pops up requesting the name, so you can select the name from the drop down list Thanks in advance for all your help To do what you are asking, you need to create a form with the drop down information, then have the query refer to the value of the combo box on the form for its criteria instead of prompting you directly. -- Wayne Morgan "Bob McHale" <rmchale786@aol.com> wrote in message news:55d90fcc.0310021310.5402102c@posting.google.com...

select qry
Hi all, when we give ' select * from emp;' it gives an output. can you get the same output by putting some value in statement below select * from ********** where table_name='EMP'; Thanks, shij No. sim Hi, What exactly are you trying to achieve? You can say SELECT * from all_tables where table_name = 'EMP', but this wil give you the meta-data. If you say Select * from EMP, this gives you the data(application specific) that resides IN the EMP table. Probably if you elaborate your need, we'll be able to help you better. Thanks...

Select qry from linked SQL table in combobox, locks update.
Hello everyone This is my first post here. I'm working on a project at work and a new to db programming. I have a very specific issue I'm hoping you can help me with.. 1) --- Select query in combo box rowsource (from linked table) -- SELECT dbo_LOCLIST.LOCCODE, dbo_DLCOMMENTS.COMMENTS dbo_LOCLIST.DESCR, dbo_LOCLIST.INVADD1 FROM dbo_DLCOMMENTS LEFT JOI dbo_LOCLIST ON dbo_DLCOMMENTS.LOCCODE = dbo_LOCLIST.LOCCODE ORDER B dbo_LOCLIST.LOCCODE 2) --- Code to update table (based on a click event command button -- Dim mylw As Databas Set mylw = OpenDatabase("LWDB...

session_start() intermittently fails on the same page, then works, then works, then fails..
I am using session_start() on index.php and for some reason sometimes it'll fail. No warnings, no errors, no notices, not even after prepending error_reporting(E_ALL) and ini_set('display_errors', TRUE) before session_start() do I see anything, it just plain dies. I used LiveHTTPHeaders to verify that I am getting a 200 OK response, thus everything seems OK except that it's dying on session_start(). Sometimes. I'll go and refresh the page and then POOF everything is just fine, the page delivers. I'll refresh a few more times to test and then after 2 - 3 times it fa...

Sometimes "ls" works while "ls -v" fails; Sometimes "ls -v" works while "ls" fails; Sometimes both fail; Why???
Hi all, I am trying to port linux kernel 2.4.20 to a new powerpc Book E processor/chip. I used the 16550A UART driver (in drivers/char/serial.c) with IRQ = 0 (no interrupt). I have tried to use different values for rs_strobe_time and the kernel prints out messages correctly. My problem is that when I tried to do "ls" or "export" in user mode, sometimes strange characters (or incorrect content) print out. Anyone has any idea? I have been struggling with this problem for over 2 months. Here is the screen dump. This one showing a case where "ls -l" ...

Nodes Selected property fails to visually select
I set a Node's selected property to True and also set the Ensure Visible property to True , yet, when I see the Node in the TreeView the Node is not visually selected, i.e. there is no focus rectangle around it nor is its background highlighted as it would if I manually clicked the Node. Why is this? How can I force the Node to be visually selected as well when I set the .Selected property to True? I set the .Selected property like so: Me.trv.Nodes.Item(Me.cmdFields(iIndex%).Tag).Selected = True Me.trv.Nodes.Item(Me.cmdFields(iIndex%).Tag).EnsureVisible A check of the debugger sh...

"Make" works at home, fails at work.
I'm having a problem with make at work. I installed djgpp on my computer at work, trying to duplicate my home installation. I also copied over my personal libraries, source code, makefiles, etc. But when I try to "make" anything at work I get error "-1", except in the case of an empty rule. Even a simple makefile like this bombs out: blat: [tab]@echo splat When I type "make", what it SHOULD do is just print "splat". What it does instead is this: make.exe: *** [blat] Error -1 When I use an "empty" rule it works just fine: Makefile: ...

builder.node('select'..) selected not working solution
FYI, with something like this: hotelement = "hotelement"+hotcount; hotspot = Builder.node('div', { id:'hotspot'+hotcount, className:'hotspot', style:''+divstyle }, [ Builder.node('select', {id:hotelement, onChange:hotspotlink}, [ Builder.node('option',{value:'textfield', selected:''},"textfield"), Builder.node('option',{value:'textarea'},"textarea"), Builder.node('option',{value:'dropdown'},"dropdown"), Builder.node('...

SELECT not working???
Hi everyone, I have a table with 4 columns in it, ID,Category,Name,Keywords I am trying to select the entries that have keywords that are submitted from a form. However, it does not always find the entry. For example if I select 'agriculture' from the form, I get a blank result when I know that there is an entry for 'agriculture'..... I am using this code $query = sprintf ( "SELECT Category,Name FROM valley WHERE Keywords LIKE '%$search%' ORDER BY name LIMIT %d,%d", $start -...