Records being deleted in a related table when not supposed to (1 Viewer)

[Laurad]

I cannot work out what is happening and hope someone might be able to shed some light and offer some advice.

To summarise, I am losing records in one table when I delete from another, i.e., I do want to delete records in one, but the related records in the other are also being deleted.

This was not happening until I did a recent up date.

I created a database in Access 2002 about 8 years ago and my client has been using it successfully ever since. I have occasionally made updates and enhancements. They have over the years upgraded and are now on Office 2010. I'm still using Access 2002.

I recently (3 weeks ago) added a new feature and installed the new code. The database is split, code and data.

The new feature was working well, but suddenly they were losing records and they cannot operate until I have it resolved. I spent 2 hours today in their office and could not work out why it was happening.

I have a straight forward client table with names and addresses and some other information. I have another table which links together Clients, Counsellors and Supervisors and works out a room allocation for Therapy sessions. Once the Therapy sessions have been completed, we need to delete the Client, Counsellor, Supervisor + room allocation, but we do not want to delete the Client record in the Client table. The User is offered the opportunity to either archive the client record or not archive it, that's all. The Archive procedure is simply to put a tick in the "Archive" field, not delete the record.

However, when the Client, Counsellor, Supervisor + room allocation is deleted, it is also removing the Client record.

I do have a relationship between Clients, Counsellor, Supervisor, but it doesn't have "enforce referential integrity", it is just a one-to-one relationship.

The odd thing is that when I delete the allocation here at home on my Access 2002 system, it does not delete the Client Record, but it does in their office, using exactly the same code and data.

The Allocation is on a sub form and the way they delete is by highlighting the Allocation and then clicking on the X - Delete button on the Access menu (the program is not very sophisticated, but has worked until now). I have some code in the "on delete" event, but even if I take out all the code and just allow the deletion with the usual Access message "you are about to delete 1 record... " I have seen that the related Client record gets deleted at the same time. it does not happen on my system, only in their office.

I'm really at a loss as to understand why it would behave differently at each location or how to remedy it.

I hope I have supplied all the necessary information.
Thanks
Laura

 

[GohDiamond]

Sounds like a cascaded delete in the relationship. Go to the Relationships editor and check for it on the client's version. Are they using Access 2010?
Something probably happened as a default during the conversion of the db to 2010.

Cheers
Goh

 

[Laurad]

Yes, it sounds like a Cascaded delete on my client's system, but the relationship in both is the same - I checked and nor have I altered it in any way.

Yes, they are using Access 2010 - but they have been for some time now (a few years) and the problem only arose after I did a recent modification to the database. I did not alter anything, to my knowledge, that would have caused this "cascade delete" and I can't seem to stop it. I just want to delete one record in one table and not the related record in the Client table.

I have none of the tick boxes ticked as per your example below. I didn't have them ticked before.. I didn't change the relationship.

 

[bob fitz]

Hi Laurad,
What happens if you install a new fe on the clients machine, created from the 2003 version you have working correctly at home.

 

[Laurad]

Well, I'm not exactly sure as I had copied the FE I had created here onto their server and then copied that down to two machines.

The odd thing is that someone reported the issue. It was tried on the other machine and it did not occur, but yesterday when I was there, I tested on the two machines and went through the process of trying to delete the Allocation on a copy of the BE (data file) and each and every time the Client record got deleted also.

I opened the tables and then stepped through the code and as soon as the on delete event came to the end, if that makes sense, I saw both tables delete at the same time, i.e., the Allocation and the Client record.

It's very difficult to resolve the problem here if it's not occurring and very difficult to work in their office because of all the activity going on there.

 

[burrina]

It's simple! Either the relationships is REALLY a Cascade or else your delete code is causing it. Can you post your delete code? Perhaps a delete query is being run?

 

[jdraw]

I agree with burrina. However, it would be helpful if we could see your tables and relationships and a brief description (in plain English) of what the "process involved" is supposed to do.

