Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

Mystifying Repopulation of Table

I was asked to clear some data from an existing Access 2003 (mdb) database. I wrote a query to delete the records from the table. I was in the backend of a split database. Files were not linked. I ran the delete query and records were deleted. After a couple of minutes, I opened the table and the records were back. As a matter of fact, I could delete the records with the query, open the table and watch it repopulate. I could not find an autoexec macro which would cause this (of course that would only matter if I closed and reopened the database). There was zero code in the database. Anyone have any ideas?
0
rodneygray
Asked:
rodneygray
  • 3
  • 3
  • 3
  • +1
2 Solutions
 
pdebaetsCommented:
You might have constraints on your table that prevent records from being deleted if there is a related record in another table. Try deleting records by opening the table, selecting a row, then pressing the delete key. Do you see a message? What does it say? If you have such constraints on the table, you may need to delete the records in the related table first.
0
 
PatHartmanCommented:
The other possibility is that your query includes a dependent table.  In a multi-table join, only records from the "lowest" level table will be deleted.  For example, if you use a left join from order to orderdetails and try to delete, rows from orderdetails will be deleted but NOT rows from order.  Change the query to remove the dependent table if this is the case.
0
 
Dale FyeCommented:
Is there code in the front-end that copies  data to a local table and updates the original table at a later time (some form of replication)?

What version of Access are the FE and BE in?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
rodneygrayAuthor Commented:
Access version is 2003. I am working with the BE file. I could find no code that updates the original table. Of course there could be a FE program somewhere on the system that I am not aware of that re-populates the table. The way I envision something like that happening is:
-FE has a linked table to the BE table.
-Code within the FE monitors the BE linked table.
  -If record is added, it copies that record to FE local table.
-Code within FE to monitor BE Linked table
  -If records deleted from BE, they would be restored to from FE local table.


Is there a method to discover any FE that has a link to a BE table?
0
 
PatHartmanCommented:
You never answered if your query includes more than one table.
0
 
Dale FyeCommented:
"Is there a method to discover any FE that has a link to a BE table? "

From your original post

"I was asked to clear some data from an existing Access 2003 (mdb) database"

Who asked you to do this?  They are the person that should be able to tell you where the front-end is located and what it does.
0
 
PatHartmanCommented:
You should also look at the table definitions to see what RI is defined since RI could be preventing the delete also.  It is not that the records are being deleted and repopulated, they are not being deleted at all and it is only a fluke in the interface that makes them look like they were deleted.  So, there are two possibilities.
1. The query contains more than one table and you aren't deleting what you think you are deleting.
2. RI is preventing the delete.
0
 
Dale FyeCommented:
Had not thought about this, but if the table you are trying to delete from is used as the "lookup" table for another table, and the field that it is used in is a required field, then I believe this would also occur.

Theoretically, if the database has this type of relationship built in, then it should be defined in the relationships, but in my experience, it rarely is.  Just one of the many reasons I don't like the "lookup" feature of table design.

In order to determine whether this is the case, you can open your tables in design view change the properties tab at the bottom of the page to the "lookup" tab, then step through each of the fields in that table to determine whether the RowSource for that lookup is the name of the table you are trying to delete from.

You could write some code to do this loop for you, but I have to run to a meeting.
0
 
rodneygrayAuthor Commented:
Thanks for all your insight.
Dale, the client that asked for this has no clue as to what a database is. They did not even know where the database was located. They only know how to use it. This is the backend database and there are only 6 tables in the database. I opened each table to verify data in them. I looked at table relationships and there is no relationship between any of the tables. I have not been able to contact client to get a copy of the database. Perhaps I can do that by the end of the week.

Pat, query only deletes records in one table.
0
 
pdebaetsCommented:
Try copying the backend database file to a new name.

Open the database with the new name while holding down the <Shift> key to ensure that no code executes when you open the database.

At this point, you should be free of any interference from a front-end application, or any mysterious code executing in the back-end.

Now try deleting the records from the table(s) and see if they re-populate.
0
 
rodneygrayAuthor Commented:
Client had to make a decision to allow me to analyze all aspects of the database. I found the following on the server. The prior developer had a couple of different front-ends  and two backend tables that were accessed by FE.

One of the FE programs was located on the server. It was called by a scheduled job (called every minute) which opened a form when started. In the on-open event the developer had code that pulled data from db1 and moved it to db2. DB1 records were entered each time the operator added an item to the inventory. DB1 had data from the time the system was installed. Records were never cleared or archived.
 
The code run from the server added all records from DB1 that did not exist in table on DB2 to inventory table in DB2. I was deleting records from DB2. When the scheduled DB1 job ran, it just added all records back to the DB2 inventory file.

Not the most efficient method to handle this job since there were a couple of million records in DB1 production table and DB2 inventory table.

Thanks for all the ideas. It turn out to less nefarious than I originally thought. I was not sure who should be awarded the points. I went with fyed because he mentioned the file being restored from another db which is exactly what was happening. I wish there was some method to tell which fe's were linked to a be.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now