Fairly simple connection, but without a doubt it works best if the data is cloud based if you want to start any auto updating/refresh.
I'm a newbie myself but have a dynamic dashboard with 2 X refresh daily via personal gateway. Host data on sharepoint in my case (. Xlsx)
Works quite well all things considered, for Access look at sharepoint lists as an alternative, it doesn't play well hosted directly on sharepoint. Or use vba to output query to sharepoint.
Azure is also possible..
Regards
Slap