ACCESS FEED: September 2022

Jumat, 30 September 2022

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

I have been playing with Power Apps and it has serious limitations vs Ms Access. When you have poor telco on site, ms access shines with SharePoint as a back end. Also ms access is still much better option to integrate cloud with on prem systems. Ms access still have an amazing future in complex organisations.

On Saturday, October 1, 2022, Duane Hookom <duanehookom@hotmail.com> wrote:

Apparently Power Apps, Power BI, and Power Automate are the future and it's in the hands of power users. It's one of the reasons I retired. Too much drag and drop build your own.

Duane

Sent from my mobile

On Sep 30, 2022, at 9:02 PM, anzus101 <anzus101@gmail.com> wrote:

Yes. It could but I am just learning that technology. I have been a ms access + posture user. I am catching up with these emerging technologies.

Thank you for this idea! I will research about it.

On Saturday, October 1, 2022, Duane Hookom <duanehookom@hotmail.com> wrote:

Does Power Automate from MS meet your needs? From my experience I expect it should fill most of your needs.

Duane

Sent from my mobile

On Sep 30, 2022, at 2: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.

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

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

Apparently Power Apps, Power BI, and Power Automate are the future and it's in the hands of power users. It's one of the reasons I retired. Too much drag and drop build your own.

Duane

Sent from my mobile

On Sep 30, 2022, at 9:02 PM, anzus101 <anzus101@gmail.com> wrote:

Yes. It could but I am just learning that technology. I have been a ms access + posture user. I am catching up with these emerging technologies.

Thank you for this idea! I will research about it.

On Saturday, October 1, 2022, Duane Hookom <duanehookom@hotmail.com> wrote:

Does Power Automate from MS meet your needs? From my experience I expect it should fill most of your needs.

Duane

Sent from my mobile

On Sep 30, 2022, at 2: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.

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

Yes. It could but I am just learning that technology. I have been a ms access + posture user. I am catching up with these emerging technologies.

Thank you for this idea! I will research about it.

On Saturday, October 1, 2022, Duane Hookom <duanehookom@hotmail.com> wrote:

Does Power Automate from MS meet your needs? From my experience I expect it should fill most of your needs.

Duane

Sent from my mobile

On Sep 30, 2022, at 2: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.

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

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

Does Power Automate from MS meet your needs? From my experience I expect it should fill most of your needs.

Duane

Sent from my mobile

On Sep 30, 2022, at 2: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.

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.

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

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.

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

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.

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

Kamis, 29 September 2022

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

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.

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

Adding more detail;

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.

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

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

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.

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

Selasa, 27 September 2022

Re: [MSAccessProfessionals] Linked Forms

Thanks for your suggestion Duane and Mark.
Will give it a go.

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

Re: [MSAccessProfessionals] Latest Office update causes bug's in Access

On Sun, Sep 25, 2022 at 09:06 AM, Richard Power wrote:

Do you still have a program to download stock data for Excel 2003?
Thanks

Richard

Another day on the Green

On Jul 26, 2022, at 2:18 PM, Bill Mosca <wrmosca@myself.com> wrote:

Hi all.MS has announced a problem with the latest update. Read this:
https://support.microsoft.com/en-us/topic/error-when-trying-to-open-an-accde-mde-file-created-in-a-different-version-of-access-f4cd36cd-549e-42ba-b75a-dfe964294a81
--

Regards,

Bill Mosca, Founder - MS_Access_Professionals

Microsoft Office Access MVP 2010-2016

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

Richard, It would be best if you start a new topic for your request instead of adding it to an existing topic as you did here where it might get lost.

--

Regards,

Bill Mosca, Founder - MS_Access_Professionals

Microsoft Office Access MVP 2010-2016

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

Re: [MSAccessProfessionals] Linked Forms

M Garcia,

Rather than using the global variable (poor idea for several reasons), you should consider sending the client# to the 2nd form as a parameter via the forms' .openargs.

That way, you can read the .openargs in the form's _open, _load, or _current events (_load would likely be best) and then respond by opening or positioning the (bound?) recordset appropriately BEFORE the form gets UI focus.

Then you can handle the logic for edit or new record appropriately as well.

On 09/27/2022 12:42 PM M Garcia via groups.io <toukey1=yahoo.com@groups.io> wrote:

hello,

I have a form (form1) with a button that opens another form (form2) and I am passing the client# from form1 to form2 by way of a global variable. This is working fine.

This is my problem:
When I open form1 and then click the button for form2, although it has the correct client# on Form2, the other information on the form is what I entered for another client.
My question is when I open form2, how can I get the information for that client alone (if it already exists) or be able to enter a new record for that client?
For example......Client 100 I entered DP# on Form 2. When I go to Client 200, the DP# for Client 100 is showing.

Hopefully the above is clear enough for you to understand. If not, please let me know.

