Queries :: Could Not Delete From Specified Tables Error
Dec 17, 2013
I'm trying to delete data (no archiving required) from 5 tables that all have a one-to-many relationship between them and keep getting the following error: "Could not delete from specified tables".
I've tried everything I can think of including the following:
* Set unique records property to 'Yes'
* Enable referential integrity and cascade delete records in the relationship diagram
* Checked the DB isn't read only
* Have correct permissions to delete records
I'm the only person in the database but I'm at a loss and short of me actually deleting the 23891 records manually I can't think of what else to do!
View Replies
ADVERTISEMENT
Mar 4, 2015
i keep getting an error "could not delete from specified tables"
I have a table with this data
Main table
bub|12345|1
bub|45678|2
bub|91011|3
Child Table
bub|45678|2
result in Main table
bub|12345|1
bub|91011|3
match from child table and delete from main table.. but i keep getting error "Could not delete from specified table main table has unique records. and it is primary key..
View 3 Replies
View Related
Jul 9, 2015
I have two linked tables (Unique_ID)
Table1 has a record of unique tasks.
Table2 has a list of people to do those tasks
They are linked by a Unique_ID (one to many - Table1 to Table2).Sometimes there can be an instance where there are Unique_ID's in Table2 but the reference Unique_ID is missing from Table1.
I have a select query that can show me these like so.
table2 table1
Unique_ID Unique_ID
Like * Not Like *
I can open the query and simply delete the records fine however i wish to automate this.What I want to do is delete the unrefenced Unique_ID's from table2.I have tried to just convert this select query into a delete query but it asks me which table I wish to delete and I cannot see where to enter this information.
View 3 Replies
View Related
Jun 19, 2015
I was able to create a query that selects records from one table that have several fields which don't match with another table.
The end-game with this query is to have it delete the records in table 1 that don't match records in table 2. The delete query tells me that it could not delete from specified tables.
I've attached the select query, the delete query and the error, and the relationship table for the db.
View 8 Replies
View Related
Feb 27, 2014
how to query data in my database based on a number of different criteria.I have reached a stage where I can get all the data I need from one query, however I can't figure out how to further query this data to return records from a table with the most recent date only. I have searched the forum, googled and experimented myself but I am running into "Aggregate Function" errors.In this scenario there are 3 tables. tblJobs, tblEquipment and tblInspectionLog. Each tblJobs record can have multiple tblEquipment records attached to it, and each tblEquipment record can have multiple tblInspectionLog records attached to them.
I would like to query the database for what tblEquipment records have been assigned to a tblJob ID and also return only the tblInspectionLog record with the latest Inspection_Date field.At the moment I am able to see tblEquipment records attached to tblJobs, however duplicate records appear due to multiple InspectionLog records associated with the equipment.
I have tried to filter records from tblInspectionLog using the "Max" criteria under Inspection_Date field in my query. This however returns an "Aggregate Function" error.
View 4 Replies
View Related
Oct 13, 2005
I have created a query to delete duplicate records.
I followed the instructions on the following help site:
http://office.microsoft.com/en-us/assistance/HA010345581033.aspx#15
I created a join on the two tables just as required, on one of several fields that sometimes duplicates.
I have two criteria from the Distinct Records query that I joined the delete query with, which are a <>MaxOfOrgDate OR <>MaxOfHistID (the primary key).
The reason for that is I know that some of the OrgDates are duplicates, so if they are the same I just want to delete the lower HistID.
When I preview the query it all works perfectly and displays exactly what I want to delete, but when I try to run the query it says 'could not delete from specified tables.' The table is not read only so I don't know what the problem is.
Note: On this particular table, if one field is a duplicate, they all are, execpt the HistId field. This is because I append data into the table from another and then I want to delete duplicates, leaving only the data that has changed since I last appended.
Thanks for you help!
View 1 Replies
View Related
Aug 2, 2006
I am creating a form that includes an add and delete button on the form. I have two other databases that these buttons work with. Using the same VB code (and name of the button) in my current database gives me the following error:
"The express On Click you entered as the event property setting produced the following error: A problem occurred while Microsoft Office Access was communicating with the OLE server or ActiveX Control.
*The express may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro."
My VB code is as follows for the two buttons:
Option Compare Database
Private Sub add_Click()
On Error GoTo Err_add_Click
DoCmd.GoToRecord , , acNewRec
Exit_add_Click:
Exit Sub
Err_add_Click:
MsgBox Err.Description
Resume Exit_add_Click
End Sub
Private Sub delete_Click()
On Error GoTo Err_delete_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Exit_delete_Click:
Exit Sub
Err_delete_Click:
MsgBox Err.Description
Resume Exit_delete_Click
End Sub
Any ideas on what may be the problem and how to rectify this error? All help is appreciated. Thanks in advance.
View 1 Replies
View Related
Nov 19, 2014
I'm a newb to Access and SQL and database management. I have a main table and a dependent table with a 1 to 1 relationship, relationship integrity and cascade update and delete. I used an append query after I had modified several records, and after the append deleted one of the records from the main table. It remained in the dependent table. Access didn't catch the disparity even after a restart. I broke the link and tried to reestablish it, and of course Access wouldn't let me. I deleted the record manually from the dependent table and all was well.
Why would referential integrity/cascade delete not be recognized after an append query?Access 2010.
This is the second time in two days that cascade delete has been broken in two days after using a query on the main table. Yesterday, after referential integrity was broken, deleting the records from the dependent table allowed me to restore Cascade Delete functionality and it worked for the rest of the day.
View 9 Replies
View Related
May 6, 2014
I have a form which contains many text boxes. What I'm doing now is that any time I create a new record I save this record to the table the form was created from and I also save to a specific field of an irrelevant table(other table) the index of one of the text boxes of this form.
e.g
My form contains the text boxes: "Index", "Tabletype", "Component".
I save all of the above to the table "tblTransistor".
Then I take the "Component" only and I save it in the field "Test" of another table which I call "tblManComps".
My form is called: "Transistor".
Now I want to be able to delete at the same time the record from each table.
Is it possible to have some VBA which will do it?
View 4 Replies
View Related
Mar 31, 2005
Is it possible to delete a table from a remote access db.
i.e
table "A" is in database "A"
from a form in database "B" i would like to run a routine that would
Delete table "A" from database "A"
i have tried using "DoCmd.DeleteObject acTable" but i think this only works for the currentdb
Can anybody help
Regards
Bjackson
I am using A2K
View 1 Replies
View Related
Nov 28, 2007
I have two different tables and I need to run a delete query to delete all information pertaining to an employee when he/she is expired threw a button on a continious form which uses one of the tables that needs deleted. How would I do this and please explain in newbie terms.....Thanks!
View 4 Replies
View Related
Feb 20, 2008
This seems like it would be a common problem but I tried to search for solutions to this and I haven't been able to find a specific answer. The forum excludes too many of the words I'm trying to use in my search....
I have two joined tables and I want a delete query to delete the specified records in the primary table and all of its related records in the secondary table.
DELETE tb_AccountHeader.*, tb_AccountDetail.* FROM tb_AccountHeader INNER JOIN tb_AccountDetail ON tb_AccountHeader.Index = tb_AccountDetail.Index WHERE tb_AccountHeader.PeriodStart=#1/1/2008#
This gives me the error "Could not delete from specified tables". I tried SELECT DISTINCTROW but that doesn't make a difference. I do not believe that it is an issue with permissions because I can delete records from either of these tables if I remove the join.
Any help would be appreciated.....thanks.
View 4 Replies
View Related
Apr 10, 2007
Hi,
I'm trying to delete a table once I've used it and always get the same error saying that the table is already in use, these are the commands I've tried:
DB.TableDefs.Delete ("Report")
AND
strSQL = "DROP TABLE Report"
DB.Execute (strSQL)
Can't figure out how to avoid this. Please help
Mike
View 1 Replies
View Related
Jun 15, 2006
Hi,
What's the SQL script for deleting all records in all tables, so without deleting the tables completely,
As I have over 50 tables I do not want to use:
"Delete from TableName"
Is there some wildcard I can use?
Thanks
View 7 Replies
View Related
Jul 11, 2007
I have two table which is c:db1.mdb; c:db2.mdb
The user is currently using c:main.mdb
In main.mdb, I have a form, and have a combobox with two value, which is db1.mdb and db2.mdb.
If the user choose db2.mdb, delete all the current db1.mdb link tables(if there is any), and import all the tables from db2.mdb as link table
If the user choose db1.mdb, delete all the current db2.mdb link tables(if there is any), and import all the tables from db1.mdb as link table
And I need to perform some vba on the afterUpdate event of combobox.
My question is, how to
1)delete all link tables using vba
2)Import all tables from a certain mdb file as linked table using vba?
View 1 Replies
View Related
Mar 22, 2008
Hi, I am designing a database for a basic payroll function. So far everything was going well until i started to design forms. I cannot delete multiple table entries from one table that connects two tables. This situation is as such:Employee Table: contains entries as follows:ID: Autonumber (PK)First Name Last Name PayCategory (Salary or Hourly) (List Box)PayInfo Table: contains entries as follows:ID: Autonumber (PK)Employee Name (Text)Pay RateThe relationship of these 2 tables is a one to many relationship; where the ID from Employee Table is connecting to Employee Name in PayInfo Table.When i created a form using these 2 tables and added a delete button to it, the entries from Payinfo table get deleted when i click the delete button, but not from the employee table.I have tried everything i can think of :( . What am i doing wrong? Can someone please guide me in the right direction. Any help would be greatly appreciated.
View 2 Replies
View Related
Apr 3, 2006
I think this is an easy one...
I have two tables - one is a Master. I want to delete the records in the Master if they are in the second table. Here's my SQL and I can't get it to work:
DELETE from TestMaster
INNER JOIN on JoinedDupList
ON TestMaster.ID=JoinedDupList.ID
WHERE JoinedDupList.ID = TestMaster.ID
I'm being told I need to define the table to delete from... I thought I defined it.
Thanks!
View 2 Replies
View Related
Feb 12, 2007
Hi,
Please, anyone know how to delete few table's record by using one query ?
I want to delete 10 tables, all the records inside at one times. I tried there is only one tables at a time. Is that imposible to do that?
shinyo
View 1 Replies
View Related
Dec 31, 2014
A database at our Food Pantry needs to be updated yearly on Jan. 1 by eliminating all data in two fields: "ID Check Date" (date field) and "Signature Obtained" (check box). There are hundreds of records. Is there a way to remove the data from the two fields (columns) while retaining the fields (and their formats) but with no data? We use Access 2010.
View 3 Replies
View Related
Nov 14, 2012
I need to delete data in a table that named Cap One Table. I can not use a delete query since database located on company server and it is being used by many people. So, I have created a form and put on there two text boxes - txtStartDate and txtEndDate and command button cmdDelete. An idea behind this is that a user types date in txtStartDate and txtEndDate and click command button and delition happend in table Cap One based on that criteria. I put the below code in command button click event , but it doesn't work.
Dim startDate As String
Dim EndDate As String
Dim strSql As String
startDate = txtStartDate.Value
EndDate = txtEndtDate.Value
[code]....
View 4 Replies
View Related
Oct 17, 2014
I am not too clear about why it happens. See following diagram:
My TBLItemID table is having several 1-to-1 related tables
I have enforced referential integrity and checked off Cascade delete related records
When I remove a record from TBLItemID the same row will be deleted from every one of the related tables
Yet when I go into any of the related tables and remove a row, via the 1-to-1 relationship I would expect that it will propagate through the whole web and remove the same row from every table. However that is not true. It will only remove its own record without touching any one else's.
How is cascading delete supposed to work? Does it work only when we remove records from parent table?
View 1 Replies
View Related
Aug 20, 2012
The tables in .mdb database are linked to a backend database. How do I delete the links, without deleting the tables?
View 3 Replies
View Related
Sep 12, 2012
I have a form, with tab control which includes fields from two tables. When I delete a record on a form it deletes fine.When I go into the tables the record is still there in the one side of the relationship but has been deleted in the table of the many side.
I've read that a simple delete button on a form will only delete records in the many side of a relationship from a table.How do I get a delete button that deletes the record from both the one and many tables.
View 10 Replies
View Related
May 21, 2015
I need to delete all the field names from my table so I can import a new excel file with different field name headings,
currently I run:
DoCmd.RunSQL "DELETE * from table1"
this deletes the data in the table, but not field names.
What Vba command will allow me to do this.
View 9 Replies
View Related
Apr 30, 2014
I am close to finishing a new database. In this database I have some VBA code that does a bunch of stuff, one of those things is to check if a temp table exists, and if yes then it will delete it.
Once finished this database will be made available to a range of people via Access Runtime 2010. I'm just wondering will the VBA code that deletes a table work in a Runtime environment?
I guess I should also ask if a save import process will also run via Runtime.just need to make sure that the users that need to do this code have full access if its required.
View 4 Replies
View Related
Mar 24, 2015
I have two queries, both delete from the same table and both have joins..This one works:
Code:
DELETE RequestCheck.*
FROM(
SELECT TOP 3 PayRequest.RequestID, PayRequest.GBPAmount, PayRequest.Currency, PayRequest.RequestDate
FROM PayRequest
WHERE (((PayRequest.Currency)="EUR Euro") AND ((PayRequest.RequestDate)>#11/16/2014#))
ORDER BY PayRequest.GBPAmount
) AS TOP3 INNER JOIN RequestCheck ON TOP3.RequestID = RequestCheck.RequestID
WHERE (((RequestCheck.CheckID)=8));
This one does not:
Code:
DELETE RequestCheck.*
FROM 300_tblDeleteRequestCheck INNER JOIN RequestCheck ON [300_tblDeleteRequestCheck].RCID = RequestCheck.RCID;
View 1 Replies
View Related