Normally, a cascade delete is intentional -- used to ensure child records are deleted when a parent is deleted (no orphans). Sounds to me you may have a "Delete" operation in the revised code. Note: Sounds like \, but that's just a guess until we hear more from you.

 

[RainLover]

I do have a relationship between Clients, Counsellor, Supervisor, but it doesn't have "enforce referential integrity", it is just a one-to-one relationship.

This is from the opening post.

What it says is that this is a One to One Relationship.

There is little to no difference between this type of structure and a Single table.

I feel that you are confused and not suppling us the correct information.

A new database with just the Tables in question and the code would help.

PS What happens if you manually delete a record in a Table. Do the matching records in the other table delete.

 

[Laurad]

I honestly don't mean to confuse anyone and did my best to try and describe everything as well as I could and as I understood. I'm not an Ace programmer and am always learning.

Originally I took on this Access Database which had been written by someone else. It might have been better and easier in the long run to have rewritten it from scratch, but they only needed some enhancements and additions at the time and of course it was driven by cost. I have modified it several times over the years and in the meantime they have upgraded their version of Office, sometimes without letting me know, so I have gone in to make a modification and found the software was running on a later version.

I cannot check until Monday morning now when the office is once again open, but I have spent most of today trying to eliminate possible causes, without actually being able to replicate the issue of the Client Record being deleted when I delete the Room/Counsellor Allocation. It works as it should, on my computer.

I also ran it on a new computer I have with Access 2013 (so new I have not migrated over to it yet) and encountered a few issues which I remedied.

One of those was that I found a query for the sub form, Counsellor/Client Allocations to the Main Form of Counsellor names. In this query two of the field names/columns had changed in some instances to "Expr1: " and the newer Office 2013 generated a message saying there was a conflict or duplicate in the field name. I renamed the fields, i.e., instead of Expr1, I put CounsellorFullName. I checked my archived copies of the Database going back over the years and it showed the queries with the correct headings, so I do not understand how they could have changed, nor why it works on my machine but not theirs. I think they are on Windows 7 as I am also.

You asked "PS What happens if you manually delete a record in a Table. Do the matching records in the other table delete." As I am not onsite, I can't test this and didn't do so yesterday.

I have continually tested the database on both the 2002 and 2013 systems here and records are not being deleted in the Client table when I delete the Allocation. All I can do now is go in on Monday morning and "fix" the query which I think might have been causing a conflict and hope that that was the reason.

As far as relationships go, this is what it looks like. Maybe I am not understanding something. Thanks for your patience.

 

[RainLover]

You have not enforced Referential integrity in your relationships.

Do so now.

Is this a compiled version. eg Not MDB but MDE. Someone may have changed things on your Database.

Reinstall the Original after testing it.

 

[Laurad]

The files are .MDB files. I wish I knew how to make a compiled version, but I've never understood it or what I need in order to do it. I used to program in Clipper and Delphi and my databases were always compiled.

It is of course possible that someone changed the code.

I'll know more on Monday. I've tried to think of all the ways of possibly getting around this, for example, opening a new blank Database and importing the code and tables, of recreating some of the forms and queries, but it all takes time.

Thanks again for help and patience.

 

[burrina]

You can always make a .mde for the front end and or add code to keep prying eyes out of the db.

 

[RainLover]

You say the files are .mdb This means that you are running Access 2003 while they are running 2007 or later.

It would appear that you have lost control.

My suggestion is to find your Master Copy of the front end. Compile that into an mde file and distribute that.

While they can gain entry into your database you have no control. You also need to hide the Database window and disable the By Pass Key.

I am assuming that once you find the Master then things will run correctly, or should I say Run as you designed them to do.

 

[Laurad]

I think I mentioned that the Access file was already created and in the possession of the client when I first started to work on it. I didn't create it from scratch. At the time they had a program that they were happy with, but which needed additions and modifications. I took this on and have since done a few more modifications and updates over the years.

