Stored Query in Access
-
I am trying to prevent duplicate records from being appended to my Access database. What I did was created two tables in Access. The first one "NewResultsTable" is populated with new records. I created a Query in Access "Query1" which appends the first database to the second database. The Table "NewResultsTable" is then cleared of all of its records. The table "TestResultsTable" has three primary keys:TestDate, TestTime, and SerialNumber. So when I run "Query1" while in Access, if all three of those primary keys are the same as any other three primary keys I will get an error message similar to the following. "Microsoft Access can't append all the records in the append query Microsoft Access didn't add (Number of records) to the table due to key violations To ignore the error and run the query, click Yes" I click yes the records that aren't duplicates are appended to "TestResultsTable". I wrote a small test program in C# and called the "Query1". When there where no duplicates the "TestResultsTable" is appended to no problem. When there are duplicates I get the following error. "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again." I don't know how to tell Access to continue and run the query. How can I do this? Or would it be easier to write a SQL statement? What would the SQL statement be that would do this same thing? Thanks to anyone who can help.
-
I am trying to prevent duplicate records from being appended to my Access database. What I did was created two tables in Access. The first one "NewResultsTable" is populated with new records. I created a Query in Access "Query1" which appends the first database to the second database. The Table "NewResultsTable" is then cleared of all of its records. The table "TestResultsTable" has three primary keys:TestDate, TestTime, and SerialNumber. So when I run "Query1" while in Access, if all three of those primary keys are the same as any other three primary keys I will get an error message similar to the following. "Microsoft Access can't append all the records in the append query Microsoft Access didn't add (Number of records) to the table due to key violations To ignore the error and run the query, click Yes" I click yes the records that aren't duplicates are appended to "TestResultsTable". I wrote a small test program in C# and called the "Query1". When there where no duplicates the "TestResultsTable" is appended to no problem. When there are duplicates I get the following error. "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again." I don't know how to tell Access to continue and run the query. How can I do this? Or would it be easier to write a SQL statement? What would the SQL statement be that would do this same thing? Thanks to anyone who can help.
Why don't you write a query that doesn't cause the error ? Ideally you should not send duplicate data to the database, but your proc for doing an insert should first check if the unique ID exists, and perhaps if it does, do an update instead. Christian Graus - Microsoft MVP - C++