>>One customer is showing a collection of errors that we can't duplicate or correct for. In the majority of cases it works properly, but at random times, the same line of code (PACK a temporary table) will throw one of three errors:
>>Error 1705: File Access Denied OR Error 52: No table is open in the current work area OR Error 1: File [temporary filename] does not exist.
>>I've been fighting it for a while and have pretty much run out of ideas. Why is it throwing different errors at the same time and place? What is causing Exclusive use to fail or file not to be found?
>>I've tried all the usual suspects and guarding the pack with USED(alias) and ISEXCLUSIVE() will hide the error but the same random set of errors appears farther along in the code where the file is used.

More thoughts with a clear head. The program is old enough that diving into it borders on archaeology. ;) The problem only appeared last December, five months or so after last change was made, and has been increasing over time.

>Is the temp table local, or on a network share? For the following I'll assume local.
Single machine, local drive, sys(2023) and sys(3) are heavily used to name temp files instead of cursors.

>These errors are variations of a single problem, that a file doesn't exist when it should.
I knew that, really I did... How can I figure out where or when it's closing?

>The most common cause IME is creating cursors from SELECTs without specifying NOFILTER, then referencing those cursors in subsequent SELECTs. Without NOFILTER cursors aren't guaranteed to exist on disk. I see you're using VFP6 so the READWRITE clause is not available in SELECT - SQL.
>If you're doing heavy data munging with cursors, I'd first make sure to include NOFILTER in every SELECT that creates a cursor that could possibly be linked in some fashion to your temp table. Even if you don't see any such connection, try it anyways. ISTR a couple of head-scratchers of my own, where I did this merely because it was thematic, and it fixed the problem.
I vaguely recall a trick for that - adding a field not present in the original table forced it to write to disk.

>I've seen cases of strange behaviour when the local temp file folder has thousands of dead/orphan files. This can easily happen when a computer is used for several years and the folder has never been cleaned out. A PACK operation makes extra heavy use of temp files; this could be a factor. Maybe file-name collision, poor disk performance, ...
I suspected temp file name collision, and cleaning out that garbage helped slightly - the problem occurred a bit less often, but the garbage wasn't piling up quickly enough for that to be the sole cause. The program queries disk space at startup, that hasn't complained, and Explorer shows plenty of room.

>Do you have to use PACK? SET DELETED ON will ignore deleted rows automatically, including in SELECT - SQL. If you have DELETED set OFF and just need read-only access to undeleted rows you could SELECT ... WHERE NOT DELETED() INTO CURSOR NewOne NOFILTER for further operations. I haven't needed to use PACK for many years except in periodic (monthly/semiannual) database maintenance procedures.
See comment above about archaeology. It's a temp table to hold line items on a transaction. The PACK and later use of the table is so multiple transactions can be entered without closing/reopening the screen. But that's not a complete caveat - the error often isn't seen until 5-10 transactions are entered, but it's also been seen on the very first.


Crap. Now a second customer is reporting similar symptoms.

Chris.