So my question is quite simple but with programming we all know its not that easy to implement. I have a linked [read only] Excel sheet in my Access. Heres where it gets complicated. The excel sheet is connected to a ODBC iseries server, and thats where it draws the info from. So with that said, I have an update button that opens, refreshes, saves. And excel re updates the access on form load. Great right?

Everything works! Except when I put it on the shared drive only one person can access it and the rest get locked out.

I've explored a couple of options that I could implement and they are create multiple excel sheets and link different ones to each one. Since the user has to update on form load it shouldn't matter that there are 10 of them. Or I could make the person work off the file locally, but then there is no centralized location for the excel files, if someone just needs to 'look' at the data without using all the excel features. I get an error about the jet engine not being able to lock the file or it doesn't exist.

Anyone want to point me in the right direction with this. I was even thinking on open create a cache copy everytime someone opens the file. IE store variable linkedexcel = random string of chars here, tack it onto the end of the excel, and then call that var when linking and running macros. Buttttt again that seems complicated and would like to know if anyone got a suggestion for me.

Thanks guys,

Mike/kay