Difference between connections and queries using VBA to create Power Query

[jaryszek]

Hi Guys,

i am using code from here:

https://www.excelcampus.com/vba/powe...ly-all-tables/

Please Login or Register  to view this content.

Can anybody explain why to create connection to created query and confirm that connection name is hidden in excel workbook memoery?

Query is just query in PQ. But why to add connection to it?

Best,
Jacek

[AliGW]

Query is just query in PQ.

What EXACTLY do you mean by this statement? How do you define 'just a query'?

My understanding of the way PQ works is this (and this may be rudimentary): if you create a query and load it to connection only (there's a clue in this), then it's only the parameters of the query (the steps taken within it) that are saved. The query tables themselves have to be recreated when you open the workbook.

Someone with a deeper understanding may be able to give more detail, but in essence, that's how connection only queries refresh.

[jaryszek]

Hi Ali,

thank you very much.

What EXACTLY do you mean by this statement? How do you define 'just a query'?

I mean that this is just a view from some kind of source range.

And what is strange connection is also created here:

Please Login or Register  to view this content.

----

The query tables themselves have to be recreated when you open the workbook

to fully understand.
Connection has to be done together with queries.add statement ? in order to have proper source connected to PQ?
I tested it:

I commented this part of code:

Please Login or Register  to view this content.

closed and open workbook and....nothing change. Still i have connection only created.

Hmm,
Best,
Jacek

[rorya]

My understanding is that yes, a query is just a query in PQ. Like a query in Access, it's basically just a set of instructions that will do something when executed. A connection is the link between that query and Excel. It allows you do things like building a pivot table connected to that query, or returning the data to a table. So you can delete the connection, but then Excel cannot actually use that query, since it no longer has any link to it. That may make sense if you have queries that are essentially helper queries - e.g. sources for a merge query - that don't need to be accessed from Excel.

[jaryszek]

Thank you very much for help and support,
It it clear!

Jacek