Hi i have a lot of linked table that i want to delete I have the macro below that deletes one at a time but i have to put in the name is there a way to delete all the ones that end in _cfd_posting as before this name there is a six digit number but these r all different is this possible
Sub delete()
DoCmd.DeleteObject acTable, "060204_cfd_postings"
End Sub
I have two tables "Master List" and "Audit". I want to delete the records from the "Master" that match those in the "Audit". I am using Access 2010 and have used it often but have built very few queries.
View 3 Replies View Related:confused:
I am Developing a simple VBA program for Access for my final year of my Degree and I have come up with a problem that I don’t seem to be able to fix.
I have create tables using SQL
SQL = "CREATE TABLE tblInvoiceLines" & _
"(InvoiceNo LONG, PartNo LONG," & _
"Quantity LONG)"
DoCmd.RunSQL (SQL)
Deleting this with the below code works,
SQL = ""
'This will delete the Invoice Line Table
SQL = "DROP TABLE tblInvoiceLines"
DoCmd.RunSQL (SQL)
But once I alter the table with the below code the Delete command doesn’t work, does anyone know how I can fix this problem?
SQL = "ALTER TABLE tblInvoiceLines " & _
"ADD CONSTRAINT fkInvoiceNo " & _
"FOREIGN KEY NO INDEX (InvoiceNo) REFERENCES " & _
"tblInvoices (InvoiceNo) " & _
"ON UPDATE CASCADE " & _
"ON DELETE CASCADE "
conDatabase.Execute SQL
Thanks
Coops
In my program you are allowed to make backups of the current databases to an external file. It also allowes you to get the tables back from one of those files. At least it should.
I thought this wouldn't be a biggy. I thought I'd just delete my current tables and replace them with "docmd.tranferdatabase" acImport.
But the stupid thing wont let me delete its tables because they are connected to each other. Can I somehow delete the link before deleting them?
If so will the links be there again once I restore the tables from the backup?
Or do I need to reconnect them somehow to each other.
Or maybe another approach would be to import them without deleting the original ones. Thus the new ones get the same name of the original ones + "1" or something. Than I delete all records from all my original tables. And refill them with all the records from the new ones (the + "1"-ones) And delete the new ones. And if I would do that, would it be best to use AcImport or AcLink (from docmd.tranferdatabse)
Can I have some pro's oppinion on this plz.
I was wondering if there is a way in selecting all tables and deleting at one go instead of selecting one by one. Perhaps a little bit of programming. Tx in adv
Shah:)
I am trying to make a database for a shop which can do several things:
1) I want to give in what is being bought by the shop to make their products form. This has to be saved in a table named STOCK.
2) I want to be able if I say in a form that from these things I made that product that these things dissapear from the STOCK table and that the final product is added to the STOCK.
3) If the final product is solled I want to remove it from stock.
In the end if I want to see what is still in the stock I will only have the things that are still there because the rest is removed.
How can I do this??
Thanks for your answer
Hi.
I have a cluttered database with too many 'no longer necessary' queries and tables.
Is there a way (other than deleting them one at a time) to delete multiple ones at once, as I can in My Documents, et cetera?
Russ
Greetings all.
Having searched the last couple of days, I am unable to find a resolution to this problem. If it has been answered already, a simply link or thread name would be appreciated. If not, help is greatly appreciated.
I have code running that imports data from a .txt file into a table.however, on some days there will be errors importing some of the data. I have found that from the last three months, there has been no data of consequence lost so, I don't care about the data errors. However, any time there is an error with the import it creates a table that is named for example:
01-03-05 Auth_ImportErrors
where "01-03-05 Auth" is the name of the file imported. So each time they import a file, a new table is created. Is there any code that I can run that can automatically delete these tables upon exit?
I'm just tidying up really.
Thanks in Advance.
I am using the following query to identify rows in one table that have no match in another:
SELECT MAS.MASID
FROM MAS LEFT JOIN IVT ON MAS.Field1=IVT.Field1
WHERE ((IVT.IVTID) Is Null);
MAS is one file with MASID as the key.
IVT is the other file with IVTID as the key
This provides me a list of the rows in MAS with no match in IVT.
I now want to delete these rows in MAS so we can process the other fields in the table.
what is the syntax for the delete statement? I can't get the join to fit in correctly.
Help!
I routinely import from an Excel document provided by a third party. Most data in it is ok, but 10-15 records always end up in the import error tables which Access creates automatically when formats do not match.
I am trying to automate this whole process thru VBA and am pretty successful so far, except that I haven't figured out how to programatically get rid of these tables (I do not need them, and data imports anyway).
Is there a way to destroy a table programatically in Access?
Thanks
If a user wishes to delete a record, rather than deleting the record i'd like to change its status to not current for instance.
The reason being that the information in the records may be useful for statistical use.
I added a yes/no field to the table and then added some code to a delete button to change the yes/no.
I can't seem to hide these records from my forms (using filter) but I could be going the wrong way about it.
i have a data base with a primary key but some records have been added from a excel data base and now the data base has duplicate records with different primary keys. I do a Query looking for duplicate records based on a field not the the primary key. There are 315 out of 22000. I can sort the table and then manually delete the dups. The Query does not allow deleting records so finding the culprits in the full Data base is time consuming. How can i delete the duplicate records using a query? Is this function available hidden someplace in the guts of Access? Seems like this would be a frequent problem. The Microsoft forum addresses this problem and the only viable solution is the manual method, when they get to the Query method it looks like they just forgot what the subject was and tell you how to delete one record if you know what the criteria is. If two non Key fields are Identical then i want to delete one of the records.
View 3 Replies View RelatedI'm running MS Access 2007. I'm creating an import procedure (for 2003 and 2007) that will automatically import an EXCEL file into a table for MS Access 2003 and 2007. The automatic import procedure works fine, but everytime the procedure is ran it creates an 'ImportError' table, due to blank rows (i.e. 'Type Conversion Failure'). The blank rows are due to the source EXCEL file not having value in a cells. This is normal for the source EXCEL file. These 'ImportError' tables are numbered each time this procedure is ran. I added a 'DoCmd' (see below for statement) statement in the procedure delete these tables, The below statements are used in a loop. I see that there are several procedures out there that use 'DAO' to accomplish this. If possible, I would like to use something that will use the below 'DoCmd' statements. This will eliminate the need to touch each PC to set 'ADO' or 'DAO' on.
DeleteTblCnt = DeleteTblCnt + 1
DoCmd.DeleteObject acTable, "Import Data_ImportErrors"
DoCmd.DeleteObject acTable, "Import Data_ImportErrors" & DeleteTblCnt
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 RelatedHi all,
Im trying to write a query so that all data in all but two tables is deleted, not the tables themselves only the data.
I keep getting these errors when im trying to build a suitable query..
COULD NOT DELETE FROM SPECIFIED TABLES
or
TOO MANY FIELDS DEFINED
With any luck i'd like to able to have this query run automatically each day is there any way to do that or do i have to repost in vba section?
Thanks for reading!!
My table (excel spreadsheet) sits on another directory from the DB. If I delete the table on this directory and immediately replace it with a table with the same filename, format, etc (only the data has changed) will each database user have to relink the table on their desktop DB? Or will the forms/subforms/reports still maintain their relationships/functions and just display the new data?
View 2 Replies View RelatedI have a number of linked tables that vary depending on use. How to delete them or drop them all from the database? I produced/stole some code that deleted all tables - but it didn't delete the linked ones.
View 3 Replies View RelatedI have a crosstab table that is displaying in a form within a form because the form has data coming from multiple tables.
Crosstab Query looks like
Name Sum Cust1 Cust2 Cust3.....
Jon 50 250
Bill 100 50 100
Kevin 200 50 100
Ryan 10 500
The issue are the blanks in the rows for each Name. When I bring it up on the form, I want the columns to be the Customers but only customers with amounts. The original form is there to show the name and position and the form within the form should have 1 row for name of the person that is being selected and columns of customers WITH A BALANCE.
I have two tables.
1. Persons (list of persons)
2. Job history (list of jobs)
each person have their own job history. all these jobs are stored in the job history table. when i delete a person i would like the job history for this person deleted as well. each job stored in the job table have a field with person name, so that it is linked to this person.
how can i do this? vba or simple properties options?
- Roy
I have a problem about deleting record from a form, it will be grateful if anyone can help me with this.
Here are my tables:
Par_tor: some personal details such as ID, name, phone NO., etc
Par_tion: the record of who has participated which activities.
Activity: name of the activity.
My problem is: the main form of the my form contains the stuffs in table Par_tor, and the subform contains the stuffs in Par_tion, so the user can view these participator's details and the activities they have participated.
In the main form, I also wanted to created a delete button to delete an entire record (including a participator's detail + his/her participation record) at once, but it didn't work since the participation was in the Par_tion table not in the Par_tor.
So is there any way that I can delete these records at once?
Hi
I have a form with a "Clear Form" button on to allow the user to delete the record they are working on without updating the database. A question window pops up asking if they are sure they want to delete the record, if they answer yes the form will clear and re-load without updating the database.
The OnClick Event Procedure will not allow me to delete the data, it always saves. I've tried
DoCmd.SetWarnings False
If MsgBox("Are you sure you want to abandon changes to this record?", vbExclamation + vbYesNo, "LogBook 2002") = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
Else
DoCmd.SetWarnings True
End If
but I get the error "2046 - The command or action DeleteRecord isn't available now." Is there another way, a better way?
I have two tables (same data but slightly different attribute structure) with a one-to-one relationship (the join field is "ID"). There are 69 matching records in these tables. How can I delete these matching records from table A, while leaving them alone in table B?
I'm confused because I brought both tables into a select query, created a join from ID to ID (within in the query's design view), and then added all fields from table A to the query. I then ran the select query and saw the 69 records from table A in the query's data view that I wanted to delete, highlighted all records and clicked delete. However, this action deleted the 69 matching records from TABLE B, not Table A!!! How is this possible? What should I do instead? Thanks.
A strange request but I hope someone can help with this one
I have a table (tbl_Econ) where I have to delete a specified number of records from a table. It does not matter which records as long as I delete the exact number
e.g On a form text box I enter the number or records to be deleted (e.g.6000).
The table (tbl_Econ) has 8000 records, so I have to delete 6000 records. I need to be able to do this automatically :eek:
How can I go about deleting records fully? So that there autonumber, etc can be used again without access moaning.
Thanks :D
I have a query (Query1) which returns results In one field recordID.
How do I delete records in a table where the RecordID is in the list Query1.recordID?
Thanks
Hi All,
I have a very simple name and address DB. What I would like to know is "What is the easiest way of finding a record using the switchboard and then deleting that record from the DB?"
I am an Access novice so nice easy replies would be appreciated.
Many Thanks
Keith:o