I have for sometime regretted ever using the Database and associated Relation, triggers etc. I wish i had stuck to the old ways of doing these jobs myself. Case in point. I have a Patient table with a Invoice Child table, that has Item Numbers child table. There is a primary key link between patient and Invoice and a primary key link between Invoice transaction number and the item number table. There are stored procedures for generating the primary keys, and unfortunatly foreign keys for the purpose of Update, Insert & Delete rules and triggers. Whilst testing some data import routines from the patient archive files ( same patient presents for a new surgical procedure), i found that when I deleted a test patient record (No child invoice had been inserted for the test patient), the database would present me with an error "Alias Invoice is Not Found", then the database would proceed to delete the patient record. After i had finished my data import routines, i thought i would investigate this error message some more, as it only just appeared in the last couple of days. Now i must stress here that i am working in a development project, with my Production version safely packed away. If i attempted to Validate Database, the error would appear during the rebuild of keys, but i would still get a Database is valid after the Error. Then all hell broke loose and i lost all the info from the database and could not recover. Loaded backup, with the same result. Now i Use the GENDBC tool regularly and decided to rebuild a brand new. clean and empty version of the database, to see if i could recover, that way. Generated the database, and validated, only to get the same error in this brand new and empty database! After much stuffing around trying to figure what was the cause of the error, using VFP6,7 & 8 i went back to my Production Database, Backed it up Again, then copied the CDX files for patient & invoice into my development folder, and hey presto after a reindex, i could validate with no errors. I generated a new GENDBC and built a new, clean & empty database, and again no errors. Now i have had many a support call from users who have been presented with Index Errors, and told by system to delete the index and recreate (I hope you all Know, that that advice is fatal) and occasionally errors from the database about the table does not match the entries in the database. I have usually found that placeing a new clean CDX file does not fix the error, and the error focus changes from the table CDX to the Database itself. In the database i can usually find the problem is a Deleted Index entry (how it gets deleted I Dont Know!)and can be repaired quite easily, ( Rebuild of the table and cdx can also be required at times!). Then theres MEMO corruption, we wont go there today! It just seems that for all this trouble, i would be better of removing all the database stuff, use free tables and manage the whole thing myself. Sorry for the rambling, my weary eyes tell me, soon be time to go home!
Happy Easter to all regardless of religous preference!