I am building an Access 2007 database that links to various Excel files across our intranet servers. VBA code uses DoCmd to set up a link to a specific range in a specific worksheet. When linked, that range is pulled into the db as a local table and the link released. All of this works -- but not without a few problems.
The problems occur (as far as I can tell) when an unshared XL file is already open for use when I want to create the link. If I attempted to manually open the file, I'd get the "locked for editing" warning. But I'm assuming that because it's happening via VBA, it simply errors the code and the error handler moves on to the next file in the list.
First, does this assumption sound correct? Or should I look elsewhere for my connection failure? If so, where would I look? (All file paths are correct and the code does work if the file is shared.)
Second, is there a way around this? I have been able to turn sharing off using VBA, but not on -- I presume this is a security feature. Some of our folks are not comfortable working with a shared file for various reasons -- like the ability to lock out other users.
About the only thing I can think of, though, is some kind of macro in the workbook that would generate a pop-up: "This person is attempting to access your shared file. Do you want to allow this?"
Maybe one other possibility perhaps? If I create an XL file with a separate worksheet for each of my "target" files, and link those worksheets to the files? Do the "target" files have to be shared in this scenario? The advatage is that the links only update when the file is opened and I can automate that, and I can use each separate sheet as a data source for my Access tables. But I could have upwards of 200 linked worksheets!
I'd appreciate hearing any comments or suggestions.
Ed
The problems occur (as far as I can tell) when an unshared XL file is already open for use when I want to create the link. If I attempted to manually open the file, I'd get the "locked for editing" warning. But I'm assuming that because it's happening via VBA, it simply errors the code and the error handler moves on to the next file in the list.
First, does this assumption sound correct? Or should I look elsewhere for my connection failure? If so, where would I look? (All file paths are correct and the code does work if the file is shared.)
Second, is there a way around this? I have been able to turn sharing off using VBA, but not on -- I presume this is a security feature. Some of our folks are not comfortable working with a shared file for various reasons -- like the ability to lock out other users.
About the only thing I can think of, though, is some kind of macro in the workbook that would generate a pop-up: "This person is attempting to access your shared file. Do you want to allow this?"Maybe one other possibility perhaps? If I create an XL file with a separate worksheet for each of my "target" files, and link those worksheets to the files? Do the "target" files have to be shared in this scenario? The advatage is that the links only update when the file is opened and I can automate that, and I can use each separate sheet as a data source for my Access tables. But I could have upwards of 200 linked worksheets!
I'd appreciate hearing any comments or suggestions.
Ed
