The Server, The CSV, and the TMP files

[ClimoC]

Arvo

Thought I'd put this in here rather than the Excel one as the bulk of the code is ADO.

So the system I have setup has a read-only workbook that connects to CSV databases, edits them, and saves them back out again (meaning only one workbook to debug!)

I have researched and been made aware that something to do with synchronicity issues in Win7 (we are on Enterprise) means that sometimes when a document is accessed, a tmp file gets created. People have tried pointing it to antivirus, but we use an in house one, and our network is fairly open (I can youtube, facebook, I can even download **** or viruses - not that I ever would obviously).

The issue: is that sporadically and randomly, when it comes time to save and overwrite the existing file, a ".tmp" file (hidden) remains, but the csv file disappears into the nether. There is no error supplied at the point of the code saving.

I initially attributed the error to people with poorer network connections, and that my original code opened the csv, copied and pasted the data to a spare sheet, at which point it was slurped up into a recordset, and work could begin.

I have since used SQL to create the Recordset directly reading from the csv itself - no 'open'. This dramatically reduced the number of .tmp files being created.

But now they're creeping back in again.

From the timestamp on 'date modified' for the .tmp files that remain when the main csvs go missing, I can see it is when people use the saving function.

Is there a way to write the recordset back to the csv file directly with SQL, much like how I now access the recordset in the first place?

The only thing I can think to do, which I don't like the idea of, is to follow the line that saves out as a csv, with a Loop that refuses to stop and allow you to continue quitting or working until the Scripting.FileSystemObject can detect the ".csv" file in the folder

Any thoughts on this?

Office 2010, Win7 Enterprise

PS if anyone's wondering why I'm not using accdb's or something equivalent, it's because the end users are more comfortable with csv's, so if there's a problem and I'm not around, someone with half a brain and no coding experience can restore/copy information from the csv without corrupting anything

 

[xenou]

The only thing I can think to do, which I don't like the idea of, is to follow the line that saves out as a csv, with a Loop that refuses to stop and allow you to continue quitting or working until the Scripting.FileSystemObject can detect the ".csv" file in the folder

If the csv file disappears and never shows up then an endless loop doesn't sound like a viable solution. Are you trying to create a multi-user environment with your csv database? That sounds like it could lead to problems if users are accessing the same data (the text files will know nothing about locking records - at best I think you could lock the entire file whenever one is in use).

As far as reads/writes with ADO I think you can create csv files with ado (basically just as if creating, appending, or updating a table). Don't know if performance is going to be different (especially if you are re-writing the entire table/file). If tmp files are appearing (and csv files are disappearing) I would guess that means that the writes are crashing before they complete so there has to be something wrong with how the data is being saved and the session ended.

 

[ClimoC]

I don't think it's exclusive to Excel, I believe it's a Windows thing, and happens with all documents. The nature of the tmp file is unique though, you can "Open WIth..." Notepad and it comes up ads a normal txt file, at which point I have to go into a new document, 'import from text' with the wizard and resave manually.

Regarding the locking feature, you're right that it is a multi-user environment - but I found a workaround for this which is that when a user logs in, a file is created with their username (blank page file). FSO picks up that a user is in there and lets the user know.

 

[xenou]

Okay - that's neat (you're creating your own locking on the tables) Whether the temp files are a window thing or an Excel thing, I don't think they would be there (while the actual target file is missing) unless your save procedure is crashing. So that's where to look, no? It's hard to say much more than that - I may be wrong but I see this as a debugging problem, not a "what's wrong with windows" problem (?).