Cannot delete record (1 Viewer)

[Vassago]

I'm trying to run the following delete query:

DELETE Employee_Attendance.*
FROM Subordinate_List INNER JOIN Employee_Attendance ON Subordinate_List.PrimaryID = Employee_Attendance.PrimaryID;

When I open the query in datasheet view, it shows the correct record. When I try to run the delete query, it says "Couldn't delete from specified tables." No one is in the table and I have full access ot it. I can go into the table itself and delete the records just fine, why can't I delete them with this query?

Any suggestions?

Thanks!

Vassago

 

[Len Boorman]

Think the problem is that you have a join in the where clause. Think that a view becomes non updateable if there is a join in the source. Also think that this is not a 100% rule but applies in 95% of cases

len B

 

[iancampbellian]

delete solution

create a new field in the table with the records you want to delete...call it Delete and make it a yes/no type.

then run an update query on this table to make the records you want to delete equal yes (-1)

then delete the records with a delete field =-1

 

[Pat Hartman]

See if re-arranging the Where clause fixes the problem:

DELETE Employee_Attendance.*
FROM Employee_Attendance INNER JOIN Subordinate_List ON Subordinate_List.PrimaryID = Employee_Attendance.PrimaryID;

 

[Vassago]

Thanks for your replies!

Pat - I tried your sql that you posted and it didn't work. I still recieved the same error message. Then I tried getting rid of the inner join with the following sql:

DELETE Employee_Attendance.*, Employee_Attendance.PrimaryID
FROM Employee_Attendance, Subordinate_List
WHERE (((Employee_Attendance.PrimaryID) Like [Subordinate_List].[PrimaryID]));

This came up with an error message that read: "Operation must use an updatable query." HUH?

Ian - I haven't tried your suggestion yet I will try it as a last resort because right now I'm unable to add fields to the table because people are ALWAYS accessing the BE.

Is there a way to delete the current record you are viewing on a continuous form using code?

Thanks!

Vassago

 

[Jon K]

Try this:-

Delete *
FROM Employee_Attendance
WHERE PrimaryID in (Select PrimaryID from Subordinate_List);

 

[Pat Hartman]

This query worked for me. I don't see the difference:

DELETE [zzSwitchboard Items].*
FROM [zzSwitchboard Items] INNER JOIN [Switchboard Items] ON ([zzSwitchboard Items].ItemNumber = [Switchboard Items].ItemNumber) AND ([zzSwitchboard Items].SwitchboardID = [Switchboard Items].SwitchboardID);

Try rebuilding it from scratch. Add the table you want to delete from, add the other table. Join them. Select the asterisk from the table you want to delete from. Change the query type to delete.

 

[Vassago]

Thanks Pat!

I've done that countless times. I guess there's just no hope for me.

I'll have to try something new I suppose.

Thanks for the suggestions. I'm sure it's not a problem with the code or the query itself.

Vassago

 

[Vassago]

What about the continuous form question? Is there a way to delete a record on a continuous form if a button is clicked? Thanks!

Vassago

 

[Pat Hartman]

Put the button on the continuous form, NOT on the main form. Having the button on the main form is confusing for the user and he can easily delete the wrong subform record.

Did you try rebuilding the query from scratch? Is the problem that you have the records locked because you are deleting the record from the current form?

 

[iancampbellian]

delete using an update first

try doing an update query then a delete query as i suggested...it works....forget about inner joins etc etc...you'll tie yourself in knots.

 

[Pat Hartman]

iancampbellian, learning how to join related tables is imperative to good system design. If you don't know how to bring the data together into a single recordset, you will be reluctant to properly normalized your tables. Improperly designed tables result in excessive amounts of VBA code to overcome the defects as well as overly complicated queries. Not to mention the really bad things like bad data that may cause your company or client to make bad business decisions.

 

[Vassago]

The problem was definitely having two tables included in the delete query. When I removed the second table, I was able to come up with a reasonable compromise by comparing the same field to a text box on the form instead. Anyway, thanks for your help everyone!

 

[Pat Hartman]

But Vas, the query I posted had two tables and it worked. I just copied the Switchboard Items table to have something non-dangerous to work with. Did you ever try rebuilding the query as I suggested?

 

[iancampbellian]

agree with your comments re design....quite right to normalise. unfortuantely it can be a bit tricky to explain to new users in a forum like this.

sometimes quick fixes are good until you learn such concepts.

have you checked out my question re linking / workgroup files and security in the general section.

would appreciate your help.

 

[Pat Hartman]

I think you posted this message to the wrong thread Ian. If you want me or anyone else to look at something specific, please post a link. Don't make us search for it.

 

[iancampbellian]

my question

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=49373

pat here's the link.

i've used system.mdw and db1 for testing purposes.

hope to hear form you.

regards

 

[Vassago]

But Vas, the query I posted had two tables and it worked. I just copied the Switchboard Items table to have something non-dangerous to work with. Did you ever try rebuilding the query as I suggested?

I've done that countless times.

Indeed I did try rebuilding it many many times. I could not get it to work in any way. I don't know why either. Can you post your sample on here so I can see what worked for you? Maybe I can see if it works for me.

Ian - I do normalize, and I'm hardly a new user. I just find myself in tough spots with databases I didn't originally create. This one was kind of handed to me and I was told to "fix it." I would think the company would follow the "if it ain't broke, don't fix it" rule, but I guess not.

 

[Dave R]

I don't know if Vassago's problem was ever resolved but I have just come across the same thing and discovered it is to do with Access 2000 Jet engine 4. When you dsign a query in Access 2000 the "Unique Records" property defaults to "No". In the query design, go to View then Properties and set "Unique Records" to "Yes". The delete query should then work. For more info see:
http://support.microsoft.com/default.aspx?scid=kb;en-us;207761

 

[Len Boorman]

I have read all the posts on this thread and whilst offering no definitive solution would make the following observations.

Quote
Delete *
FROM Employee_Attendance
WHERE PrimaryID in (Select PrimaryID from Subordinate_List);

This works because it is a straightforward query that uses a sub query as the source of the Where clause. i.e there is no JOIN

some of the other examples appear to work but as I said in my original post having a JOIN generally screws up a delete query, not always though so it is disconcerting to find that sometimes it works and sometimes doesn't

The solution I generally use is the Update/Delete method.
Update a flag field in teh table where you actually wish to delete records and then run a delete query on that table based on the flag field value.

I am currently doing an Open University course on Relational Databases and this subject actually came up during a tutorial. The lecturer actually is a member of the International SQL Body (vcannot remember the full name) and he commented that this is one of the inconsistencies within SQL.

It appears that if the sql can positively identify the record to be deleted then it does otherwise it just bails out seemingly without reason.

Hope the above is of some interest

Len B