Solved

Mystifying Repopulation of Table

Posted on 2014-01-09
11
211 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

920 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

15 Experts available now in Live!

Get 1:1 Help Now