Access macro / vba code transfertext not over writing files.

avoorheis
avoorheis used Ask the Experts™
on
I have set up several macros and some vba routines that use the transfer text or transfer spreadsheet commands to create files on various network folders. These routines, I thought, would just overwrite the existing files. In fact, I've tested each one several times to make sure (like changing the data, run the routine and then making sure the data had been updated).

I happened to review one of the files and found that the data had not been updated, even though the timestamp of the file showed that it had been modified that day. When I found this, I deleted the file and re-ran the code, which created the file with the correct data. I even ran my test again where I modified the data in the file and re-ran the code to make sure the data was updated and it was.

The only thing I can figure is that if someone else opens the file and saves it, something happens to the permission and access won't actually overwrite the data, even though the modified date was changed and Access did not report any problem.

Since I've found this, I've implemented changes that will just delete the existing file before updating (so, just creating a new file each time).

My actual question is can someone confirm what I believe is happening or have a different explanation?

thanks
alan
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
MIS Liason
Most Valuable Expert 2012
Commented:
Could be a lot of things.

but your general assumption is correct.
These files should overwrite the existing files.

If not:
1. Make sure the code actually Runs.
2. The code may do more than just create this file.
If another part of the code fails before the file is created, then your error handler will take over and exit the sub.  ...Thus, not running the file code.

What I do is always include a Time/Date stamp on files I create via code.
This way, no matter what the OS thinks, I can see exactly when the file was created...
Something like this, depending on how you are creating your textfile:


Dim strFileName As String
'...
strFileName = "C:\YourFolder\" & Format(Now, "YYYY-MM-DD-HH-NN-SS") & "-YourName.txt"
'...
DoCmd.OutputTo acOutputTable, "YourTable", acFormatTXT, strFileName
'Or:
DoCmd.TransferText acExportDelim, , "YourTable", strFileName
'...or whatever...

JeffCoachman

Author

Commented:
Thanks for confirming my assumption. I believe it has to do with the way our network folders are implemented and how Access interacts with those rules. I'll implement your strategy where I can, otherwise, I'll just delete the old file first.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial