Code to make an excel spreadsheet shared from Access

[nintendomasta811]

Does anyone know if code exists to make an excel spreadsheet shared
from within Access? Basically, I need to overwrite an excel .XLS file
from access on a regular basis. I'm doing this using the docmd.outputTo
method since docmd.transferspreadsheet was horribly problematic. It
works, however, once the file is overwritten I can't overwrite it
again; the new .XLS file defaults back to not being shared so I can't
overwrite it. Is there some code I can use after docmd.outputTo in
order to make the spreadsheet shared again?

Thanks in advance.

 

[SusanV]

The sharing and permissions should be set on the folder the file resides in,
rather than the file itself, both for ease of user rights management and to
avoid this type of problem.

 

[nintendomasta811]

Thanks for the suggestion, but I'm still having problems after making
everything shared and allowing full control to all users. Basically, I
need to overwrite or update an excel spreadsheet from Access WHILE a
user is in excel. In the past, a linked table would suffice from Access
to update both ends; however, with the release of MS Access Service
Pack 2 microsoft disabled the ability of users to edit the Access end
of a linked table (it is permanently read only in Access, you can only
update it from the excel end). Is there any solution to this problem?
Right now I can import data from excel just fine when someone is using
it, however, updating that file has been quite problematic for me...I
wish MS offered a better alternative when they disabled that Access
functionality...

 

[SusanV]

Ah you have a write conflict due to the file being open, rather that it
being a share/permission thing. Honestly I don't see how you can resolve
this using Excel files. Is there some reason the data needs to be in Excel?
Can you import the data and get ALL your users on Access? Then you would
only have a write conflict if 2 users were trying to edit the same record at
the same time...

Perhaps someone else has an idea, maybe in one of the Excel newsgroups...

Sorry if this hasn't been very helpful

:-/

SusanV

 

[nintendomasta811]

I only wish that the data could be entirely in Access, believe me.
Unfortunately, the request specifically mentioned using excel for a
client end so I have little control over it. Thank you for your help,
though.