SallyBullard
asked on
Referential Integrity Problem
I have two tables, tblOrders and tblOrderDetails on which referential integrity is enforced. I have an orders form which an order detail subform. When the user chooses an order type, the code saves the record and then pulls up an additional form asking for more information based on the order type. This code has been used for 6 months without incident. Yesterday, I started getting a run-time error message saying that a record couldn't be located in tblOrders. When I hit debug, the problem appeared to be on the code which saved the record. On the theory that the file had been corrupted, I did a compact/repair on both the tables file and the front-end file. No luck. I created a new file for the tables, no luck. I have checked tblOrders and the required record is definitely in the table. I have no idea what to try next. Any ideas on your end?
ASKER
I am using Access 2000. Since I posted the question, I have discovered that the problem is not with the reference. I have a form and subform with the OrderID as the linking criteria. Previously, when a new item was added to the order detail subform, the order ID field was automatically added to the detail subform because of the link, I guess. For some reason that has stopped working. I have added code to the OnEnter event of the subform to put the order ID in the proper field on the subform and it works. What I find frustrating is figuring out what could cause this to suddenly occur? I don't think the file is corrupted, because the front-end portion of this program is on 10 computers in my company and they all started doing the same thing! One of the VERY frustrating things about Access, as much as I love it.
>> I have added code to the OnEnter event of the subform to put the order ID in the proper field on the subform and it works
Do yourself a favor and make a backup of your files now. This is not normal behaviour, and abnormal behaviour in Access is normally a big, flashing neon sign that says "I'm About To Corrupt And Lose All Your Data!!!" <g>. Also, the Enter event only occurs ONCE - when the user first leaves the main form and enters the subform, so if users add more than one OrderDetail, you could end up with other errors or, worse yet, orphaned records.
Do all users have their OWN copy of the frontend, all of which are connected to the SAME backend?
Do you have relationships setup on the backend tables IN the backend? If all users have their own copy of the frontend, and all users started having the same problem at the same time, you may have some issues with relationships (aka "constraints"). Access handles these internally, and you could have a pending corruption issue. Same drill as before - make a copy of your tables NOW ...
When you created a new file, did you import the tables to this new file, or use some other method? If you imported the tables, then Access will also import the constraints, and you may simply have moved the problem from one db to another. You may actually have to rebuild by hand (i.e. rebuild your table structure, recreate your relationships/indexes etc, then run Insert queries to move your data across).
Do yourself a favor and make a backup of your files now. This is not normal behaviour, and abnormal behaviour in Access is normally a big, flashing neon sign that says "I'm About To Corrupt And Lose All Your Data!!!" <g>. Also, the Enter event only occurs ONCE - when the user first leaves the main form and enters the subform, so if users add more than one OrderDetail, you could end up with other errors or, worse yet, orphaned records.
Do all users have their OWN copy of the frontend, all of which are connected to the SAME backend?
Do you have relationships setup on the backend tables IN the backend? If all users have their own copy of the frontend, and all users started having the same problem at the same time, you may have some issues with relationships (aka "constraints"). Access handles these internally, and you could have a pending corruption issue. Same drill as before - make a copy of your tables NOW ...
When you created a new file, did you import the tables to this new file, or use some other method? If you imported the tables, then Access will also import the constraints, and you may simply have moved the problem from one db to another. You may actually have to rebuild by hand (i.e. rebuild your table structure, recreate your relationships/indexes etc, then run Insert queries to move your data across).
ASKER
Oh, great! Yes, all users have their own copy of the frontend (the main file is on the server and then copied to each individual workstation). The main relationships are set up in the file with the backend tables. This is not the first weird problem I have had with Access. Two months ago when I was working on a major revision of the database, I started getting very strange errors in the Visual Basic Code. It would just stop recognizing fields that were definitely on the forms, it quit recognizing the Me. designation in the code, etc. In every case, if I created a new file and copied everything over the problem would go away. Then, all of a sudden, it just stopped giving me errors. I have felt for a while that there are major problems somewhere in my file or in Access, but am at a loss as to how to fix it. Starting over would be a major project, but I am beginning to feel that maybe that is my only option. I have told the staff to make a copy of the data tables every day (usually we do it once a week) so the most we will lose is one day's work. Are there any options other than starting again?
If the users copy the file to their workstation regularly (like every day, everytime they log in, etc), then I'd bet a day's pay your project is corrupt.
There is always the undocumented SaveAsText and LoadFromText features that will handle your non-table objects (forms, reports, etc). I use them somewhat regularly when developing, espeically when I think my VBA project may be corrupt. I export all objects, build a new, blank database, then use the LoadFromText routine to import all objects to my new, blank database. I have personalized routines to do this, but they wouldn't do you much good since they copy things to and from specific machines on my network (and do a bunch of other things as well). Notice, however, the "undocumented" wordage - MS doesn't support these, and they're really for use internally. Basically, they save the Text definition of your objects (matter of fact, after running SaveAsText, you can open the resulting file in Notepad and view this definition in plain text ... pretty neat, if you ask me).
Here's a link to a recent exchange ... includes a link in my comments to a website with the SaveAtText stuff on it:
https://www.experts-exchange.com/questions/20891441/Object-Corruption-w-in-MS-Access-2000-Database.html
There is always the undocumented SaveAsText and LoadFromText features that will handle your non-table objects (forms, reports, etc). I use them somewhat regularly when developing, espeically when I think my VBA project may be corrupt. I export all objects, build a new, blank database, then use the LoadFromText routine to import all objects to my new, blank database. I have personalized routines to do this, but they wouldn't do you much good since they copy things to and from specific machines on my network (and do a bunch of other things as well). Notice, however, the "undocumented" wordage - MS doesn't support these, and they're really for use internally. Basically, they save the Text definition of your objects (matter of fact, after running SaveAsText, you can open the resulting file in Notepad and view this definition in plain text ... pretty neat, if you ask me).
Here's a link to a recent exchange ... includes a link in my comments to a website with the SaveAtText stuff on it:
https://www.experts-exchange.com/questions/20891441/Object-Corruption-w-in-MS-Access-2000-Database.html
ASKER
We continue to have weird problems, mostly when saving a record. We are creating a new file for the front-end and importing all the forms, etc. today. I followed your link to the website with a sub called DocDatabase. I am a little confused as to how to use it. Could you give me precise instructions? Are the Application.SaveAsText and Application.LoadFromText built-in, or do I have to go somewhere else for the code? Do I run the DocDatabase from a copy of my front-end? Thanks for your time.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you. I used the code and created a new file for the front-end. I also made created new tables and used queries to import the data into the new structure. So far, everything is fine. (I have also found one computer with outdated virus software and updated. Just in case that was the case of the file corruption.)
What version of Access are you using?