Solved

Mystifying Repopulation of Table

Posted on 2014-01-09
11
210 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
  • 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 34

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 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 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
 
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 34

Expert Comment

by:PatHartman
ID: 39776636
You never answered if your query includes more than one table.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 500 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 34

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 47

Expert Comment

by:Dale Fye (Access MVP)
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now