omnialive
Registered User.
- Local time
- Today, 08:24
- Joined
- Sep 17, 2010
- Messages
- 23
Quick background: I've developed a solution for the company I work for that populates a MS Access database based off of the build done in a multiple worksheet Excel file. This data is then used to create files that are used to rapidly populate a clinical system via an HL7 interface.
I've had the basic functionality working flawlessly for a couple of months now and haven't even looked at the code not to mention change any of it. However, a strange thing happened today when I was running some tests and loading the data from the Excel spreadsheet into the database:
Error 3073: Operation must use updateable query.
I had never ran into this before. I was truly baffled at first! I looked at the code that generated the error and saw no fault. I examined the build of the Excel worksheet that was involved as well and no fault either (I've been using this same Excel file for testing for a while now with no changes). So I cleared everything out and ran the load test again. No errors! I filed it away as some strange Access anomaly.
I then cleared everything again and did another reload...and WHAMMY! It happened again! So after some investigation on the error and more looking, I deduced the problem.
What happens when my application loads the data from the Excel worksheets is that it generates unique identifiers. This is accomplished via a function called "NextID" that is passed an argument that uniquely identifies a row on the [IDPool] table. The [IDPool] table is very simple. It has two columns: [idtype] and [id]. NextID takes the current value for [id] that is found on [IDPool] table with the given [idtype] value and increments [id] by one, updates the [IDPool] table with the incremented value and then returns that value to the calling procedure.
So, what was happening is that my single instance of the application was going through and at times was executing the NextID function quicker than the previous update from the last called instance of NextID had a chance to be applied to the database such that the currently executing NextID function queried a value for [id] that was the same value as the previously executed instance of NextID. So, when the current instance incremented the value and tried to apply the update...it was a duplicate update!
Once I realized what was going on, I solved it easily by adding an IF statement in my error handling section that looked to see if the err.number was 3073 and if so, it redirected function execution back to the top of the function so that it could try again. As with any execution redirection, this could potentially create an infinite loop if for some reason I kept receiving 3073 errors. A good idea would be to implement a counter you increment with each retry and set a max number of retires allowed before the function exits with an error.
I thought I would share this with people in case anyone runs into this in the future. If anyone has any suggestions they would like to add or if they see how my method could be improved, please let me know! I am all about learning less dumb ways of doing what I do!
I've had the basic functionality working flawlessly for a couple of months now and haven't even looked at the code not to mention change any of it. However, a strange thing happened today when I was running some tests and loading the data from the Excel spreadsheet into the database:
Error 3073: Operation must use updateable query.
I had never ran into this before. I was truly baffled at first! I looked at the code that generated the error and saw no fault. I examined the build of the Excel worksheet that was involved as well and no fault either (I've been using this same Excel file for testing for a while now with no changes). So I cleared everything out and ran the load test again. No errors! I filed it away as some strange Access anomaly.
I then cleared everything again and did another reload...and WHAMMY! It happened again! So after some investigation on the error and more looking, I deduced the problem.
What happens when my application loads the data from the Excel worksheets is that it generates unique identifiers. This is accomplished via a function called "NextID" that is passed an argument that uniquely identifies a row on the [IDPool] table. The [IDPool] table is very simple. It has two columns: [idtype] and [id]. NextID takes the current value for [id] that is found on [IDPool] table with the given [idtype] value and increments [id] by one, updates the [IDPool] table with the incremented value and then returns that value to the calling procedure.
So, what was happening is that my single instance of the application was going through and at times was executing the NextID function quicker than the previous update from the last called instance of NextID had a chance to be applied to the database such that the currently executing NextID function queried a value for [id] that was the same value as the previously executed instance of NextID. So, when the current instance incremented the value and tried to apply the update...it was a duplicate update!
Once I realized what was going on, I solved it easily by adding an IF statement in my error handling section that looked to see if the err.number was 3073 and if so, it redirected function execution back to the top of the function so that it could try again. As with any execution redirection, this could potentially create an infinite loop if for some reason I kept receiving 3073 errors. A good idea would be to implement a counter you increment with each retry and set a max number of retires allowed before the function exits with an error.
I thought I would share this with people in case anyone runs into this in the future. If anyone has any suggestions they would like to add or if they see how my method could be improved, please let me know! I am all about learning less dumb ways of doing what I do!
