Hello. Running an insert query (for multiple records) via VBA (sql = "INSERT INTO...") Once in a while (randomly it seems), VBA loop will stop, and warn me it needs to be an updateable query. If I just F5 continue the sql execution, it works. Would a DO EVENTS help? Still weird! It feels like VBA needs to catch its breath!
Weird VBA / Query Behavior (2 Viewers)
- Thread starter CedarTree
- Start date
isladogs
Access MVP / VIP
- Local time
- Today, 09:54
- Joined
- Jan 14, 2017
- Messages
- 19,428
Possibly but only if the underlying table is locked from the last append which seems unlikely. Try it and see.
However is there a reason why you can’t just append all the records at once rather than looping through one record at a time?
However is there a reason why you can’t just append all the records at once rather than looping through one record at a time?
RonPaii
Active member
- Local time
- Today, 03:54
- Joined
- Jul 16, 2025
- Messages
- 315
Once when archive old quote data because the FE size reached the MDB max, the queries would fail because of transaction limits. I changed to moving 1 quote per transaction. It would also fail after certain number of transactions. I would then restart access and restart the program to continue where it left off. So there is/was a limit that you may have reached. Now this was thousands of quotes so if you are talking about 10 or 12 then that limit would not be the issue.
tvanstiphout
Well-known member
- Local time
- Today, 02:54
- Joined
- Jan 22, 2016
- Messages
- 658
How many records?
One at a time...
I tried Do Events and I think it's helping (?)
Adding one at a time due to analysis in the mix for each record and the writing the results...
tvanstiphout
Well-known member
- Local time
- Today, 02:54
- Joined
- Jan 22, 2016
- Messages
- 658
I understand so far that each INSERT INTO inserts 1 record.
Are you calling that code in a loop? If so, how many iterations?
If you need to verify each record before deciding if you insert, I'd suggest having a counter that not only shows how many records have been processed, but also allows you to DoEvents every 10 or 20 inserts.
If your computer acts sluggish, you can DoEvents more often. If not, you can see if doing them less often allows your insert loop to run more quickly.
Unless you are doing 10 or 15 inserts you will find having SOMETHING show up on screen will keep users from fearing it isn't working.
If your computer acts sluggish, you can DoEvents more often. If not, you can see if doing them less often allows your insert loop to run more quickly.
Unless you are doing 10 or 15 inserts you will find having SOMETHING show up on screen will keep users from fearing it isn't working.
- Local time
- Today, 03:54
- Joined
- Feb 28, 2001
- Messages
- 30,751
I might have expected a specific error message for this but it sounds VAGUELY like a problem we have seen in the past. When you do repeated single operations like this, each single operation is implicitly opening a file and in so doing, you consume a file handle structure (behind the scenes). I would have expected the error "out of resources" or some similar error that you were out of SOMETHING.
The solution is to find a way to do that INSERT INTO for all eligible records at once, not one at a time. Do it in bulk mode so that the query only has to open the implied file once.
If this IS a situation where a resource has topped out, DO EVENTS will probably have no effect.
The solution is to find a way to do that INSERT INTO for all eligible records at once, not one at a time. Do it in bulk mode so that the query only has to open the implied file once.
If this IS a situation where a resource has topped out, DO EVENTS will probably have no effect.
Similar threads
