After even more digging I eventually found this Issues with R (3.6.3) and RStudio (1.2.5033) on network mapped drive (U:) - #2 by Harlananelson and it seems that it's related. Previously I was accessing the network location as //server/share/folder and it seems to be working now when I actually mapped the share as Z:\folder and accessed the files from there.
I don't know how the current version 2021.09 relates to the v1.4 mentioned here Not possible to save file from Rstudio 1.2.5033 and 1.3.036 (Windows 10) on network shares (UNC paths) · Issue #6587 · rstudio/rstudio · GitHub but it's been over a year and the issue still seems to persists? Should I maybe open another issue in GitHub or will the fix eventually be released? @kevinushey ?