Regards
Toukey

Re: [MSAccessProfessionals] Linked Forms

Hi Toukey,

Have you considered using a subform rather than opening form2? You can open form2 with a where condition in the DoCmd.OpenForm method. This will create a filter for a specific client.

Regards,

Duane

From: M Garcia via groups.io <toukey1=yahoo.com@groups.io>

hello,

I have a form (form1) with a button that opens another form (form2) and I am passing the client# from form1 to form2 by way of a global variable. This is working fine.

This is my problem:
When I open form1 and then click the button for form2, although it has the correct client# on Form2, the other information on the form is what I entered for another client.
My question is when I open form2, how can I get the information for that client alone (if it already exists) or be able to enter a new record for that client?
For example......Client 100 I entered DP# on Form 2. When I go to Client 200, the DP# for Client 100 is showing.

Hopefully the above is clear enough for you to understand. If not, please let me know.

Regards
Toukey

[MSAccessProfessionals] Linked Forms

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

Minggu, 25 September 2022

Re: [MSAccessProfessionals] Latest Office update causes bug's in Access

Do you still have a program to download stock data for Excel 2003?

Thanks

Richard

Another day on the Green

On Jul 26, 2022, at 2:18 PM, Bill Mosca <wrmosca@myself.com> wrote:

Hi all.MS has announced a problem with the latest update. Read this:
https://support.microsoft.com/en-us/topic/error-when-trying-to-open-an-accde-mde-file-created-in-a-different-version-of-access-f4cd36cd-549e-42ba-b75a-dfe964294a81
--

Regards,

Bill Mosca, Founder - MS_Access_Professionals

Microsoft Office Access MVP 2010-2016

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

Selasa, 20 September 2022

Re: [MSAccessProfessionals] MS Access integration with Power BI

You're welcome.

Resuming, the best way to work in your case is, have PowerAutomate assemble a t-sql script, have PA customize that script with replacing placeholders by variable values and throw it with Exec Query in the database. The script can function as odbc client to access data (by itself or calling custom stored procedures). A rich data environment like SQL-server is a far better place to execute vertical functionality in than PowerAutomate. The only thing we had to build a CLR stored procedure for in C# was having T-SQL become a WebAPI client (sample projects on StackOverFlow and GitHub.

Ironically T-SQL is by far the best client application development environment we ever used.

Robl

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of ozairkhalidozair
Sent: dinsdag 20 september 2022 08:23
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] MS Access integration with Power BI

Rob,

Thank you very much!

Ozair

Senin, 19 September 2022

Re: [MSAccessProfessionals] MS Access integration with Power BI

Rob,

Thank you very much!

Ozair

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

Re: [MSAccessProfessionals] MS Access integration with Power BI

Sorry Ozair,

I knew it was working but only now our final findings from 3 years ago (with the help of Ben Clothier) come back to me again. The cData solution will be the simplest. Using SQL-Server you need to C# webservices from us that we can get you for free if you want to use them.

Accessing access tables from SQL-server will work fine but you need an active Access engine to run table macros and VBA.

Kind regards,

Rob

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Rob Koelmans
Sent: maandag 19 september 2022 11:30
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] MS Access integration with Power BI

Ozair,

I just saw this ODBC driver is a 999 dollars. A free and even better solution may be to go by the SQL server connector.

You create a small t-sql script in Power Automate (complete or using one or more stored procedures in SQL-Server). That way you can have SQL-server to format the requested data into JSON format. SQL Server is extremely efficient in that. You would have to check whether SQL-Server Express Edition supports everything but I guess so.

Rob

Connect Access to SQL Server (microsoft.com)

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Rob Koelmans
Sent: maandag 19 september 2022 11:07
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] MS Access integration with Power BI

Hi Ozair,

I found this:

Connect to Access in Power Automate Desktop via ODBC (cdata.com)

In general I would do all interconnectivity on the Microsoft PowerPlatform through PowerAutomate or Azure equivalent LogicApps. Works best.

Remember you can add TableMacros to Access tables to get create fantastic and simple middleware with PowerAutomate. You just fill in the procedure parameters into a dedicated table. It takes you a couple of weeks to build up routine for testing and debugging in TableMacros but you'll be 10 times more productive in there then than in VBA (providing you haven't built a toolset for yourself in ther but event those can be invoked from TableMacros).

Rob

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of ozairkhalidozair
Sent: maandag 19 september 2022 10:45
To: MSAccessProfessionals@groups.io
Subject: [MSAccessProfessionals] MS Access integration with Power BI

Hi,

Presuming we could use Power BI Desktop for free.
So has anyone tried using Power BI reports/visualizations inside MS Access?

Best,
Ozair

Re: [MSAccessProfessionals] MS Access integration with Power BI

Ozair,

I just saw this ODBC driver is a 999 dollars. A free and even better solution may be to go by the SQL server connector.

