Consulting

Results 1 to 8 of 8

Thread: Another Multiple Users Question

  1. #1
    VBAX Regular
    Joined
    May 2010
    Posts
    10
    Location

    Another Multiple Users Question

    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

  2. #2
    VBAX Regular
    Joined
    May 2010
    Posts
    10
    Location
    Anyone????

  3. #3
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,288
    Location
    Have you set up the Excel workbook for Sharing?
    see the help topic Share a Workbook.

  4. #4
    VBAX Regular
    Joined
    May 2010
    Posts
    10
    Location
    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?

  5. #5
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,288
    Location
    Sorry I can't help, I never use Excel for updating Access.

  6. #6
    VBAX Regular
    Joined
    May 2010
    Posts
    10
    Location
    Anyone Else??

  7. #7
    VBAX Expert Imdabaum's Avatar
    Joined
    Jun 2006
    Posts
    652
    Location
    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.
    Someday I'll understand everything...
    Even then...I'll still pretend I'm a beginner.

  8. #8
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    If Excel gets the data from ODBC, can you not setup Access to link directly to that rather than going through Excel?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •