Query Lets Me Delete But Doesn't Actually Delete...
Apr 2, 2007
I have a query based on two tables, joined on one field (this field is the primary key in Table A. Table B doesn't have a primary key). When working inside this query, it gives me the option to delete records. However, when I try, the row disappears from the query but then if I save and close and either re-open table A or the resulting query, that record is back. It doesn't actually delete. If I go to table A directly and delete it, it's gone for good.
How can I make it so that when I delete the record inside the query it actually deletes? And if that's not an option, can I make it so that it doesn't seem like I can delete records inside the query so that people don't make the mistake?
I want to execute a delete query on a table of account records. The query contains two tables linked on account number, one with a list of unique records, linked to the other with "many" records to be deleted. For each unique record from table A, I want the delete query to delete all records with a matching account number in table B. However, I get a message stating that I "could not delete from the specified table." Huh???
I can manually delete from table B, or I can get the query to work if I don't try using table A as the control table and instead specify literal criteria. I have tried all three join types but still no luck. Any ideas?
I have two simple tables. I want to delete the records from Table1 that are on Table2. I've created a select query that gives me what I want but when I change to a delete query, I get this message: "Unable to delete from specified tables"
I think my problem has something to do with security but I can't figure out what to change.
All I am trying to do is insert to have a form with a "Delete Record" button on it. The problem is I don't want anyone to be able to delete a record, I would like someone to have to insert a password to confirm the delete.
DELETE dbo_MP_Audit.*, dbo_CAD_SINV_HEADER.SIN_CANCELLED_YNF FROM dbo_MP_Audit INNER JOIN dbo_CAD_SINV_HEADER ON (dbo_MP_Audit.Usr = dbo_CAD_SINV_HEADER.USR_CODE) AND (dbo_MP_Audit.Sin_DocNum = dbo_CAD_SINV_HEADER.SIN_NUMBER) AND (dbo_MP_Audit.company = dbo_CAD_SINV_HEADER.CMP_CODE) WHERE (((dbo_CAD_SINV_HEADER.SIN_CANCELLED_YNF)=1));
but receiving error message: "could not delete from specified tables".
I have a table that has records listed more than one time. Some of these multiple records have in the score field a zero for the score. I would like to remove all records that are in the table more than once that have a zero for the score in the score field. Any help is greatly appreciated!
i have set up a delete query to delete items ticked on a query. the form related to the query only shows the items with a "False" value in the criteria. anything set as "true" ( ticked ) is reserved for deleting. the delete query looks at the query and selects all items set to true. it works ok but i dont know how to get this work automatically without going trough the access prompts of deleting.
can this be done with code behind a button whereas if clicked, it runs the query and deletes all of the true items?
I have created a query that append all the records to another table3 that are older than two years. But at the same time delete from the original table1.
However it will be moving several records at a time any ideas?
Secondly Table1 has a 1toMany Relationship with the table2, how can i move the records from table2 to table 4 at the same time. Will the data keep its relationship?
How can I automatically bypass the messages "You are about to run a delete query that will modify data in your table."? I don't want an end user to see that message, I just want the delete query to run. Is there a way to get rid of that message without supressing all the messages in the entire database?
Dim db As DAO.Database Set db = currentdb db.execute delete master.* FROM master WHERE workitem=" " and corpid=" "
Please tell me how should i write this code( whcih should execute when a form is loaded)... i want to delete all records where workitem is blank and corpid is blank.
I'm trying to delete records from Table1 where the field "Name" is equal to the field "Name" in Table2. Please see below what I tried and is not working, any ideas?
DELETE table1.* FROM table1 INNER JOIN table2 ON table1.Name = table2.Name;
I'm trying to delete some records out of a table. I have a query that acts as a filter and is linked to two fields of the table that I want to delete from. I have put the * field from the table in the query and set the "Unique Values" property to "Yes". Access tells me "You cannot delete from the specified table".
The filter query is not an aggregate one, and if I remove it and simply have the table I want to delete from (i.e. delete all records) it will let me. I have done this loads of times before but this time it won't budge.
I have two tables. tbl_Job_Master w/ Job_No, and other fields tbl_Job_Desc w/Job_No, Status and other fields
The Select query seems to work fine
I have tried. Right Join, Left Join, Inner Join
All with no luck. If I go to datasheet view I see records returned but if I try to run it 'Could not delete from specified tables'.
DELETE tbl_Job_Master.*, tbl_Job_Desc.Status FROM tbl_Job_Master RIGHT JOIN tbl_Job_Desc ON tbl_Job_Master.Job_No = tbl_Job_Desc.Job_No WHERE (((tbl_Job_Desc.Status)=0));
I could realy use help on this. If anyone has any idea I would appreciate it.
I have now have another problem doing the same thing. I made a nested query like you said below, then I wanted to delete those records - so I changed it to a delete query. when I tried to run it, it says "Specify the table containing the records you want to delete". I tried playing with it, but I can't seem to get it right.
Basically, I have a table and a sub-table, and I want to delete all records from BOTH tables where data matches a certain criteria in the sub-table. Specifically, I have a table of patients, and a sub-table of their test results. If any of the test results are positive, I want to delete that patient and all of his/her test results.
is there any way that i can delete records from two different tables using the same delete query as i have them linked in the relationships using Member ID and if i try to delete from one of the tables then and error message appears stating that key violation has occurred. Thanks
I have two tables. The first table EES has the list of employees that I need and their information. The second table EPAY has even more employees and each employee may have duplicate rows.
I am wanting to delete any employee on EPAY that does not exist on EES.
I tried creating a delete table with the join from EPAY to EES, where a field on EES is null and then added the table fields from EPAY to be deleted. When I "view", it is showing all the rows that should be deleted, however, when I run, it is giving me the error that it "could not delete from specified tables".
Is there a way to do this? I'm not that familiar with delete queries, even though this seems to 'make sense' to me.:confused:
I have exact duplicate records in my table. I would like to remove one of the records so there are no more duplicates but the original record remains. What is the best way to get these quickly removed? thanks for any help!
i have a Query that references 2 tables, a customers table and a purchase table.
i have a query that looks up customers who have not purchased within the last 7 months.
i would like to make a seperate form / query / macro / script or whatever that will prompt the user to enter the primary key from the customer table (customers unique number) and then it delete that customers record from the customers table and not touch anything from the purchases table.
i have looked at a delete query but can't make it so i can run my criteria and not delete from the purchases table
I am trying to run the following sql but where there should be 54 records for deleting I keep getting 0 records will be deleted. I used an findunmatch query to establish tat there where 54 records unmatched. Can somen tell me whether the sql below is correct. I am Very new at this
DELETE * FROM Status WHERE (((Status.Spin) Not In (Select Spin From tbl1)));
I know there is a simple solution to this, but I'm not well versed enough in Access to know the answer. I work in a prison as the "IT" guy, my weakness is programming. Basically I'm the Computer repair guy.
Anyhow, I have two Db's, one is a Db used by the Housing Units to keep track of the inmates, what cell they're assigned to, thier jobs, and when they go to medications. The other Db is our Main inmate Db that keeps track of all the inmates in the institution. I've linked the Housing Unit Db to the main inmate table in our Main Inmate Db. This pulls the inmate's number, Last Name, First Name, cell location, job, and pay rate. However details like days off, the hour the inmate reports to work, and medication details have to be entered manually.
The problem I'm having is when the delete query runs it deletes everthing. Now I know I need to get the query to look at say the inmate's number and the the cell location, and if the cell location no longer matched delete that entry, I just don't know how to express that in the query.
If I can get this working, I'll have an append query question, and you may be able to figure out what I'm going to ask. :)
Hey, Basically my goal is to have records in my customer table deleted from a date in my Service Allocations table. They are related by the Customer ID that is contained in both tables.
Customer Table: Customer ID field 1 field 2 field 3 field 4 so on
Service Allocations: S-Allocation ID Dte Customer ID Mechanic ID Automobile ID Service ID
So where the date is more than 5 years old from the current date (I'm using <=DateAdd("yyyy",-5,Date()) to do this), I want this query to delete the related customer records. How can I go about doing this as I keep getting the "Could not delete from specified tables" error. The actual code works in datasheet view, but somehow when I run the delete query it doesn't work.
The SQL for my query is: DELETE [Service Allocations].Dte, Customer.* FROM Customer INNER JOIN [Service Allocations] ON Customer.[Customer ID] = [Service Allocations].[Customer ID] WHERE ((([Service Allocations].Dte)<=DateAdd("yyyy",-5,Date())));
