How can I get a linked Excel table to update automatically?

[Pat Hartman]

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]

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]

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]

Hi. When you say “background,” are you talking about when the Excel file is closed or just hidden?

I want it to refresh the queries and connections without the user opening it.

 

[Mr. Southern]

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 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]

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.

 

[Deleted member 147267]

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]

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]

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.