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
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'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
My 2nd post, and I am very new to DB and Access. I have a problem that I want to get help on. I want to set up a main form that is used to enter and delete all data for my table. I wish to add either a button or to make it automatically happen when a record is deleted, that it is first copied to a separate table with the same fields, except it also has a closed date that would be the date that the record was copied over. I know zilch about VB, VBA or any other language other than AutoIt, so assume I am what I am, an ignorant beginner.
I did look into the event somethihng like upondeletion or something... while trying to find help on this in the access and VBA parts of Office, but I do not know how to utilize the event with Basic or SQL, which I know none of either.
Any help or examples are very much appreciated.
**EDIT** I do not require all fields to be recorded to the secondary DB (History), so if someone can just give me an example of how I would move two fields to a separate DB, I can hopefully learn enough from it to do more.
Thanks a Bunch!
EXAMPLE** Current Loans (Table 1): CustomerID Name Address City State Phone
Customer History (Table 2): CustomerID Name Phone
That gives an example to help understand what I need. I want to store the CustomerID, Name and Phone values of the record being deleted, to the History Table, which I am using as a closed account table for later look up.
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 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 having trouble executing a SQL command in VB... I want it to find the the value of the input box in TBL-Purchases and Delete all related values. Here's my code.... I get an error on the line I've highlighted in green...
Private Sub Command31_Click() Dim Message, Title, Default, MyValue1, MyValue2 Title = "Sell Stocks" Default = "" MyValue1 = InputBox("Which stock ticker name would you like to sell?") MyValue1 = UCase(MyValue1)
I have a table whose key is two fields. I am having no luck in selecting a given record from a form and deleting it. No problem in a table with a single field key.
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 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 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 have 2 tables. 1- customers table with 2 fields : customername,customerno 2-conversationstable with 4 fields: date,customername,customerno,details
The conversations table is for keeping memo of telephone conversations with the customers.
I built a simple form deriving from the conversations table. And added to it a combobox with 2 columns from customers table to select the customername an customerno for the form.
While deleting the record in the conversations table,through the form, I saw that the customername and customerno in the customers table of that particular customer record are deleted also. I made no links between both tables.
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.)
I'm trying to create a right-click event on a listbox that will copy selected listbox item(s) to a temp table. So far, I've got this code to acknowledge the right click:
Code: Private Sub List0_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single) If Button = acRightButton Then MsgBox "You pressed the right button." End If End Sub
Problem is the selected item on the list box doesn't move until after the mouse down event so whatever code I would run would involve the wrong record(s).
I'm using Access 2000 and 2003. How to get the the correct record selected on mouse down, or point me to a working example of right-click functionality on a listbox.
Can I add a delete button at the bottom of my form to allow me to delete a record or do i have to go to that table and delete it from there. if so how do i proceed with this.
Hey all, I was thiking can i make a blank query and then on my filtered form. Pass the data from the selected fields(VIA a ceckbox) and then display a report based on that query? Then have the query cleared for the next time?? Is this possible? Thanks in advance for any help!
