Can Delete Tables Via VBA Code In Runtime Environment
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.
I am currently using the SetValue function from within a macro to set the value of one text box to the value of another. It works fine under full version but when I use it under Runtime which is where the final application will be run from, it brings up the Halt box and will not continue the macro. Does anyone know what can/cannot be done within Runtime. Is it just the same as A full version but without Design functions or are some other functions removed. I am painstakingly having to go through all the vba code that I wrote in full version and I am having to change so many things in order for it to work with Runtime. This is a real pain the backside! Any thoughts would be huuuugely appreciated.
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:
I have this code on the a Close Button and if the record is incomplete I want it to be deleted from the table. The ID field is an autonumber so it is the 1st field to get a value if a user starts entering data. I get a msg box saying 0 fileds will be deleted why? the id's do match but it does not delete??
I have a table that has 1335 records in, and columns names, Title, First Name, Surname, Address, Address2, Town/City, County, Postcode.I want to know how to write a SQL code, which will locate all the duplicates that match the First Name, Surname and Postcode and delete them, but keeping 1 copy of the duplicate.I have created a query to locate the duplicate records, and I have put the SQL code there. I tried changing this, however it would delete all the records, so I am not to sure what to try...
SELECT SurnameSearch.Title, SurnameSearch.[First Name], SurnameSearch.Surname, SurnameSearch.Address, SurnameSearch.Address2, SurnameSearch.[Town/City], SurnameSearch.County, SurnameSearch.Postcode FROM SurnameSearch WHERE (((SurnameSearch.[First Name]) In (SELECT [First Name] FROM [SurnameSearch] As Tmp GROUP BY [First Name],[Surname],[Postcode] HAVING Count(*)>1 And [Surname] = [SurnameSearch].[Surname] And [Postcode] = [SurnameSearch].[Postcode]))) ORDER BY SurnameSearch.[First Name], SurnameSearch.Surname, SurnameSearch.Postcode;
Some times, the system become stuck, which forces the user to end the program using Windows Alt+Ctrl+Delete. I have MS Access code which I want to execute when the user made the 'End Program' event using the Alt+Ctrl+Delete.
I am trying to delete a record in tblinclude where record from tblexclude are equal to clientid and codeid
Here is the sql DELETE tblinclude.ClientID FROM tblexclude INNER JOIN tblinclude ON (tblexclude.ClientID = tblinclude.ClientID) AND (tblexclude.CodeID = tblinclude.CodeID) WHERE (((tblinclude.ClientID)=1));
I get the error Specify the table containing the records you want to delete. I've searched for this but I am just not getting it today.
I have a table called "EquipmentRequired" which is populated by 4 append queries,
5000BaseReq, 6000BaseReq, 6000IFBBReq, EquipmentReq which get some of their information by counting fields in another table but all have the same field names.
The queries contain all data that is initially used to append new records to the table and this works fine.Unless some information changes or a record is added then I would like to add a button to a form and call it "update equipment" behind which would run a vba code firstly to delete all the records in "EquipmentRequired" table then run the 4 queries without the warnings and re-populate the table.
I can open up my tables using the code below in a distributable runtime version, but I cannot filter and sort in the table. Right clicking does nothing.
Dim strTableName strTableName = Nz(Me.cboTableNames, "") DoCmd.OpenTable TableName:=strTableName, View:=acViewNormal, datamode:=acEdit
I know with a form we can select to allow filters in the form properties, but when the OpenTable command is used, how can we allow for filtering?
Is it possible to open the tables in a generic form that allows for filtering?
This is one of this only times i've had a problem whose answer i couldn't find on this or other forums, so here's the question.
My DBase links to the back end tables tblBrkdn tblBrkdnArchive tblBrkdwnTradespeople tblBrkdwnTradespeopleArchive
The following two tables are local, not linked tblBrkdnArchiveTemp tblBrkdwnTradespeopleArchiveTemp
After running through the following code, all of the four above mentioned linked tables are now local. It's vexing. I'm terribly vexed. If you can help, my status would chnge from vexed to joyous....:
Thx in advance...
' 1) Join all together (Each Table) ' Create two recordsets, One for the Brkdn set, one for the Tradespeople Set. ' Brkdn: Want it to be the Union of all records in (tblBrkdn, tblBrkdnArchive) without duplicating BrkdwnID 'Standard Union Query with no duplicates. Transferring data into a temporary table SQLStr = "SELECT * INTO tblBrkdnArchiveTemp FROM (" SQLStr = SQLStr & "SELECT tblBrkdn.* FROM tblBrkdn INNER JOIN tblBrkdnArchive ON tblBrkdn.BrkdwnID = tblBrkdnArchive.BrkdwnID " SQLStr = SQLStr & "Union ALL " SQLStr = SQLStr & "SELECT tblBrkdn.* " SQLStr = SQLStr & "FROM tblBrkdn LEFT JOIN tblBrkdnArchive ON tblBrkdn.BrkdwnID = tblBrkdnArchive.BrkdwnID " SQLStr = SQLStr & "WHERE (((tblBrkdnArchive.BrkdwnID) Is Null))" SQLStr = SQLStr & "UNION ALL SELECT tblBrkdnArchive.* " SQLStr = SQLStr & "FROM tblBrkdn RIGHT JOIN tblBrkdnArchive ON tblBrkdn.BrkdwnID = tblBrkdnArchive.BrkdwnID " SQLStr = SQLStr & "WHERE tblBrkdn.BrkdwnID is null);" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
' BrkdnTradespeople: Want it to be the Union of all records in (tblBrkdwnTradespeople, tblBrkdwnTradespeopleArchive) without duplicating ID 'Standard Union Query with no duplicates. Transferring data into a temporary table SQLStr = "SELECT * INTO tblBrkdwnTradespeopleArchiveTemp FROM (" SQLStr = SQLStr & "SELECT tblBrkdwnTradespeople.* FROM tblBrkdwnTradespeople INNER JOIN tblBrkdwnTradespeopleArchive ON tblBrkdwnTradespeople.ID = tblBrkdwnTradespeopleArchive.ID " SQLStr = SQLStr & "Union ALL " SQLStr = SQLStr & "SELECT tblBrkdwnTradespeople.* " SQLStr = SQLStr & "FROM tblBrkdwnTradespeople LEFT JOIN tblBrkdwnTradespeopleArchive ON tblBrkdwnTradespeople.ID = tblBrkdwnTradespeopleArchive.ID " SQLStr = SQLStr & "WHERE (((tblBrkdwnTradespeopleArchive.ID) Is Null))" SQLStr = SQLStr & "UNION ALL SELECT tblBrkdwnTradespeopleArchive.* " SQLStr = SQLStr & "FROM tblBrkdwnTradespeople RIGHT JOIN tblBrkdwnTradespeopleArchive ON tblBrkdwnTradespeople.ID = tblBrkdwnTradespeopleArchive.ID " SQLStr = SQLStr & "WHERE tblBrkdwnTradespeople.ID is null);" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
'delete everything from the four non-temporary tables 'tblBrkdn SQLStr = "DELETE * FROM tblBrkdn" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
' 2) Paste the entire record set to each Brkdn table 'tblBrkdn SQLStr = "SELECT * INTO tblBrkdn FROM tblBrkdnArchiveTemp" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
'tblBrkdnArchive SQLStr = "SELECT * INTO tblBrkdnArchive FROM tblBrkdnArchiveTemp" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
' 3) Delete from Active & Archive Brkdn tables WHERE [DATE] < txtArchiveDate.Text 'tblBrkdn SQLStr = "DELETE * FROM tblBrkdn WHERE ([DATE]<#" & dateStr & "#);" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
'tblBrkdnArchive SQLStr = "DELETE * FROM tblBrkdnArchive WHERE ([DATE]>=#" & dateStr & "#);" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
' 4) Paste to Active Tradespeople table WHERE Associated Breakdown Record [DATE] >= txtArchiveBeforeDate.text SQLStr = "SELECT tblBrkdwnTradespeopleArchiveTemp.* INTO tblBrkdwnTradespeople FROM tblBrkdwnTradespeopleArchiveTemp INNER JOIN tblBrkdn ON (tblBrkdwnTradespeopleArchiveTemp.BrkdwnID=tblBrkd n.BrkdwnID)" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
' 5) Paste to Archive Tradespeople table WHERE Associated Breakdown Record [DATE] < txtArchiveBeforeDate.text SQLStr = "SELECT tblBrkdwnTradespeopleArchiveTemp.* INTO tblBrkdwnTradespeopleArchive FROM tblBrkdwnTradespeopleArchiveTemp INNER JOIN tblBrkdnArchive ON (tblBrkdwnTradespeopleArchiveTemp.BrkdwnID=tblBrkd nArchive.BrkdwnID)" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True ' 6) Clean up: Delete all records from the temporary tables 'tblBrkdnArchiveTemp SQLStr = "DELETE * FROM tblBrkdnArchiveTemp;" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
My office recently purchased Microsoft Access for our secretary to keep track of our contacts. No other employee owns Access so we have to distribute the tables through Access Runtime. However, there seems to be a problem.
I save and publish a database using the Packaging Wizard. I then send the associated folder with the install program to a computer which has Access Runtime installed on it. But, when I install the file I created with Packaging Wizard it just opens an Access window that is completely blank. It's actually the same thing that occurs when I try to open an Access file on a computer that doesn't have Access or Runtime installed on it at all.
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.
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.
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!
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.
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.
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.
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?
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.
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
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?
