Apparently, repeatedly using a recordset to connect to a linked table, and never closing it, but only redefining it over-and-over,
In theory if you did this in a single-step-of-the-loop subroutine, this would not happen because of memory cleanup (a.k.a. garbage collection) that occurs at every END SUB. HOWEVER, it appears that you stay within the same routine for each iteration. That means that garbage cleanup does not occur between loop steps.
When you create a Recordset object, you create TWO things. The recordset
variable is actually an object pointer to the recordset
object that you never actually see. (You can get a hint of its structure using the Object Browser.) Failing to close the prior iteration of the recordset leaves behind a dangling invisible object. In fact, it may leave behind more than one object, since it technically counts as another file channel to be able to independently browse the recordset, which IS part of a file. The action of SET RS = NOTHING goes through the process of clearing out the stuff and closing connections - such as file handles.
In your example you had NESTED loops, one for rows and one for columns. How many rows and columns do you normally have? Because what MIGHT have happened is that you breached the limits of the program HEAP which then cause some catastrophic failure. Another notorious limit is the file handle limit - which usually runs into the error "RESOURCES EXCEEDED." The lack of a different kind of error message initially confused me, but thinking about, I believe I understand the mechanism.
You were doing a DoCmd.Transferxxx operation, which is a
black box to us since Access is NOT Open Source. This means that you don't know what error ACTUALLY tripped the black box - only that it returned an error that you can't even verify - because as you noted in post #1, when you look in the appropriate system table, the import spec seems to still be where it should be. If you blow out a memory feature (HEAP) or a quota-based feature (recordset or file structure) while inside the black box, you will still only get ONE error message - that the box couldn't work with the named specification. Since you got a file-related message, I'm
guessing you blew out the file handle quota.
Interestingly enough, if you had built those loops to set up the circumstances but then called a routine to do the work required for the individual row/column combination, the sub's cleanup implied by the END SUB statement would have correctly handled the unclosed recordset and open file handle. If so, you would not have seen the error at all. (Probably.)
)
