How can I get a linked Excel table to update automatically? (1 Viewer)

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:37
Joined
Feb 19, 2002
Messages
42,970
Each time you open Access, the spreadsheet will be "refreshed". I have no idea what happens if you maintain a live link to a spreadsheet which other people are updating. I would never do that. Access does not play well with others and I can't imagine that this will work well.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:37
Joined
Feb 28, 2001
Messages
26,996
The one time I dealt with this situation was years ago, but we had a "nightmare" data sharing setup in which Access linked to an Excel worksheet as a table and it was always running into trouble with the Engineering team trying to update it through Excel while the Sys Admin team was trying to update something else through Access. Data corruption got slung all over the place like a monkey-cage riot at the zoo.

We finally solved the problem by telling the Engineering team that Excel was bollixing up the works because it didn't share quite as nicely with others as would Access. I had to rebuild the table they were using and take into account that as it was originally a spreadsheet, it was denormalized to Hell and gone. That was three months of redesign and migrating a little bit at a time to make it behave in a way that came closer to satisfying everyone. I never could make it work perfectly because I was not allowed to finish the project.

Of course, the Engineers hated it anyway because it was no longer "their spreadsheet" - but the Navy told them that what HAD been happening was "unsat" and that it was time to do something else, so "live with it and get on to other business." The upshot of that was that I was persona non grata because I had to fix their problem in a way that they could not. If you know about the "Not Invented Here" (NIH) syndrome, you will understand.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:37
Joined
Feb 19, 2002
Messages
42,970
Users do become enamored with their spreadsheets and they're afraid that they will be given an Access app that requires them to learn Access when in fact, a properly designed Access app, never lets the users ever see "Access" at all. The user interacts with forms and reports only. He doesn't build tables or queries or anything else. He only uses what the professional built for him.

Convincing them to trust you to give them something where they won't have to learn new technology can be tough but it will be so much safer in the long run that it is worth the errort.
 

Mr. Southern

Registered User.
Local time
Today, 03:37
Joined
Aug 29, 2019
Messages
90
Does this mean that the assumption is that once a spreadsheet is linked to Access that it needs to be "refreshed" because it's outdated just because it was linked (e.g.) yesterday? A linked spreadsheet always contains the latest data when the db is opened.

If this is about "refreshing" on a continual basis because the spreadsheet changes (e.g.) every 5 minutes, then there's nothing to do from the Access side of things AFAIC, because Access cannot save a workbook that is already opened by someone else. Excel workbooks are opened exclusively, no?

I'm not sure everyone is on the same page.

The spreadsheet changes every couple of hours during the day. This workbook only has one user and it wouldn't be opened by someone else.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:37
Joined
Oct 29, 2018
Messages
21,357
I want it to refresh the queries and connections without the user opening it.
Hi. We're still not sure if we're on the same page here. Without opening it could mean something that's you don't want something "visible" to the user. In other words, it could be open but hidden from view. Otherwise, how can you expect something to "work" or do its job without opening it (visible or hidden)? Codes can only run when they are executed. If you don't open the file to execute the code, the code won't run. Again, "opening" the file could simply mean using an external code to run the subject file, without user intervention.
 

Mr. Southern

Registered User.
Local time
Today, 03:37
Joined
Aug 29, 2019
Messages
90
Hi. We're still not sure if we're on the same page here. Without opening it could mean something that's you don't want something "visible" to the user. In other words, it could be open but hidden from view. Otherwise, how can you expect something to "work" or do its job without opening it (visible or hidden)? Codes can only run when they are executed. If you don't open the file to execute the code, the code won't run. Again, "opening" the file could simply mean using an external code to run the subject file, without user intervention.

I understand what you're saying. I know it has to open but I don't want it to be "visible" to the user.
 

Micron

AWF VIP
Local time
Today, 04:37
Joined
Oct 20, 2018
Messages
3,476
I know it has to open but I don't want it to be "visible" to the user. Yesterday 02:08 PM
Then if the code you posted is OK otherwise, just add

objXL.Visible = False

after

Set objXL = CreateObject("Excel.Application")

Should make it invisible to the user, but of course, this won't affect the workbook if it is already open.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:37
Joined
Feb 19, 2002
Messages
42,970
Think about how Excel works. The workbook is opened and loaded into memory on the users personal computer. He makes changes to update the workbook. They stay in memory on his PC until he issues a save command. How is Access supposed to get what is in memory on some other computer? If the user forces a save but leaves the file open, Access might "see" the updates but this is a seriously bad idea.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:37
Joined
Feb 28, 2001
Messages
26,996
Just to step in to amplify Pat's last comment: In fact, it is contrary to the model by which Windows MUST run if it wants to sell systems to the USA federal government. There is a principle of "isolation" that means that external non-privileged processes MUST NOT be able to see the contents of memory for another process. This would not only be a security violation but it would disqualify use of Windows for any installations with "Sensitive" or higher data levels. In essence, it PAYS Microsoft to assure that Windows does not ever gain this ability.
 

Users who are viewing this thread

Top Bottom