Ok, this makes no sense, and if someone told me this, I'd have to assume user error. I primarily work in vba in excel but we have a developer who has made some tools in Access.
One of his tools loops through a list of databases to run a check on the data, which needs to be done daily.
He keeps this list in a table and recently there was a need to add a new facility. I opened up his access file which resides on a network and made the amendment to the table with the new facility.
After doing so, the performance went down considerably. A daily check per facility usually takes 30 seconds tops. Once I added this new facility, all facilities were taking over 5 minutes each!
I thought what could I be doing wrong?! I had him add the table to the previous copy and he has this file on his drive, made the addition and copied it over to the network share, and it ran back to the usual performance.
On a hunch, instead of me modifying the network file directly, I copied it to my drive and added the facility and then copied it back to the network and lo and behold it's fast again.
So even though I know how to get around this, it still bugs me not knowing why this happens. I even tried taking the slow version, copy it to my drive, make a small change just so it's updated, and save that and copy it back to the network drive but it's still slow.
I tried that to see if a save on my drive resolves the problem once it's happened but that doesn't appear to be the case. Once a file is 'slow' I can't seem to get it to behave with normal performance again.
I can't see it being file permissions but regardless, I have full access to this network drive.
I've used computers for years, but this is baffling to me, and I want to ensure I don't do this going forward, but I would like to think I can update code (or even a simple table change) over a network and not have it result it some huge performance hit.
And I duplicated this several times as I thought wait I must have done something wrong. But each time, modify it on my drive and copy over to network, it's fine, modify the file directly on the network, and the performance takes a huge hit.
One of his tools loops through a list of databases to run a check on the data, which needs to be done daily.
He keeps this list in a table and recently there was a need to add a new facility. I opened up his access file which resides on a network and made the amendment to the table with the new facility.
After doing so, the performance went down considerably. A daily check per facility usually takes 30 seconds tops. Once I added this new facility, all facilities were taking over 5 minutes each!
I thought what could I be doing wrong?! I had him add the table to the previous copy and he has this file on his drive, made the addition and copied it over to the network share, and it ran back to the usual performance.
On a hunch, instead of me modifying the network file directly, I copied it to my drive and added the facility and then copied it back to the network and lo and behold it's fast again.
So even though I know how to get around this, it still bugs me not knowing why this happens. I even tried taking the slow version, copy it to my drive, make a small change just so it's updated, and save that and copy it back to the network drive but it's still slow.
I tried that to see if a save on my drive resolves the problem once it's happened but that doesn't appear to be the case. Once a file is 'slow' I can't seem to get it to behave with normal performance again.
I can't see it being file permissions but regardless, I have full access to this network drive.
I've used computers for years, but this is baffling to me, and I want to ensure I don't do this going forward, but I would like to think I can update code (or even a simple table change) over a network and not have it result it some huge performance hit.
And I duplicated this several times as I thought wait I must have done something wrong. But each time, modify it on my drive and copy over to network, it's fine, modify the file directly on the network, and the performance takes a huge hit.
