Error 3086

[Anon93]

I'm an IT student currently working on a database and I am required to perform a delete query. The database I am working on seems simple enough but the query simly wont run.

This is my SQL:

DELETE Payments.*, Sections.*, [Personal Information].*, [Personal Information].[ID Number]
FROM [Personal Information] INNER JOIN (Sections INNER JOIN Payments ON Sections.[ID Number] = Payments.[ID Number]) ON (Sections.[ID Number] = [Personal Information].[ID Number]) AND ([Personal Information].[ID Number] = Payments.[ID Number])
WHERE ((([Personal Information].[ID Number])=1001 Or ([Personal Information].[ID Number])=1002));

Can anyone help me with this?
~Kris

[Stewart Ross]

Hi, and Welcome to Bytes!

Unfortunately, Access is somewhat limited when it comes to DELETE queries. You cannot use a join in an Access delete query to delete rows across multiple tables the way you might expect.

As Access will only delete rows from a single table at a time, you will have to perform several delete queries one after the other to accomplish what you want to do - one each for tables Payments, Section, and Personal Information. If the tables are related 1-many you need to delete the many-side entries first before you try to delete the one-side entries.

There is an alternative, which is to have the cascade delete option set when defining the one-to-many relations for the tables involved. I would not recommend this approach, however; it is rather dangerous, because if you accidentally delete a one-side row by mistake all many-side rows for that entry will be deleted at the same time as a result of the cascaded delete.

-Stewart

[Anon93]

Hmm, I actually do have the cascade delete option set, however, my relationships are a one to one type. Although, I took your advice and it seemed to have worked out.

Thank you!
~Kris

[jspeta]

Your table requires a primary key in order to delete a row

[isladogs]

@jspeta
Welcome to the forum. In case you aren't aware this is an 11 year old thread.

Although it is certainly good practice to have a primary key field in each table, you can definitely delete records from an Access table using a query even if there isn't a PK field. Try it and you will see it works

However, you cannot use a query to delete records from a linked SQL Server table unless it has a PK field.

[jspeta]

@isladogs
--
Yes, I know it is an old thread. No one had a solution in this thread. If anyone comes now , they will know what to do

[isladogs]

Actually there was a solution in the thread...cascade delete.
But the fact remains that if its an Access table, no primary key is required to run an update query.

[NeoPa]

JSPeta:
Yes, I know it is an old thread. No one had a solution in this thread. If anyone comes now , they will know what to do

Well, there are two problems with that.

1. There was already a valid answer that is actually correct.
2. The solution you proposed is not correct, so only leads any readers away from a true understanding.

Feel free to dig up old threads, but do yourself a favour and consider carefully before posting something that may need to be corrected later by others.

While we accept that members come in at all levels of competence and they offer what they can according to that competence, we will feel obliged to correct misinformation when it's posted as we want people to come here and find reliable information & help. It hurts the reputation of the site when people post stuff that is inaccurate. Not to mention that it's not helpful for those reading it either.

[jspeta]

@NeoPa
I don't know your background, mine is working with MS Access since 1998 and I'm a DBA for SQL Server since 2000. My solutions fixes this problem . There a lot of "newbees" on the internet forums that are trying to get there. you have a good day

[twinnyfo]

jspeta,

My apologies that your experience on Bytes was not up to your standards. I'm not going to sling hash, here, but it seems a little arrogant to become a member of a forum and on the same day, might I say politely, "insult" one of the moderators (and a very well-respected one, at that) who has 10,000 times more posts than you do. I don't give my loyalties easily, but once I do, I will defend those loyalties. NeoPa has probably forgotten more about MS Access than I will ever know or understand, and he doesn't need me to defend him--but I will.

Back to the actual question, whether the record has a PK or not is immaterial. A delete query will not work in the manner the OP described. I've experienced the same behavior in Access with my heavily PK'ed database. My experience tells me that complex JOINs will not allow DELETE to function. That is the answer.

I hope you have a great day as well!

[NeoPa]

I have just had encouragement from the owner to calm things down in here a little. We have a new member who has possibly come in and ruffled some feathers. To them (JSPeta) I would just say please be aware of what you say and give some consideration to how it may be perceived. Enough on that.

To those others, myself included, let's remember that JSPeta is new here and possibly much of what they've said is a defensive reaction to the responses that were triggered, but clearly unexpected. A step back should help. I know that all of us here are old enough (and ugly enough) not to be too sensitive about what others say about us. If ordinary members were involved it might be different but all here can be considered to have skins of rhino.

JSPeta:
I don't know your background, mine is working with MS Access since 1998 and I'm a DBA for SQL Server since 2000.

Welcome to Bytes.com. As a new member here let me advise that a click on any member's icon/ID will take you to their profile & bio. You can find out about me there, as well as IslaDogs & TwinnyFo if you choose.

JSPeta:
My solutions fixes this problem.
(Your table requires a primary key in order to delete a row)

Unfortunately that is not the case. I'm not sure you paid a great deal of attention to earlier posts that explained this so I will post a picture where I created a brand new database, a table with a single field without any PK, and then proceeded to delete one of the records from that table with a simple delete query. This is something you can try for yourself very easily.

As moderators & experts here at Bytes.com we care deeply that we don't allow misinformation to be reflected in the solutions we provide. I'm confident you posted with every good will. I imagine you also have a great deal of experience using Access & SQL Server. In this case though, you were mistaken. I would suggest you accept that just as I've had to once or twice. I certainly hate it when it happens but there's no doubt I've been pulled up by other experts when I've got something wrong. I do what I can to keep that to a minimum ;-)

Perhaps if you continue to post you can show that as being a simple abberation and that you still have a lot to offer.