Another Multiple Users Question

[mikekay]

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

[mikekay]

Anyone????

[OBP]

Have you set up the Excel workbook for Sharing?
see the help topic Share a Workbook.

[mikekay]

See the thing is Access opens it...because its linked.

I've tried setting up sharign with no luck. Its the way that excel is opening up the linked table that I cant run two along side each other pointing to the same file. I cant make it read only either because I have an update button macro that opens in background and saves and updates in access.

Any other help?

[OBP]

Sorry I can't help, I never use Excel for updating Access.

[mikekay]

Anyone Else??

[Imdabaum]

I've never done anything with updating Excel within Access. Usually I have linked Excel docs, but they are for retrieving data and I usually don't change it so read only has worked for me.

Let us know if you find a solution. We'll keep searching.

If I do need to update the data, I usually have a function/macro that imports the Excel data into a local table in Access where the data can then be modified. If you want the output updated in your Excel then you can maybe try and Export the local table, kill the old file(or rename it) and give the new data the appropriate file name. Might be a little complicated, but it's a work around until you find the solution you want.

[geekgirlau]

If Excel gets the data from ODBC, can you not setup Access to link directly to that rather than going through Excel?