Rob

Connect Access to SQL Server (microsoft.com)

Hi Ozair,

I found this:

Connect to Access in Power Automate Desktop via ODBC (cdata.com)

In general I would do all interconnectivity on the Microsoft PowerPlatform through PowerAutomate or Azure equivalent LogicApps. Works best.

Rob

Hi,

Presuming we could use Power BI Desktop for free.
So has anyone tried using Power BI reports/visualizations inside MS Access?

Best,
Ozair

Re: [MSAccessProfessionals] MS Access integration with Power BI

Hi Ozair,

I found this:

Connect to Access in Power Automate Desktop via ODBC (cdata.com)

In general I would do all interconnectivity on the Microsoft PowerPlatform through PowerAutomate or Azure equivalent LogicApps. Works best.

Rob

Hi,

Presuming we could use Power BI Desktop for free.
So has anyone tried using Power BI reports/visualizations inside MS Access?

Best,
Ozair

[MSAccessProfessionals] MS Access integration with Power BI

Hi,

Presuming we could use Power BI Desktop for free.
So has anyone tried using Power BI reports/visualizations inside MS Access?

Best,
Ozair

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

Jumat, 16 September 2022

Re: [MSAccessProfessionals] Update Subtotal row with cost center from previous line

oh. Duane.

I like that a lot. Thank You so much

I will put that into production on monday.

Jim Wagner

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Duane Hookom <duanehookom@hotmail.com>
Sent: Thursday, September 15, 2022 10:42 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Update Subtotal row with cost center from previous line

Hi Jim,

You could simply create a totals query that filters out Subtotal:

SELECT [Cost Center], Sum(Charges) AS SumOfCharges, Sum([Usage Charges]) AS [SumOfUsage Charges], Sum(Taxes) AS SumOfTaxes

FROM tblCellCharges

WHERE [Cost Center] <>"Subtotal"

GROUP BY [Cost Center];

Or delete all the records with Subtotal and then create a totals query.

Being a bit OCD about normalization, I would normalize the data with a union query like:

SELECT [Cost Center], "charges" As ExpenseType, Charges As Amount

FROM tblCellCharges

WHERE [Cost Center]<>"Subtotal"

UNION ALL

SELECT [Cost Center], "Usage",[Usage Charges]

FROM tblCellCharges

WHERE [Cost Center]<>"Subtotal"

UNION ALL

SELECT [Cost Center], "Taxes", Taxes

FROM tblCellCharges

WHERE [Cost Center]<>"Subtotal";

**Query1**
Cost Center	ExpenseType	Amount
CC12345 PG11233	charges	$66.54
CC23456 PG23456	charges	$100.00
CC23456 PG23456	charges	$10.00
CC23456 PG23456	charges	$20.00
CC23456 PG23456	charges	$30.00
CC23456 PG23456	charges	$40.00
CC12345 PG11233	Usage	$0.00
CC23456 PG23456	Usage	$6.00
CC23456 PG23456	Usage	$2.00
CC23456 PG23456	Usage	$1.00
CC23456 PG23456	Usage	$3.00
CC23456 PG23456	Usage	$5.00
CC12345 PG11233	Taxes	$1.03
CC23456 PG23456	Taxes	$3.00
CC23456 PG23456	Taxes	$3.00
CC23456 PG23456	Taxes	$3.00
CC23456 PG23456	Taxes	$3.00
CC23456 PG23456	Taxes	$3.00

From: Jim Wagner <josephwagner2@outlook.com>

To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: [MSAccessProfessionals] Update Subtotal row with cost center from previous line

Hello all,
I get a download from verizon with cell phone information. the data includes the cost center which is the account the phone is paid from. and other information like monthly access charges and usage charges taxes etc.
the spreadsheet has the cost centers grouped together with a subtotal for each cost center. Right now i copy data from the spreadsheet into the tables into the database. But I was trying to find a way of parsing out and getting the data to be aligned the tables are.

what i am trying to do is to create a query to get all of the subtotals with the cost centers. I am not sure how to get the associated cost centers to replace the word subtotal
I would like to get it to look like
CC12345 PG11233                             66.54               0.00                1.03
CC23456 PG23456                             200.00              17.00           15.00

I need to copy all of the subtotal information into a summary table but the data looks like below
Cost Center                     charges       usage charges     taxes
CC12345 PG11233             66.54               0.00                1.03
Subtotal                              66.54               0.00                1.03
CC23456 PG23456            100.00             6.00              3.00
CC23456 PG23456             10.00             2.00              3.00
CC23456 PG23456            20.00             1.00               3.00
CC23456 PG23456            30.00              3.00               3.00
CC23456 PG23456            40.00              5.00              3.00
Subtotal                             200.00              17.00           15.00

Is there a way of doing this in Access?

Thank You

Jim Wagner

_._