Yes, it would have been Access 2002 at the time, I think. In their office they have since upgraded to 2013 (I am still using 2002) which is how this issue came to light. The last modification I did was about three years ago. I never really did have control from the beginning so to do so now would be difficult and time consuming and they are not paying me for my time other than to make these recent modifications.

Every time I have made a modification I have kept and archived previous versions. I would not like to go back to the last update 3 years ago even, as I have done a lot of work recently, for which I have been paid. It's just this new "bug" which is now taking my time and which may have been in the system for a long time, but I am at a loss as to know how and why it has emerged only now when they have been using that side of the software for 10 years or so.

They will not be willing to pay me to do any extra work at this time, which is why I am anxious to resolve this issue, invoice them and then let them know that in future, any modifications could result in glitches and bugs since I did not originally write the software and since they do updates in their operating system and in Access from time to time.

I didn't really understand what you meant by compiling to .mde file. Do I have the means to do that with Access 2013?

I will advise the outcome on Monday and am hoping this particular issue will be resolved quickly.

Again, thanks for your time and patience.

 

[RainLover]

I have read your note and do acknowledge that you are on the correct track.

This is a link to an acticle that appears in one of my favourite technical books.

http://www.dummies.com/how-to/content/securing-your-access-2003-database-as-an-mde-file.html

This will help with compiling.

I am also in the middle of writing something on installing a new version and a Disaster Recovery Plan. It goes a bit off the track but hopefully of some help to you.

 

[RainLover]

Disaster Recovery Plan
Last time you worked on the database I would assume you had everything working correctly.
One would think that everyone received a locked down version of the front end installed on their machine.
The next thing that you should have done was to record what you did do. This is called a change management document. This document should record all requests for changes and by whom. The effect of this change, who approved the change, who tested it and so on. This should be attached to the previous changes that were made and kept in a safe place like a CD.
A copy of the Back end and the UNLOCKED front end should also be put on that CD.
Now both you and the Client have the same database at the same point in time. This should be part of your kit that you use when working on a client's database.
Well that is something like how it is supposed happen. If this was the case you should or even the client should be able to reinstall the original if and when something went wrong.

Who wrote the original database, who changed it and who is going to pay for it has nothing to do with this Forum. If they wanted me to work for nothing to fix something that they did not pay to have done properly in the first place. Well it just is not going to happen. If I were to do this then the next thing is that they would be expecting more at the special price of nothing and would want it yesterday.
On the other hand there are things you need to do and learn.
You need to know the difference between an mdb file and an mde. How to keep back up copies. How to install a new version for everyone.
I would assume you are working a split system where everyone has their own copy of the front end installed on their own machine.

 

[Laurad]

Thank you so much and apologies for my lack of skills and knowledge. Taking on someone else's database is a mean and difficult task. Had I written it myself to begin with I would have felt more responsible, but they are a charity and lacking in funds.

I will read what you linked to and report back on Monday when I hope (so very much) that I will find the root of the problem and resolve it.

I saw your second post after I replied.

Thank you for taking the time to write the "Disaster Recovery Plan".

Yes, last time I worked on the database, some three years ago perhaps, everything was working and yes, I kept multiple copies myself, but didn't think to give them a copy also (good lesson and I will do so in future). I do still have dated copies of both FE and BE.

Yes, I always fully itemised changes requested and changes implemented, in detail.

They are a charity on a shoestring budget.

No, we do not have a "locked down" verions of the FE.

Yes, I need to know the difference of mdb and mde files. One one of my computers a few years ago I did work out how to create an .mde file, but something went wrong and I was never able to remedy it or to find any help. I'm still wondering if my new Access 2013 will give me that ability - I have yet to discover.

As always.. it goes without saying.. thank you.

Laura

 

[RainLover]

Thank you so much and apologies for my lack of skills and knowledge. Taking on someone else's database is a mean and difficult task. Had I written it myself to begin with I would have felt more responsible, but they are a charity and lacking in funds.

Please do not apologize. Helping people like you helps me so we both win.

I will read what you linked to and report back on Monday when I hope (so very much) that I will find the root of the problem and resolve it.
I saw your second post after I replied.
Thank you for taking the time to write the "Disaster Recovery Plan".

