0

I have a huge application made in VBA. All the data is stored on a shared network hard drive. When users use the application, the data is read from the network HDD, updated, then printed.

Like this:

Open FilePath For Input As #1
    Line Input #1, Data
Close #1

'Do stuff, Update Data...

Open FilePath For Output As #1
    Print #1, Data
Close #1

This happens all the time. The application is used 9 to 5.

I've had occurrences where only half of the file gets printed, the data is just cut off. I reckon what has happened is that the connection to the network HDD were lost precisely when the data was printed. I have a backup system that backups the data every 15 minutes, so we lose at most 15 minutes of work. But it requires me to manually copy and paste from the backup. If I'm not there it will be difficult. People there are not tech savvy.

Is there a way to solve this so that if the connection is lost on a certain computer, the data is somehow not gone?

Jens
  • 183
  • 1
  • 10
  • How about that you write the data to the local file system and only after (successfully) writing it copy it to the share? – FunThomas Dec 07 '22 at 13:59
  • That's an idea! But what will happen if the connection is lost during the copy? – Jens Dec 07 '22 at 14:19
  • You could copy it back under a different name (original_temp.xls). Then when the copy is complete rename the original to original_backup.xls and rename original_temp.xls to original.xlsx – Senior Momentum Dec 07 '22 at 14:26
  • That's another idea, but what happens if the connection is lost in the middle of this process? Then the original is renamed original_backup and original_temp is not renamed yet? – Jens Dec 07 '22 at 14:36
  • If copy fails, you should get an error message and therefore know that it failed. See https://stackoverflow.com/questions/16943003/vba-to-copy-a-file-from-one-directory-to-another for the two possibilities to copy a file using VBA. But unless you have really large files (or a really slow network), the copy should run with a very small risk that the connection is lost while the copy process is running. – FunThomas Dec 07 '22 at 15:30
  • If the copy fails mid through (due to lost connection) could it be that only half of the file is copied? Resulting in the file being cut off? – Jens Dec 07 '22 at 15:40
  • I don't think so. But if you want to know, just copy a large file and disconnect your network cable... On the local side, you will receive a notification that the copy failed. And on the server side, the incomplete file is likely discarded. – FunThomas Dec 07 '22 at 16:38
  • Maybe you need a database. – Senior Momentum Dec 07 '22 at 17:03

0 Answers0