Re: [MSAccessProfessionals] VBA code to open a excel file with power query etl and 'refresh it all'

At least Access is, at best, viewed for what it is while Power BI is sold for more than its shortcomings.

Giorgio

On 09/30/2022 12:03 PM Maurice A <anzus101@gmail.com> wrote:

Power BI has the same SSIS / SSAS but the problem is that the OLAP cubes that you built are trap into a server. To get them out, you need an excel file since the data is locked down my Microsoft.

On Friday, September 30, 2022, Maurice A <anzus101@gmail.com> wrote:

It is very confusing because MS is selling technologies for Citizen Developers.  They are trying to sell small - medium business the idea that a vertipack database can work as a OLTP data warehouse. 

Excel is now a Frankenstein. It has the dumbed down versions of SSIS / SSAS under the hood.

Get to learn the enhancements and you will see what I mean.

On Friday, September 30, 2022, Giorgio Rovelli <giorgio_rovelli@virgilio.it> wrote:

Interesting...so...are you saying that Power BI doesn't have the capabilities to handle its own datasets and has to hand them over to Excel's Power Queries?

On 09/30/2022 11:17 AM Maurice A <anzus101@gmail.com> wrote:

You may not want to hear this but;

Power Query is much better at ETL than access.

Also, 

Most data comes from Power BI datasets (SSAS), there is not currently a way for access to connect to this service.

On Friday, September 30, 2022, Giorgio Rovelli <giorgio_rovelli@virgilio.it> wrote:

Can't you just link all the Excel tables directly from Access and let Access do all the querying jobs so as to modify the Excel tables from Access?

Giorgio

On 09/30/2022 9:59 AM anzus101 <anzus101@gmail.com> wrote:

Thank you for responding Duane.

Ms Excel has become a swiss knife; It has several other tools inside. 

Power Query is an ETL tool that allows you to create Tables. These Tables then go to Power Pivot and from there you create Cubes.

Power Query brings to access the ETL capabilities that was missing, but the excel file does not have the query, it is the Power Query App which then puts it onto the excel file.

Having said this, if an excel file has 20 tables created in Power Query, the excel file needs to be open and then refresh all the underlying connections.

What I am asking here is if anyone of you is using Power Query as an ETL tool for access and to then, come up with a way to refresh all power query connections within ms access.

Please feel free to ask if you need more clarifications about my question.

On Friday, September 30, 2022, Duane Hookom <duanehookom@hotmail.com> wrote:

Hi anzus101,

I'm not sure why you need to refresh. I just 

-created a link to an Excel file

-opened the Excel file

-added a new column

-closed the Excel file

-opened the linked Excel table

-the new column was visible with no extra effort

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of anzus101 <anzus101@gmail.com>
Sent: Thursday, September 29, 2022 6:30 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] VBA code to open a excel file with power query etl and 'refresh it all'

Adding more detail;

I have the excel files as linked tables, so I need a macro that can 'refresh all' the excel file, which includes the underlying power query. I want to add a button in a form.

On Thursday, September 29, 2022, anzus101 via groups.io <anzus101=gmail.com@groups.io> wrote:

Guys, 

I am using Power query to do a lot of ETL to then import into a db. I would like to have a macro that automates this task. Could you please share any code you may have for this regard? Thank you so much in advance.