This is a massive subject and applies to everything in a business. What would happen if the server crashed, if there was a fire on the production floor, several key executives were killed in a plane crash etc.

A short story.
I wrote a Database for a Medium sized company. This showed faults in the Accounts department so they wanted me out of there. They called in an Auditing firm who noticed that throughout the whole organisation that I was the only one using Change management and Disaster recovery.
I was then asked to consult to other department/divisions. I made a lot of money.

Yes, last time I worked on the database, some three years ago perhaps, everything was working and yes, I kept multiple copies myself, but didn't think to give them a copy also (good lesson and I will do so in future). I do still have dated copies of both FE and BE.
Yes, I always fully itemised changes requested and changes implemented, in detail.

One thing you can reply on is that they, the client, will not only NOT look at what you gave them but they will most likely lose it. Who is in the box seat now. You are. You can blame them for everything because they cannot prove otherwise. Not as though you would do this or need to do this but it does give you a position of authority instead of being the grubby little contractor who has no idea what is going on.

They are a charity on a shoestring budget.

Charities are experts at getting something for nothing. This is what they do and it is currently what they are doing to you. Don't let them. Insist on getting paid for what you are worth. If not be polite, and let them know that you have to do work that you get paid for in order to be able to feed the dog and cat. Then walk, because you actually end up better off financially by not having the cost of doing something for them for nothing.

No, we do not have a "locked down" versions of the FE.

Yes, I need to know the difference of mdb and mde files. One of my computers a few years ago I did work out how to create an .mde file, but something went wrong and I was never able to remedy it or to find any help. I'm still wondering if my new Access 2013 will give me that ability - I have yet to discover.

This one is your fault because a simple Google search would have helped a long time ago.

As always.. it goes without saying.. thank you.
Laura

I have carried on, have I not. It is just one of those Lazy Sunday mornings. We have just started our winter. The sun is shining and the temperature is such that only light clothing is required.

We got off the track but you did need to know some of this stuff or you would be on a hiding to nowhere.

No doubt I will write again.

TTFN

One final thing. Why don't you complete your profile a little better. Knowing where you are and your Regional Settings does help. Knowing when you will be awake (Your time zone) is also a good thing.

 

[Laurad]

Please do not apologize. Helping people like you helps me so we both win.

One final thing. Why don't you complete your profile a little better. Knowing where you are and your Regional Settings does help. Knowing when you will be awake (Your time zone) is also a good thing.

Firstly thank you! In my own way I also help where and when I can, so I do understand.

I know only too well that my ignorance can often frustrate. I wouldn't be where I am now without help and support from individuals along the way, so it's always appreciated.

Secondly - my profile does show where I am - Wimbledon, London, England. My tonight is your today, RainLover.

Hopefully I will have some news tomorrow. I won't sleep well tonight. I just want to resolve this current issue and move on. They won't be interested in paying me for the time it will take me to lock the code down and I haven't the time to do it without being paid. It's a charity whom I have helped a lot in the past and have not begrudged it, but I fully take on board your advice. Hopefully this current issue will be resolved and in future I will be clear about the implications of further modifications. Thank you and I will be back to update.
Laura

 

[Laurad]

I did manage to resolve the issue finally yesterday in their office. It took a while as I had to work on a copy of their BE and go through the process again of stepping through the coding, causing the issue to occur again, then finding the bit of code where the variable name had changed to Expr1:. There seems to have been a duplicate variable name, but I am still not sure how or why it caused records to be deleted in the Client table.

I am grateful for all your help. Sometimes explaining things here, seeing possible solutions and discussion, helps one to work out what the problem might be, even if it's just by a process of elimination.

In future if they ever want me to change anything on the database again, I will have to make sure there is a big contingency plan for things going wrong and the time taken to resolve. Basically the program needs to be rewritten from the bottom up and as you suggested, locked down. I'm not sure if they would want to go down that route, but thank you again.

Laura
UK