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 RepliesI'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.
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?
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
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.
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
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
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?
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 RelatedI 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!
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
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!
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 RelatedI 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]....
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?
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 RelatedI 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.
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.
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.
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.
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..
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.
I'm wondering if it's possible to create a Query / Macro / VBA or whatever works, to detect the duplicate records in a table and delete them with the push of a command button. Or, even to do this automatically when the database opens using an AutoExec macro or something ?. I'd like for one copy of each record to be left intact in the table.
View 8 Replies View RelatedIm trying to make a basic customer database and pos system. The part iam having trouble with is, I have two tables as follows:
Inventory: orders(invoices):
productId ProductId
amount (in stock) amount(sold)
pricePerUnit CashTendered
I want the amount(in stock) sold in each order to be deducted from the total in the amount(sold) automatically.
I have an Access table with data fields however at after the last field column, I have about 15 blank and unselectable or uneditable columns that I don't want there. How I delete these? Reason I want to delete them is because table is a subform on a main form and these extra columns are visible on it and they also cause a scroll bar to shown
View 4 Replies View RelatedAll 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.
View 13 Replies View Related