Results 1 to 6 of 6
  1. #1
    FJM is offline Competent Performer
    Windows 98/ME Access 2003
    Join Date
    Jun 2010
    Posts
    117

    ##deleted records##

    I have created a small database which has a problem where every now and then it automatically deletes records.
    There are 12 tables in total but only 2 which seem to have the problem.
    TABLE 1; NCRFORM Table 2; NCRDETAILS
    NCRNO (Number - PK) NCRID (Number - PK)
    Date Etc
    Etc
    The tables are joined between NCRNO and NCRID on a one-to-one basis where all Table 1 and Table 2 where equal


    The database itself is stored on a remote server and accessed from individual terminals.
    Every now and then, when data is entered, details on Table 2 are automatically deleted and each entry is replaced by a series of #########
    An error message is displayed and I then have to Compact and Repair the database to get rid of the problem.
    The Compact and repair allows me to delete and then re-establish the record in Table 2 but it also removes the Primary key within Table 2 which then has to be put back in.
    Does anyone have any idea what is causing this problem and if there is any permanent cure ?

  2. #2
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    I would expect that the problem is that someone (or some process) is deleting records. Access would only do that itself in the case of enforcing cascade deletes, which still means that someone is deleting a related record.

    I'd start by finding all processes that use or refer to that table. I'd also check the relation between the two tables, and make sure that Access wasn't going to delete related records in table 2 automatically on delete of a record in table 1. Then find out which processes are causing the delete in the first table.

  3. #3
    FJM is offline Competent Performer
    Windows 98/ME Access 2003
    Join Date
    Jun 2010
    Posts
    117
    Unfortunately, I can confirm that no one is knowingly deleting a record. I can guarantee this because it happened yesterday to me after I had entered a record into the database. It accepted all of the details, I exited, and then when I went back in a couple of minutes later it told me that the details (in Table 2) had been deleted. I then had to repair etc.
    When you say check the relationship between the two tables, can you elaborate on that. Have you anything in mind for me to look for ?

  4. #4
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Ah, yes, with emphasis on that word "knowingly". Clearly, there must be a rogue process in Access that is causing the delete.

    1) Check the relationships between those tables and any others, and see if there is a cascade delete situation. http://office.microsoft.com/en-us/ac...001173951.aspx It almost seems like there may be some temporary table that (unaccountably) has a cascade delete trigger of some sort.

    2) Check object dependencies, and determine what each of the objects dependent on the table actually do.

    3) If this were in my installation, I would do a search of all the VBA code for the word "delete". I would also do a search for all references to those tables. If you heavily use macros, I'd take a junk copy of the database, and use Access's facility to automatically convert the macros for each form to VB. That's the reason I don't use macros, I have no idea how to search the contents of macros - macro code is totally hidden from my eyes, as far as I know. By switching it all to VB, it's all findable with a global search.

    4) Create a new, blank database, and import the old database into it. First, import everything, and see if the database still has the bug.
    4A) If not, then verify the new one functions as expected, back up and retire the old one, and deploy the new one.
    4B) If so, then start over, and import the minimum number of items that allows you to insert the records you were just inserting. Test if the problem exists yet. Keep adding items from the old database until the new version finally breaks.
    and so on.

    Sorry I can't give you any magic bullets. This one is disguised as a lot of diagnostic work.

  5. #5
    FJM is offline Competent Performer
    Windows 98/ME Access 2003
    Join Date
    Jun 2010
    Posts
    117
    Thanks for that. I will start working through the steps.
    Do you think the fact that the database is on a server which is then accessed through a network could have anything to do with the problem ? I know there are times that the server / network is slow. Could something be happening as data is being entered onto and transferred through the database ?

  6. #6
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Any issue with the network should only result in success or failure, not in random deletion some time after the update. That's a stability feature that's required in modern database products.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-12-2013, 04:41 PM
  2. Remove deleted records
    By amerifax in forum Access
    Replies: 4
    Last Post: 10-17-2012, 03:26 AM
  3. delete query produce #deleted in all deleted values
    By learning_graccess in forum Queries
    Replies: 2
    Last Post: 03-31-2012, 07:20 AM
  4. Log Data Changes and Deleted records
    By pkstormy in forum Code Repository
    Replies: 0
    Last Post: 12-31-2010, 12:41 AM
  5. Records deleted with NO warning message.
    By evanscamman in forum Access
    Replies: 2
    Last Post: 12-14-2007, 11:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums