Related Topics

Problem Deleting Records Using Custom Delete Sequence

When deleting one record its deleting record from another table
Having problems when I delete a record from my tenants table.
Two tables one property to many tenants is my relationship with enforced referential integrity ticked and cascade update ticked and I took the cascade delete off.

Problem: When I delete a tenant from tenant table it is also deleting the whole property record from the property table also. I know it is not correct to delete records but its just in case of wrong information being recorded and its bugging me why this is happening when cascade delete is turned off.


Delete Query Deleting Too Much
Importing data to a table each week that can sometimes include duplicate records, I created a duplicate query. It works well to identify those records that are duplicated, but instead of deleting the extra copies and leaving one it proceedes to delete them all.

Any advice? Is there a better technique than the delete query


Delete one or more records from a database
Deleting inaccurate or obsolete data can make your database faster and easier to use. For example, if you move data from the previous calendar or fiscal quarter to a set of archive tables in another database, you can reduce the size of your working database and help it run faster. If you remove inaccurate or duplicate data, you can reduce business costs.
This article explains how to delete data from a Microsoft Office Access 2007 database. It also explains how to delete data from individual fields a relatively simple process, and how to delete entire records a process that can be much more complex. the article also explains how to delete data manually, and how to use delete queries.
In this article
* About deleting data
* Delete the data in a field
* Delete the data in a list
* Delete unrelated records from a database
* Delete related records from a database
* Delete tables from a database
* Sample criteria for select queries


Linking table and Deleting one
If I delete linked table linked to maindb in frontend db will this cause problem to the maindatabase data? [not deleting the datas within the table - but deleting the whole table?]

What will happen if I delete table and keep on relinking it back? Can action like this cause any problem to the front end of even maindb?

Also is there anything special I need to keep in mind while linking tables across databases


Delete Duplicate Records From Access Tables
Yes, deleting duplicates should be easy, but it's not. A Google search for newsgroup posts about deleting duplicates scored over 2500 hits. Clearly, this is a common problem.
The confusion is compounded, no doubt, by the existence of the built-in Find Duplicates query wizard. While this wizard does a great job of helping you identify duplicates, it does not provide a way to delete them. Many posts at the newsgroup included frustrated comments like this: "I can see them, but I can't delete them!"


Use queries to delete one or more records from a database
This article explains how to delete data from an Microsoft Office Access 2007 database by using update and delete queries. You use an update query to delete individual fields from the records in a database, and you use a delete query when you need to remove entire records from a database, including the key value that makes the record unique. As you proceed, remember that Access also provides a number of ways to delete part or all of a record manually.
For more information about deleting records manually, see the article Delete one or more records from a database.
In this article
* Understand using queries to delete data
* Plan the deletion
* Stop Disabled Mode from blocking a query
* Delete partial records (one or more fields)
* Delete entire records
* Sample criteria for select queries


Delete Query deleting more records than Subquery Returns
The following Delete Query is deleting all records, but I need it to delete only records returned by the subquery.

The subquery correctly returns records when I test it without the Delete operation. Could I be implementing the Delete Query incorrectly?

DELETE tblCheck.fldPrimary
FROM tblCheck
WHERE EXISTS (SELECT tblCheck.fldPrimary, tblCheck.fldPkg, tblCheck.fldAuditDate
FROM tblCheck INNER JOIN tblImportCheck ON (tblCheck.fldPkg=tblImportCheck.fldPkg AND tblCheck.fldAuditDate=tblImportCheck.fldAuditDate


Error handling when deleting records with button
It's been 8 years since I've taken a Visual Basic class and I'm a bit rusty. Just starting to use VBA for a db I'm designing.

I have a multiple items form with a tabular layout. When this form is loaded, it filters in only at a few select records.

At the bottom, I added a "Delete Button" which allows the user delete one of the above current records with:

DoCmd.SetWarnings False 'Removes warning box on deletion
DoCmd.RunCommand acCmdDeleteRecord 'Deletes current record
DoCmd.SetWarnings True 'Reinstates warnings before deleting

However, if there is no record to be deleted (either they have already all been deleted or there is no records to begin with) and the user hits the Delete Button, the program flips out. How can I have it catch that, so if there are no records to be deleted a MsgBox says "There are no records to be deleted" and the delete does not run?


Whats the easiest way to delete a record with related records?
I was reading that cascade delete option will do this, but is that only if you are deleting the record right from the table?

I want to have a button click event that will delete the record and all related records.anybody have any examples or ideas on how this would work?


Deleting Multiple Areas
I have a form that comprises of Sub forms within tab controls.

I have placed a delete button on the main form to remove any unwanted records. There is a problem with this. You are not able to delete a main record when a child record exists in related tables.

How can I achieve this using a click function


Deleting Records using VBA Module
What is the recommended method for deleting records using a VBA module? Currently I am using an SQL method that specifies the table name and the upper time stamp date from which values should be deleted.

My database is continually being updated with new files from this same VBA module. I would like to delete old records based on a rolling window (say 30 days). Would this be the best way to go? It seems a bit slow.but then again my database contains 1 million + records.


How to Trigger "No Records Found" When There is No Records in .accde/.accdr?
I have a database in .accde/.accdr format. The database has been designed using many relational tables. I set a form for deleting some records or all database. The problem is when you delete the records with action query it pop-up a message saying "you about to delete zero rows (0s)from-specified table" ten times for 5-relational tables. I know how to get read of this message and display a customer message as follows:


Delete First Record in a Table?
I've got a situation where I need to append data to an empty table that will autonum the records for me, delete the first record of that table, run a series of queries using the table's data without that first record, then delete all the records from that table again to set it up for the next time that event needs to fire.

I'm having trouble figuring out how to delete just the first record from that table automatically as part of the sequence of events.


Matching Tables and Deleting
I am trying to match table 1 (EMP ID) to table 2 (ID) and delete all records from table 1 that do not match. I did get it to delete but it deleted all the records but the one that I wanted to delete.


Stopping people from deleting records in a subform
I have a form with a subform. I would like to stop people from deleting records in the subform. I know under properties in the main form that I can change delete records to NO but no such property exist under the subform.


Creating and deleting records : A simple task
Microsoft Access 2003 is a great tool for creating, managing and modifying databases. Like any tool or application, the more that you use it, the more adept you will become. Creating and deleting records in Access is one of the simplest things tasks. Here are some steps that will help you to create and delete records in Access 2003.


Reverse Sequence of Numbers
I think the best way for me to ask the question is to explain the scenerio. Data is linked to an external source. Each time an event occurs (like a payday lets say), a new record is created and it is assigned a sequence number. (For argument sake, there is no set day or date that payday falls on.) Someone paid by the company two times would have two records, the first payday would be sequence # 1 and the second payday sequence # 2. Someone with the company multiple years could have 100 records or more. I am trying to isolate the highest sequence number for each employee. The highest number would indicate the latest event. I have utilized other software in the past and used a rank function which would reverse the numbers and then I could just filter for sequence = 1. Is there a way for me to do this in access


Specify the Table containing the records you want to delete
Im currently using MS Access 2003.

I'm experiencing this error: Specify the Table containing the records you want to delete when deleting a records.

I have 2 tables. Table A is my reference IDs to delete from Table B.
When I link now this 2 tables, then run the query, it prompts me Specify the Table containing the records you want to delete, yet I have already link the Table A which is my reference IDs to delete in Table B.

How to resolve this?
Here's the syntax from MS Access (SQL View)

DELETE DISTINCTROW TABLE_B.ID_A
FROM TABLE_A INNER JOIN TABLE_B ON TABLE_A.ID_A = TABLE_B.ID_A


deleting records that have no relation
I have two tables, one for jobs and one for clients. I have a one-to-many relationship, so one client can have many jobs. Due to a problem with data entry, I have some client records that have no job associated with them. I need to delete these records. I am very new to access and this isn't supposed to be my job, so I don't really know how to do this


Deleting records in datasheet view subform, from mainform
I have a form with a subform in it. The subform is in datasheet view. I want the user to be able to select the row in the subform they want to delete, and click the delete button. I have code which deletes a record on the subform from the mainform but it is only deleting the last record on the subform. I need it to only delete the record the user selects on the subform