?
Solved

Mystifying Repopulation of Table

Posted on 2014-01-09
11
Medium Priority
?
219 Views
Last Modified: 2014-01-26
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
Comment
Question by:rodneygray
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +1
11 Comments
 
LVL 12

Expert Comment

by:pdebaets
ID: 39768961
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
 
LVL 39

Expert Comment

by:PatHartman
ID: 39770200
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
 
LVL 48

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 39776484
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:rodneygray
ID: 39776608
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
 
LVL 39

Expert Comment

by:PatHartman
ID: 39776636
You never answered if your query includes more than one table.
0
 
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 2000 total points
ID: 39776673
"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
 
LVL 39

Expert Comment

by:PatHartman
ID: 39776739
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
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39776809
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
 
LVL 1

Author Comment

by:rodneygray
ID: 39780148
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
 
LVL 12

Expert Comment

by:pdebaets
ID: 39780254
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
 
LVL 1

Author Closing Comment

by:rodneygray
ID: 39810768
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question