I have attached an image of the 2 tables concerning my question.
The main "transaction" table is the tblAssessments and a linked table tblRisks drives a subform - showing multiple Risks per assessment.
The field "OccupantID" identifies the facility where Assessments are done (there are other tables, of course).
I am using an OccupantID "00000" to store templates - pre-filled assessments with most common options selected.
I am trying to develop code and/or query or SQL that would do this:
copy all templates (records from OccupantID 00000) and corresponding sub-records from tblRisks into the same tables, but under a different (selected by user) OccupantID.
I have no problem just using an Append Query (actually a SQL statement in VBA with variable parameters), but that only lets me copy into 1 table - so I can copy just the tblAssessments records.
but how do I then copy the tblRisks related records and make sure I attach them to the correct AssessmentID?
I have an Access database with linked tables from Sybase SQL Anywhere, via an ODBC data source. I have just linked the tables recently, all the data used to be in native Access tables. It is fairly common for members of our team to open these tables in datasheet view, and copy/paste several records to create new records, then edit a few fields on the new records. This used to work fine with native Access tables, but when we try it with the linked tables we either get an ODBC error - "primary key value already exists" or the new records show up with #Deleted.
The root of the problem is this: In the old Access tables, the primary key was an autonumber field, and Access was smart enough to assign new ID's when you copy/pasted records. In the linked table, the primary key is type "Number" in Access, and Access is not smart enough to let Sybase assign new ID's when you copy/paste records in datasheet view. Access is trying to force the existing ID's into the primary key field, and Sybase says "too bad so sad".
Of course, I could just write some quick append queries to copy/paste the data. And in the short term, that's exactly what I will have to do. But is there any way, long term, to allow members of my team to do it the "quick & dirty" way by copy/pasting in datasheet view? I have tried to change the primary key to an autonumber field in design view, but Access doesn't allow that. Is there a way to do it in code, or a way to force Access to allow Sybase to always handle the primary key field?
Please can you help me find the issue to my problem that is probably hitting me in the face.
I have an unbound form to which i use an append query to post the data from the fields on the form to a table. The table is set up very simple and has a primary key which i will never duplicate as you would expect.
Once i have inputted one lot of data into the form the command button runs the query and the data is posted to the table fine. The problem i am having is that the second lot of information i go to append the query refuses to add. I get the validation rule violations error and it won't post the data.
If i close out of the form, open it again and input the same information the query adds it fine and again the second lot you get the error message.
i had this problem before but for the love of me cannot remember how i resolved it. I have had to keep closing the form after each entry to post the information.
hi Guys, I have been looking at different post and checking Microsoft help files as well, but still can't seem to fix this problem.
I am having 2 tables. The first table is connected to a form for viewing and entering data, and in the second table i am just copying 3-4 fields from the first table.
I am trying to use the insert statement to insert records in the second table, and everytime i click on the "Add" button to add the records i get the following error "MS access can't append all the records in the append query ... blah blah blah"
However if i close the form and reopen it, and goto the record (as it is saved in the first database) and now click on the add button to add the fields to the second table/database, it works.
I am rebuilding an application for a client and I have an Access table that I am using as a temporary table. Once the user is done entering information into the temporary table through a form, the user presses an update button that appends the records using an Append Query in Access to an SQL Server Table.
The following error message occurs:
"ODBC -- insert on a linked table 'linked tblname' failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] Explicit value must be specified for identity column in table 'linked tblname' when IDENTITY INSERT is set to ON. (#545)
I am using a form, subform combination to record a bill with many details. The bill summary is posted into a tbl_TransactionsMain table in SQL Server using the ADO AddNew method. The PK for the tbl_TransactionsMain is then entered into the temporary table in Access. When the temporary table records are appended into tbl_TransactionDetail the error message occurs.
What is also interesting is while typing out this post I thought to test the error by manually trying to run the query. The query worked like a charm! :confused: When the orginal error occurred off of the form I tried to run the query manually and it failed. I am guessing that this might have something to do with the ODBC timeout.
I think SQL Server/ODBC connection is not liking how I have a set of records in an Access table with foreign key numbers assigned when I am attempting to append the records. I am new to SQL Server and any ideas are most appreciated! :)
So looking at the tables, C1 = 2 C2 = 4+6 = 10 B2 = C1 + C2 + D4 = 20 A1 = 20 + 10 = 30
Here, there are 4 levels that I have to go into to get the grand total. If I did not know how many levels there were, how would I be able to do this through code? through queries if possible??
If anyone could help, that would be great! Thank you in advance.
I have followed the development of Access from the early beginning, and even before the early beginning with databases like Q&A, Foxbase etc. I have survived with Access 2002 for a long time. However, the unavoidable string of events has given us more and more complex systems (for the noble purpose of making life easier, I assume). In my particular case I now have an up-to-date system (Access 2010) that I do no handle. My impression is that also the experts have trouble. I am now close to discard Access 2010 as too complex for my simple needs.
Here follows an example of a database that was easy to construct in Access 2002 but which I have major problems to reconstruct in Access 2010: The database in question shall keep track of expenses. The database have fields like date, project, type of merchandise, description of merchandise, price, name of store, notes, hyperlinks.
On opening the database I want a search screen (a form) that is exactly identical to the records I am going to produce (except that all fields are empty). On the search screen I put a search topic (e.g. the name of a store), and then initiate the search by pressing a fast key. The search result generates a number of records, I move on to the last one, I copy it, and on the copied record I do the necessary adjustments. The whole operation is done in a matter of seconds. The procedure is functional, and it gives me what I want.
I have made some "work around" procedures that bring me to the final touch, namely to copy/paste the record.
I need help with ACCESS query. I am not at all good with access queries and am unable to figure this out even though it may have an easy solution.
There is an existing query designed by our company in a database. This query links different fields from 4 more queries and finally generates 16 columns as shown below. (I have also attached excel file with the same example as the text below is not aligned)
In this example I am just showing 8 records, but in reality there usually about 4000 records and the number changes every day.
Now I am asked to design a query based on the query that gives the above results.
The 14th column (heading –MV by Fund code) actually sums up the market values in column 12 (BMV) that has same fund code in column 15. For example for fund code “abcd” the BMV are $150, $256 and $325 which sum upto $731 and hence this number shows up in column 14 against each record with “abcd” fund code.
Now there is a difference of $14 between column 15 (heading-Total TNAD) and column 14 (heading- MV by Fund Code) for fund code “abcd”. I am asked to design a query that will add records on the results generated above by the existing query.
The new record added should show the difference of $14 in column 12 and text “OTH” column 8 and column 9 and should show the fund code “abcd” . The values in remaining columns should be the same as the fund code “abcd”.
The above logic would then apply to funds “wxyz”, “pqrs” and so on
In this example the following three records should be added below the above 8 records with these details
My user opens frm_ENTRY. Which is pulling data from tbl_TEMP. They clean up a few fields on the form. Then click a button which executes an append query which appends the data to tbl_MAIN.
My problem is sometimes when it appends the data it is overriding older data. It is not creating a new line each time. It simply overrides an existing record. Does this make any sense at all?
There are many other forms, queries, tables and macros in this DB. If that matters at all. I think it has something to do with a bound form that is connected to a drop down from a query, but I have no way of trouble shooting this? Where do I start?
Thanks for taking the time to look at my problem first of all. I have a form that shows records but I want a button next to each record that will append that one single record to another table that is built exactly the same, which is for historic records. Not the brightest on Access and I can only manage to append all records.
As an example, I have an employee record that have a task, when the task is complete I want to append the record to a historic table that I can look up later on. However, just that one employee and not all that are in the current table.
How can I modify the below code so that it only adds new records to AttributesTBL from ProductInformation-Consumer?
INSERT INTO AttributesTBL ( Material ) SELECT [ProductInformation-Consumer].Material FROM AttributesTBL INNER JOIN [ProductInformation-Consumer] ON AttributesTBL.Material = [ProductInformation-Consumer].Material;
Can anyone help me with this - I have an append query to a statement table for invoices with a macro set. When the query is run the data is added to the statement query.
what is happening is that it is adding the data ok on a seaparate line but is also updating any previous invoices for the same client to the new invoice number obviously not what I want.
I have an order system whereby there is a "basket" table and an order detail table.
I want to use an append query to move all the records from the basket table to the order detail table.
However, I also need to mark each record that gets appended to the order detail table with an "Order Id" that has already been saved in an Order table.
I.e. I save the main details of an order into the order table then copy records from the basket table into the order detail table along with an order ID that comes from a form.
I'm using an append query that needs to add the records from another database into a table. I can get it to get the data and add the records. There are other columns in the database that are not in the one the data is pulled from. If I would run the append query again, it would add the same records again at the end. How can I avoid this? I only need to add new records that are not yet in the database I want to pull the records to.
I want to 'save' certain emails from a linked inbox folder. the folder takes its info from a IMAP email account and I want to be able to archive old messages from this to the database so the are not removed from the database if they are deleted from the IMAP account.So here's what I have got:
Code: strSQL = "INSERT INTO SavedEmails ([Sender Name], [Subject], [Received], [Content]) SELECT [Sender Name], [Subject], [Received], [Contents] FROM Inbox WHERE [Sender Name] ='" & Forms![assignEmail].OpenArgs & "'"
When I execute this code, it gives me an error: Runtime Error 3001 - invalid argument. When I select debug it just points to the execution of the SQL and gives me no further information. The openArgs for the form is basically just a name
I have an append query that everytime it runs it will simply duplicate the records. ex.. sample table has 11 records if you run it again the tableB will now have 22 records. I need it to only update the records, so if someone makes in change / new record in tableA it will only update the records in TableB. I have the Primary Key set to Yes no duplicates. TableA Primary Key is ID TableB Primary Key is CustomerID
So, I've been searching through this forum and can't seem to find the answer to this one. I would like to capture a value from a main form and have it used as a value in an append query, in order to populate a subform based on the main form, like so:
INSERT INTO tblTakenSurveys ( VisitID, SurveyQuestionID, ResponseCodeID) SELECT Forms![frmMyFormName]!VisitID, tblSurveyQuestions.SurveyQuestionID, 66 FROM SurveyQuestions WHERE SurveyID = 3;
Might help to explain some of the terms in this statement: tblTakenSurveys is where I need the new data to be entered via the subform. Forms![frmMyFormName]!VisitID is a textbox control bound to a PK in another table that has a one-to-many relationship with tblTakenSurveys. tblTakenSurveys.ResponseCodeID is a foreign key that represents respondents actual answers to questions. 66 is a value for a ResponseCodeID that stands for a dummy value meaning "data not yet entered"
As per advice I received from others on this forum, I have set a query like the above to run from a command button to populate the subform (in theory). But I'm sure I've done something wrong within the query because it will not return a value from the form "VisitID" control and therefore will not append the rows. Without the appended rows, my subform will not populate. And this has me running in circles...
I pasted the link to another thread below, where I originally received a lot of input as far as the table structure. I did not start this one, but my posts are the most recent (as of now anyway). Pat Hartman had given me a lot of the guidance here.
- I have a module which runs queries on linked sales spreadsheets, to merge them in to one Access table. - To prevent duplication of sales, the primary key merges the sales record and item number fields.
Today, it's found 103 key duplication errors, which is fine. But it's still adding data to the table. The data seems to be fields which aren't even mentioned in the query. It only does this when the query is ran from VBA.
I am creating a database where the records of individual students are to be moved to different table depending on where they are in the graduation process. In order to do this, the secretary will enter the student in the "90 Hr Request" table (think of it as the first step), and move them down the line of tables (4 in total) until the final "Completed" table. Each table in the progression has more and more fields. However, a student (with their ID number as the primary key) can only be in one table at a time.
I understand this does not sound like a traditional database, as the data is not normalized. However, this is being used more as a filing system than anything else. Their data is primarily paper based (for legality reasons), but wishes to keep it organized in a database so they can run queries and print reports.
Currently, I am creating a macro that will run specific queries (in an order). I have made an append query that will move the records over, then I used a update (to null) and delete query combo to delete the old individual record. I made the delete + update query work by using a selected criteria.
How to make the append query move only one student's record at a time.
I want to copy three of my tables from the back end to another database (backup) at regular intervals. When using docmd.CopyObject and docmd.TransferDatabase it is the link that get copied (naturally) and not the table itself.
I have created a linked Excel table in Access 2010 called 'tblExcelLinked' and I have a form called 'ASB Log Form' for the purposes of presenting the data in a more readable manner that is easier to view, plus link other fields of data that are not directly related to the 'tblExcelLinked'.
Because there is no unique ID in the 'tblExcelLinked' to create a relationship, I have created a table called 'tblASB', which allows me to add other table data linked from same d/b.
I now want to update the 'tblASB' with data from the 'tblExcelLinked', but only append new records from 'tblExcelLinked', but my inadequate append query is duplicating the records each time I run it, rather than just adding the new ones.
Once sorted my next challenge is a macro so that this runs automatically rather than being manually triggered.
I have an MS Access accdb with linked SQL Server 2012 ODBC tables. I am working on a procedure to copy data from local tables to these linked tables (identical schema). I did a simple
Code:
DoCmd.RunSQL "INSERT INTO linkedTable SELECT * FROM localTable"
This works, but is very slow. Way too slow. (INSERT copies the data one record at a time).
I would like to copy the data in a bulk operation, or operations that I can execute programmatically.
I have two tables, submit and imgdest. Submit is edited by front-end users to load pictures for back-end users who then delete the images when they're done with them. Submit is edited by a form, in the form I've placed a button (Command37) that has code:
Code: Private Sub Command37_Click() Call InsertData MsgBox "Completed", "0", "Completed Backup" Exit Sub End Sub Private Sub InsertData()
[code]....
This code was working for a short while, now anytime it's run I get error 3027 - Cannot update. Database or object is read-only. However, I can open the linked table and manually change information in it with no problems.
Well, heres the situation. I have a complicated query that refuses to work all the time using a linked table for the data (data is gathered from a FoxPro DB). If I copy the data into a local table in my database then the query will run fine.
The data needs to be updated only 1/month but I don't want to have to do it manually every month. I would like to use VBA to copy and paste the data from the linked table into my local table. Does anyone know of an efficient way to do this? I'm trying to avoid running a VBA loop and adding each record one-by-one (very slow).
