Excel connected to SQL - auto update without having the file open?

[WorkingRicardo]

So I have this Excel file in sharepoint library with a table set to update every 10 min or so to its data source, SQL. I don't have the SQL credentials so I just set up the Excel for a colleague who does, so that person must have the file all day the file opened so that it auto-updates.
Lately there's been so much trouble with this file, as it is updating in their side of things, but not me or no one else is seeing the changes, and sometimes the last update that I can see is from like 3 hours ago. 

I'm guessing this has something to do with the fact that there might be people opening the file and messing up connections. 

So... Is there a way for this file to autoupdate with no one having the file open? That might solve things.

Or, if you know what could be happening with my colleague's connection, it would be very much appreciated.

Thanks for the input.

[Omid_Motamedise]

I had the same problem, but in any case I recomend you to add a new query to show the time of last update . 

[mermanahan]

How do they use this excel file with the connection to SQL Server? Need a better understanding of the overall issue

Are YOU using the this excel file as work around for not having the credentials to the SQL Server?

[WorkingRicardo]

@mermanahan hey ty for your reply!
1) I'm using an Excel table to populate various comboboxes throughout different Apps. So, it is a file that everybody has to have acces to it. 
This excel file is sourcing its data from the SQL, set to auto-update every 3 mins. I have my colleague with credentials open the file everyday and leaving it open and it does auto-update for them, however this changes are not being uploaded to the collective Excel file (I can't see them). 
Therefore, all my users are missing data in their comboboxes. 

2), Yes, its a workaround. I guess if I had credentials I could make a PowerAutomate flow to update changes automatically to the Excel file. Considering I need everyone having access to the source, I'd still need that Excel file. 

[mermanahan]

Ah I see. I am used to work arounds 🙂 that's why it sounded familiar. 

Can your colleague that has access to the server create dataflows? Then maybe your app can connect to that dataflow. See the link below. 

https://learn.microsoft.com/en-us/power-apps/maker/data-platform/create-and-use-dataflows 

[lbendlin]

Is there a way for this file to autoupdate with no one having the file open?

Why update it when nobody looks at the data?

[WorkingRicardo]

@lbendlin hey ty for your reply!
I'm using this Excel file in many PowerApps app, and it is a key factor in all of them. 

[lbendlin]

You may want to start thinking about moving your data to a more robust platform, like SQL Server.

[WorkingRicardo]

@lbendlin I don't have credentials to SQL and don't think that's changing anytime soon, I'm stuck the way it is. Need to use the Excel as source for my apps. Also, users of the app need to have acces to that Sql database as well, so don't think that would even work for me. 

In other words, I'm forced to use this clunky excel file