Using Transactions in MS Access VBA Issue
Posted on 2014-09-14
I have an issue concerning using transactions in VBA.
I am trying to surround 2 queries that modify an existing table (Table1) in a transaction.
Table1 has data already in it and Field1 is set to Indexed and No Duplicates.
My code looks something like this:
CurrentDB.Execute "DELETE * FROM Table1", dbFailOnError
CurrentDb.Execute "INSERT INTO Table1 (Field1, Field2) SELECT NewField1, NewField2 From Table2", dbFailOnError
I am trying to DELETE all the records in Table1 and repopulate the table using Table2.
However I keep getting 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."
What I don't understand is if the data is suppose to be completely deleted, why would there be duplicates? Btw there are no duplicate values in Table2 so that's not the issue.
Is it because I am using transactions and because of that it doesn't see that the records will be deleted before they'll be inserted?
I've been working with workspaces for a bit now but I keep getting weird logic errors like that when I try to execute too many queries within a transaction.
I would really like to find a solution to the problem as the success of the execution of the queries in this procedure is really important as a whole.
Please help! Regards,