Refresh Excel from open database

[Jalex]

Scenario:

1. sharing.xlsx is linked to database.accdb
2. sharing.xlsx refreshes on open.
3. database.accdb is open all day long (front-end, non-exclusive).
4. When opening sharing.xlsx it throws "You attempted to open a database that is already opened".

How can this be solved? (we always want it to refresh on open; database.accdb is password protected; can't ask users.accdb to close it; sharing.xlsx refreshes when users.accdb is closed).

Excel 2010, Access 2010

I'll appreciate any help on the matter, this has been driving me crazy. :banghead:

 

[boblarson]

Using Excel as a linked table has serious limitations.

So, can you describe your current "set up" a bit more in detail (what is the difference between database.accdb and users.accdb and are your users opening the same Access frontend file on the network or do they have a copy of the frontend on each of their machines?) and why you need to link to this workbook.

 

[Jalex]

Using Excel as a linked table has serious limitations.

So, can you describe your current "set up" a bit more in detail (what is the difference between database.accdb and users.accdb and are your users opening the same Access frontend file on the network or do they have a copy of the frontend on each of their machines?) and why you need to link to this workbook.

Hi bob, thanks for taking over this one.

This thread is a follow up of this topic (which the forum kindly helped me with): http://www.access-programmers.co.uk/forums/showthread.php?t=234498

Addressing the question:

1) database.accdb (back end database)
2) users.accdb (front end FormsQueryReportMacro)
3) both files are over the same shared network but within different folder)
4) users.accdb is opened by up to 10 concurrent users (they are not allowed to copy the front end to their local machines.
5) Reason: I have a client that wants to work with the most updated data by having a copy of it in its own Access file (client.accdb). My users want to share the data in database.accdb but not give any other permission to client.accdb. The best solution found was the "excel linked table" as client.accdb can link to it, and refresh the data by just opening the file. (more detail on the original thread)

Thanks a lot!

 

[boblarson]

Okay, first off. Having multiple users using the same frontend is not good. The client should be educated on the pitfalls (including a much greater chance of corruption, losing data, having everyone not able to work instead of one person going down until their frontend is replaced and the rest being able to continue).

As for refreshing, I don't see a way to do it as it is a single file which, when linked is "opened" upon anyone opening Access. Excel is not a multi-user database backend. It never really has been, although prior to Access 2000/2002 area when MS lost a lawsuit, you could actually update the Excel file through Access and vice-versa. But as you have seen, when the first person opens the Access file, it refreshes the Excel file because it "opens it." But it is not closed until the last person gets out. So as far as I know, you are stuck with that.

Now I could be wrong and someone might post differently, but based on what I know about all of this, I'm pretty confident that you would have no way to do it short of having a separate frontend for each user (like you should) and then having their frontend linked to a COPY of the Excel file which is overwritten with the latest when they open their frontend.

 

[Jalex]

Okay, first off. Having multiple users using the same frontend is not good. The client should be educated on the pitfalls (including a much greater chance of corruption, losing data, having everyone not able to work instead of one person going down until their frontend is replaced and the rest being able to continue).

As for refreshing, I don't see a way to do it as it is a single file which, when linked is "opened" upon anyone opening Access. Excel is not a multi-user database backend. It never really has been, although prior to Access 2000/2002 area when MS lost a lawsuit, you could actually update the Excel file through Access and vice-versa. But as you have seen, when the first person opens the Access file, it refreshes the Excel file because it "opens it." But it is not closed until the last person gets out. So as far as I know, you are stuck with that.

Now I could be wrong and someone might post differently, but based on what I know about all of this, I'm pretty confident that you would have no way to do it short of having a separate frontend for each user (like you should) and then having their frontend linked to a COPY of the Excel file which is overwritten with the latest when they open their frontend.

That sucks.

I agree about the benefits, but can't do anything about it, my users were specific about not wanting copies spread on users machines.

Thanks a lot Bob.

 

[boblarson]

That sucks.

I agree about the benefits, but can't do anything about it, my users were specific about not wanting copies spread on users machines.

If they were thinking like one of my previous employers - then that can be overcome. My employer (big Healthcare company) had an org using a set of Access databases for Medical Equipment. 100+ users and they were all using a single frontend from the network location. When I got there they told me they had constant corruption issues. So I told them what I told you. They said, "NO WAY!! We don't want to have to touch 100+ machines everytime the frontend changes." So I told them, no problem. And I built my Frontend Auto Update Enabling Tool which could turn them into self-updating frontends.

Frontend gets changed, version number gets changed. Then user opens their frontend and they get a message that it is out of date and will close and get the new one. It closes after they click OK and it deletes itself, downloads the new version and reopens for them all without them or us doing any bit of work.

And now, here at my current location we just have a batch file which is the shortcut and it copies a new copy into the user's temp folder (on Citrix as we have to use that) and it opens. It does it every time so it is always new and it also hardly ever corrupts and, when it does, all the person has to do is close it and click the shortcut again and a new copy is there and opens.

So, anyway after the Frontend Updater was created I put it on my website to give away for free to anyone who wants to use it. I have had people from major companies email me to thank me, people in the Armed Forces who are using it email me, etc.

I guess the thing is that the reason for them to not want it on user's machines should be gathered because some things can actually work BETTER for them if they do. But again, they should be made to be aware that running multiple people off a single file can, and has for others, have some serious consequences. But if they still won't listen, then their problem.