Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-03-2019, 01:50 PM   #16
Mr. Southern
Newly Registered User
 
Join Date: Aug 2019
Location: United States
Posts: 59
Thanks: 8
Thanked 0 Times in 0 Posts
Mr. Southern is on a distinguished road
Re: How can I get a linked Excel table to update automatically?

Quote:
Originally Posted by theDBguy View Post
Yes, I set mine to refresh every 1 minute and then waited for one minute and saw the data changed.
theDBguy,

I'm going to have to wait for tomorrow morning to see if it changes it. Nothing will be updated until then.

Mr. Southern is offline   Reply With Quote
Old 09-13-2019, 11:38 AM   #17
Mr. Southern
Newly Registered User
 
Join Date: Aug 2019
Location: United States
Posts: 59
Thanks: 8
Thanked 0 Times in 0 Posts
Mr. Southern is on a distinguished road
Re: How can I get a linked Excel table to update automatically?

Quote:
Originally Posted by theDBguy View Post
Yes, I set mine to refresh every 1 minute and then waited for one minute and saw the data changed.
It refreshed if its open but im trying to find a way to do it in the background.

I saw:
Code:
Function fRefreshWorkbook() As Boolean
On Error GoTo Err_fRefreshWorkbook
    
    Dim objXL As Object, objWbk As Object, blNewInst As Boolean, _
        strPathToFile As String
    Const c_strFile As String = "Layout.xlsx"
    
    On Error Resume Next
'   See if we can grab an instance of Excel already running
    Set objXL = GetObject(, "Excel.Application")
    If Err <> 0 Then
'       No existing instance of Excel, let's create one
        Set objXL = CreateObject("Excel.Application")
'       Set a flag so we know to destroy the instance after we're done
        blNewInst = True
        Err = 0
    End If
    On Error GoTo Err_fRefreshWorkbook
    
'   Define the Excel file we want to open
    strPathToFile = CurrentProject.Path & "\" & c_strFile
'   Open the file setting the parameter to RefreshLinks = True
    Set objWbk = objXL.Workbooks.Open(strPathToFile, True)
    With objWbk
'       Uncomment following line if links aren't refreshed by the Open() method
'        .RefreshAll
        .Save
        .Close
    End With
Exit_fRefreshWorkbook:
    If Not objWbk Is Nothing Then Set objWbk = Nothing
    If Not objXL Is Nothing Then
        If blNewInst Then objXL.Quit
        Set objXL = Nothing
    End If
    Exit Function
Err_fRefreshWorkbook:
    Select Case Err.Number
    Case Else
        MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
               "Description: " & Err.Description & vbNewLine & vbNewLine & _
               "Procedure: fRefreshWorkbook" & vbNewLine & _
               IIf(Erl, "Line No: " & Erl & vbNewLine, "") & _
               "Module: basTest", , "Error: " & Err.Number
    End Select
    Resume Exit_fRefreshWorkbook
End Function

but i cant tell if its working
Mr. Southern is offline   Reply With Quote
Old 09-13-2019, 02:12 PM   #18
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,609
Thanks: 50
Thanked 1,052 Times in 1,033 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: How can I get a linked Excel table to update automatically?

Quote:
Originally Posted by Mr. Southern View Post
It refreshed if its open but im trying to find a way to do it in the background.
Hi. When you say “background,” are you talking about when the Excel file is closed or just hidden?

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 09-13-2019, 04:31 PM   #19
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,149
Thanks: 15
Thanked 1,572 Times in 1,494 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: How can I get a linked Excel table to update automatically?

Excel is not a database. It should NOT be treated as a database. It would be far better to use an Access FE with proper forms to manage updating the data. If the users need to create reports that Access can't do, then the data can be exported to Excel to let the user slice and dice at will.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 09-13-2019, 06:15 PM   #20
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 932
Thanks: 10
Thanked 189 Times in 179 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: How can I get a linked Excel table to update automatically?

Quote:
Is there any way that I could put a button on a form that would refresh the excel files?
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.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 09-15-2019, 07:28 AM   #21
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,149
Thanks: 15
Thanked 1,572 Times in 1,494 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: How can I get a linked Excel table to update automatically?

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 09-15-2019, 09:23 AM   #22
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,356
Thanks: 87
Thanked 1,642 Times in 1,524 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: How can I get a linked Excel table to update automatically?

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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 09-15-2019, 12:45 PM   #23
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,149
Thanks: 15
Thanked 1,572 Times in 1,494 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: How can I get a linked Excel table to update automatically?

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 10-03-2019, 09:58 AM   #24
Mr. Southern
Newly Registered User
 
Join Date: Aug 2019
Location: United States
Posts: 59
Thanks: 8
Thanked 0 Times in 0 Posts
Mr. Southern is on a distinguished road
Re: How can I get a linked Excel table to update automatically?

Quote:
Originally Posted by theDBguy View Post
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 is offline   Reply With Quote
Old 10-03-2019, 10:01 AM   #25
Mr. Southern
Newly Registered User
 
Join Date: Aug 2019
Location: United States
Posts: 59
Thanks: 8
Thanked 0 Times in 0 Posts
Mr. Southern is on a distinguished road
Re: How can I get a linked Excel table to update automatically?

Quote:
Originally Posted by Micron View Post
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.
Mr. Southern is offline   Reply With Quote
Old 10-03-2019, 10:08 AM   #26
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,609
Thanks: 50
Thanked 1,052 Times in 1,033 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: How can I get a linked Excel table to update automatically?

Quote:
Originally Posted by Mr. Southern View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-04-2019, 05:00 AM   #27
Mr. Southern
Newly Registered User
 
Join Date: Aug 2019
Location: United States
Posts: 59
Thanks: 8
Thanked 0 Times in 0 Posts
Mr. Southern is on a distinguished road
Re: How can I get a linked Excel table to update automatically?

Quote:
Originally Posted by theDBguy View Post
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.
Mr. Southern is offline   Reply With Quote
Old 10-04-2019, 05:43 AM   #28
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 932
Thanks: 10
Thanked 189 Times in 179 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: How can I get a linked Excel table to update automatically?

Quote:
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.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 10-05-2019, 06:41 PM   #29
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,149
Thanks: 15
Thanked 1,572 Times in 1,494 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: How can I get a linked Excel table to update automatically?

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 10-05-2019, 09:05 PM   #30
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,356
Thanks: 87
Thanked 1,642 Times in 1,524 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: How can I get a linked Excel table to update automatically?

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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Reply

Tags
excel , linked table , refresh linked tables

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Rechck linked table automatically Krays23 Tables 3 05-09-2016 01:18 AM
[SOLVED] Insert/update values into linked table (linked from sql server via odbc connection) dejanc Tables 2 12-17-2015 04:31 AM
Automatically Refresh Linked Data On Update Jewlrose Excel 1 09-14-2012 01:50 PM
Update table from excel automatically tberma Tables 8 12-08-2009 07:51 AM
Update automatically with linked forms manwen Forms 1 08-04-2002 08:02 PM




All times are GMT -8. The time now is 02:12 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World