Modules & VBA :: Updating Master Table From Temp Table
Jun 5, 2013
I have two tables that are formatted identically....
Table1 = MasterTable
Table2 = TempTable
I am taking a copy of one of the entry from the master table and Copying it to the temp table.I then open a form on the TempTable that enables the user to modify the content without affecting the information in the MasterTable.On Completion and Save I wish to take this modified content and update the MasterTable Using the Field "ID"
the tables for example look like this
ID NAME AGE DATE1 DATE2
I am guessing I need to use a
Code:
DoCmd.RunSQL UPDATE "MasterTable" WHERE "TempTable"
Type of command but not to sure on the correct syntax and as everything is the same the use of wildcards for all fields
I'm trying to copy the structure of a table to make a temp table. I'm using CopyObject (which also copies the data). So when I delete the data from the temp table, it also deletes data from the source table. Is the data linked? It should just be deleted from the temp table. Below is the beginning of the code. I've stepped through, and at the last step shown, the data in the source table deletes.
Code: Dim strFile As String Dim temp As String Dim tbl As String Dim db As DAO.Database
I have some code that creates a table based off another table. This code works perfect but Im trying to alter my ID field and change it to an auto increment. How can I fix my code so that it alters my ID field?
Code: Private Sub Command0_Click() 'OBJECTIVE: to build a table by extracting some fields from a main database Dim rst As Recordset Dim strSQL As String Dim strSQL2 As String Dim intCount As Integer
I am trying to write a VBA Module that will look in a directory for Excel files with a specific file prefix, and then import specified cells into a temp table. Once it processes the first file, it copies the file to another folder, and then kills the file. However, when I run my current code, it goes through the loop the first time, but fails on the second attempt, because it is still looking for the first file it came across even though it has been moved. The code is pasted below:
Sub Count()
Dim xlwrksht As Excel.Worksheet Dim xlWrkBk As Excel.Workbook Dim nIndex As Integer Dim strMvPath As String Dim mvPath As String Dim strFile As String
I am attempting to insert a record with selected data into a temp table and I am getting "Run-time error '3075': Syntax error in (comma)...". Here is the code:
Code: Private Sub XferDataToTempTable() Dim db As Database Dim strSQL As String Set db = CurrentDb
I need to extract a specific number of records into a table using a MakeTable or Append command using a temp variable, e.g. TempK&SA. Previously on the forum I was shown how code could be added to the OnOpen function to use a temp variable to select a specific number of records to report. ACCESS does not have the OnOpen function in the design view of a query like in the report. It does allow a SELECT TOP but only with fixed variables or percents (e.g. 25 in the code below).
The beginning code for the make table query (where 25 is the number of records added) is:
INSERT INTO [Output] ( RndNo, PointBiserial, BloomsTax, DateRevised, Exam1, Status, Exam2, Exam3, Exam4, [NCCPAKnowledge&Skills] ) SELECT TOP 25 TestBank.RndNo, TestBank.PointBiserial, TestBank.BloomsTax, TestBank.DateRevised, TestBank.Exam1, TestBank.Status, TestBank.Exam2, TestBank.Exam3, TestBank.Exam4, TestBank.[NCCPAKnowledge&Skills], * FROM TestBank WHERE (((TestBank.PointBiserial) Is Null Or (TestBank.PointBiserial) Between [TempVars]![TempPointBiserialLow] And .....
how to modify the code to allow a temp variable to determine the number of records to append to another table would be gratefully received. (This process then is repeated for a total of 7 append tables with different temp variables.)
Hoping someone can help me with this DELETE query. I have a Main table that's being updated by a Temp table that's an exact copy of the Main table but with a subset of records.
1) Insert records from Temp table NOT found in the Main table - this query I have worked out below - not tested, but the results look correct.
Need Help Here... 2) Delete Records from the Main that are not found in Temp table with an exception...only DELETE records where certain key fields are matching. i.e. If S.CAD_NAME, lngStoreNumber are a match to what's in the Main table. While Temp table: lngStoreNumber - CAD_NAME - lngcomponentSerial 1 - "CHK" - a 1 - "STK" - a 2 - "CHK" - a
Main table lngStoreNumber - CAD_NAME - lngcomponentSerial 1 - "CHK" - a - LEAVE (EXISTS In Both Tables) 1 - "CHK" - b - DELETE (lngStoreNumber & CAD_NAME composite Found /lngcomponentSerial NOT Found in Temp) 1 - "STK" - a - LEAVE (EXISTS In Both Tables) 1 - "RMM" - a - LEAVE (lngStoreNumber & CAD_NAME NOT Found in Temp) 2 - "STK" - a - LEAVE (lngStoreNumber & CAD_NAME NOT Found in Temp) 2 - "CHK" - b - DELETE (lngStoreNumber & CAD_NAME composite Found/lngcomponentSerial NOT Found in Temp) 3 - "CHK" - a - LEAVE (lngStoreNumber = 3 Not in Temp table Subset)
Rule: Only delete the records for a particular CAD_NAME and lngStoreNumber from the Main table leaving all other CAD_NAME/lngStoreNumbers.
I'm running these updates in batches of lngStoreNumber. So the Temp table will only contain subsets of what's to be deleted from the Main table thus the need to link on the key fields only NOT to delete a Subset of lngStoreNumber/CAD_NAME. I think I've tried every possible query that doesn't work.
Here is query #1 to insert records missing from the Main table that exist in the Temp table. I think what I need is a variation of this??? SELECT D.* FROM Main AS S RIGHT JOIN Temp AS D ON (S.CAD_NAME=D.CAD_NAME) AND (S.lngcomponentSerial=D.lngcomponentSerial) AND (S.lngStoreNumber=D.lngStoreNumber) WHERE S.lngcomponentSerial is null AND S.CAD_NAME is null AND S.lngStoreNumber is null;
I am trying to update a table with the value of a text box on the form where the table to update is as selected from a combo box on the form.I keep getting the following
Error message: Run-time error 2465 Microsoft Access cant find the field & table_to_update & referred to in your expression..
But really can't see what I've done wrong. Have checked that the table_to_update string does contain the name of the table so guess it must be sql..
Code: Private Sub Command91_Click() Dim table_to_update, sql_string As String table_to_update = Me.Combo49 Debug.Print table_to_update sql_string = "UPDATE [" & table_to_update & "] SET [" & table_to_update & "].[Project] = """ & Text89.Value & """ WHERE [" & table_to_update & "].[ID] = " & Forms![T_entity]![" & table_to_update & "]![ID] & "" db.Execute sql_string End Sub
I have three tables that contain different columns but linked by a primary column call Name. I want to create a table where all these different columns in the three tables join to form a master table which can be updated regularly either through the master table or the smaller tables. The master table also has the primary column as Name.
If I update the master table with records, it should update the respective linked table and vice versa. I also want to link these tables to my SharePoint site.
Note: except the Name column, none of these tables have any other columns in common How do I go about this?
I'm Access 2010 newbie. I need to transfer Excel program into Access.I have a .csv file (data extracted from emails) and a Master Excel sheet. Master Excel fields are updated from the .csv - if the primary keys match, else the new records are added. Also, the .csv contains colour names, which must be translated into corresponding peoples' names.
SELECT * from mastertable INNER JOIN secondarytable ON mastertable.Identicalfield = secondarytable.Identicalfield
But none of these do:
DELETE FROM mastertable INNER JOIN secondarytable ON mastertable.Identicalfield = secondarytable.Identicalfield **Incorrect syntax near the keyword 'INNER'.**
DELETE FROM mastertable JOIN secondarytable ON (mastertable.Identicalfield = secondarytable.Identicalfield) **Incorrect syntax near the keyword 'JOIN'.**
DELETE FROM mastertable WHERE (mastertable.Identicalfield = secondarytable.Identicalfield) **The column prefix 'secondarytable' does not match with a table name or alias name used in the query.**
DELETE FROM mastertable WHERE IN (SELECT * FROM secondarytable) **Incorrect syntax near the keyword 'IN'.**
Can anyone help? This should be easy, shouldn't it?! Thank you.
When you open the form, it asks for a tool # (which will be a drop-down box). There will be a command button that will bring up a sub-form. In the subform, there are 2 combo boxes. One picks the group that the tool falls under, and the other has the section of the group for the tool.
When the user picks the section, I need it to filter the questions that pertain to that section (which is listed in a list table), and pull those questions, and populate the master table with them. (i.e., Pulls "where is tool?" from tblQuestions, and puts it in the tblQuestionMaster when section is selected from combobox). :confused:
Is it possible to have a form that will filter my data to what i need, and place in a temperary table and then be able to display it in a report??? So I already have a filtered form, however I would like to be able to creat reports on the fly. So I will not need all my fields from my table everytime I filter. So If I create a report I can choose the fields that I need, however this is done will all records in my table and I would like to only use the data I have filters. What way would be best to accomplish this?? I already have the ability to load the report wizard by a command button. I just dont know the best way to use my filtered data with it. Any help would be great. thanks
Hellooo Hi gurus I have a smallish problem I have two tables that I need to join togther - normally no problem
I have one table with 1 event on it- easy however if I have more than 1 event on it I have another table that opens up and I add multiple evnets to it main id number 12345 with 1 event on it and 23456 may have 20 events on it on my other table (with multiples on it) i have this autonumbered (this is great unique id - now i need to make a temp table to include boths sets of data in one file
1 event table - easy multi event tabel I want it to get the main refernce fromt he first table (using 23456 as the example) and have it list these in the table - main refer 23456-multi table unique ref number 23456-1 , 23456-2
so my table should have 12345 23456-1 23456-2 23456-etc
I have been using append qry to make tables - any pointers would be great
I'm trying to create a simple 1 field temp table to populate a combo box with the name of the current user and the word "Company." However, after the user closes the form (or as soon as the Temp table is no longer necessary) I would like to delete the table. I can create the table, the fields, add the data, and populate the combo box just fine, but I'm having problems deleting the table after I'm done. I keep getting the error:
Run-Time Error 3211: The database engine could not lock table 'Temp' because it is already in use by another person or process.
here's my code:
Code:Option Compare DatabaseDim dbRoofing As DAO.Database Private Sub Form_Close()dbRoofing.TableDefs.Delete "Temp" 'where i get caught when i close the formEnd Sub Private Sub Form_Open(Cancel As Integer)Set dbRoofing = CurrentDb Dim tblTemp As TableDefDim rcdTemp As DAO.Recordset Set tblTemp = dbRoofing.CreateTableDef("Temp")tblTemp.Fields.Append tblTemp.CreateField("Owner", dbText)dbRoofing.TableDefs.Append tblTemp Set rcdTemp = dbRoofing.OpenRecordset("Temp", dbOpenDynaset)With rcdTemp.AddNew!Owner = CurrentUser.Update.AddNew!Owner = "Company".Update.CloseEnd WithOwner.RowSource = "SELECT Temp.Owner FROM Temp"End Sub
I have a form with around 10 checkboxes which serve as a filter option...now, when I hit my cmdFilter button it works well with a simple MsgBox !Ime showing all the filtered names...now, I want to put the results into a table tblTemp so that I could show the results in my subform. I've tried with making a sql string something like "INSERT INTO tblTemp..." but it's still empty.:confused: Since this table will serve as a one time data, I will need to delete all records when I hit the Filter button next time...so, how do I send my recordset data into my table.
I'd like to copy data from an excel spreadsheet and paste it into a temp table in Access and then hit a button which will run an append query and append all the data in the temp table to a permenant table.ow to create a temp table?
I am trying to create a table from a form. The form has several fields but I need to take the value from 4 separate combo boxes ([cr] +[br] +[tr] and add them, then add the value from one more combo box [inc] to be my beginning value in a table.
I then need to add the last value [inc] to the total and that become the next line in the table. I would the like to add this value [inc] an infinite number of times until it reaches a max number.
The scenario would be something like this cr=3 br=2 tr=3 inc=1.5
So the first total would be 9.5. Then every row after that would be plus 1.5 11 12.5 14 15.5 17 and so on.
This would be a temp table that I would run a query on to let an operator know lengths they can choose from in a combo box. I don't know if this is even possible.
All using access 2010. I have a multiuser database that I feel would benefit from splitting into a backend with multiple user frontends. My problem is that there are tables from make table queries processed every two weeks that all users need access to. As far as I know; you can not put a table in the backend that you will delete and remake or a temporary table and link to it in the front end. Is there any other way I would be able to split the database and have temporary tables linked from backend to frontend that I am not aware of? Is there another way to creating temporary tables and tables from make queries? This database has 9 users and counting and really needs to be split. r
I have a form where the user selects check boxes to choose what fields to include in a report. Because of the massive amount of data, I need to send it to a temp table and then on to Excel and not use a query.
I have this line of code, for the insert into temptables for other forms that don't require the yes/no box and it works very well, but I can't figure out how to do it with these yes/no conditions.
This is the execute line that inserts into the TempTable for the other forms:
How would I write: If Check1=True Then INSERT INTO TempPicktbl Field1 If Check 2=True Then INSERT INTO TempPicktbl Field2 If Check 3=True Then INSERT INTO TempPicktbl Field 3 etc.
I'm pretty sure it's the INSERT level where I need to put this code.
I am attempting to automate the import of a .csv file from a given directory and copy and append it to another table in a different layout. Below is my code. The error that i'm getting is it can't fine the copyobject in the database...
Option Compare Database Option Explicit Function DoImportandAppend() Dim strPathFile As String Dim strFile As String Dim strPath As String
I have a VBA script that looks at a date on a form, adds a certain number of months (selected by the user). at the moment i can output the new data to a message box. I want to update a table with the new data value.
I am trying to export into a temp table (all text fields because it will be going into a text export later) and I'm having difficulty adding 0:00:00 onto the value of "ApptdateLast" for the update...
INSERT INTO cbt_Export_Temp ( TransactionType, ID, ApptdateLast ) SELECT "Add" AS TransactionType, "BC" & [TransId] AS ID, dbo_Info.ApptdateLast & " 0:00:00" AS ApptdateLast FROM dbo_examInfo
I am trying to edit an existing record in my database.It goes to the correct record to update but will not update if "RecordUpdate" is specified as auto numbered.If the "RecordUpdate" is just specified as a number in the query it is fine.
