Access will do many strange things if you abuse it. When you are running code to test and debug, any time an error occurs that causes the program to halt, you are likely to get some memory space that is not 'released' from a varible assignment that is not 'cleaned up.' Or, even if you do not crash a lot, not performing the proper steps to release memory space can cause unpredictable results.
When using Recordsets it is important to close them and set their varibles to Nothing when you are done with them. If you are using any form of 'automation' (controling other programs or databases from Access) you need to be sure you close connections and applications that you started. If you are doing a lot of sorting or other memory intensive operations and a crash occurs, things get 'stuck' and memory gets fragmented. Stacks get corrupted.
Compact and Repair often. Close Access and start again. If you are on a LAN, logout and back on. Rebooting is best, even if time consuming. Occasionally you can even corrupt a dll file and it will need to be reloaded. I have even had to called the IT department at work to reload Access onto my workstation as a last resort (mostly that just gets the dlls fixed.)
'Back in the day' when databases were less complicated (and less powerful) they were more stable. Now they are so complex it is really not surprising that things go wrong when coding. The key is to make sure it doesn't happen when your product is rolled out.
