Re: [MSAccessProfessionals] Access Database Documentation Help

Mama mia! Some IT departments are filled with idiots.

 

Anyway, Access has had built-in mapping tools since 2010. Change your view to "Tables and Related views".

 

But if you want a truly effortless way to do this get Total Access Analyzer from fmsinc.com ; it's not cheap, but it will save you DAYS of tracing back.

 

Regards,
Bill Mosca,
Founder, MS_Access_Professionals

MS Access MVP 2006-2016
My Nothing-to-do-with-Access blog

https://wrmosca.wordpress.com

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Jim Wagner
Sent: Friday, October 14, 2022 3:41 PM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Access Database Documentation Help

 

Thank You Bill,
My tables are in Microsoft Access databases. So I am trying to find where they are in queries and reports. I guess I did not explain the situation well enough. 
The tables from Alteryx exported to excel. 
then I take those tables and distribute them to 15 databases that are specific for the table. I am not allowed to have a backend database because they do not trust it for all of the databases. 
Years ago I asked for a SQL server to host the tables. I was told that SQL was not secure. I laughed so hard. Even though the University has SQL everywhere being used. 

Jim Wagner

Re: [MSAccessProfessionals] Access Database Documentation Help

Thank You Bill,
My tables are in Microsoft Access databases. So I am trying to find where they are in queries and reports. I guess I did not explain the situation well enough. 
The tables from Alteryx exported to excel. 
then I take those tables and distribute them to 15 databases that are specific for the table. I am not allowed to have a backend database because they do not trust it for all of the databases. 
Years ago I asked for a SQL server to host the tables. I was told that SQL was not secure. I laughed so hard. Even though the University has SQL everywhere being used. 

Jim Wagner

_._,_._,_

---

Groups.io Links:

You receive all messages sent to this group.

View/Reply Online (#116318) | Reply To Group | Reply To Sender | Mute This Topic | New Topic
Your Subscription | Contact Group Owner | Unsubscribe [sugeng.panjalu.access@blogger.com]

_._,_._,_

Re: [MSAccessProfessionals] Access Database Documentation Help

Hi Jim

 

If I remember correctly, PeopleSoft uses SQL Server as a backend. There are lots of analyzing software that could help you track down the fields/tables. Back when I was a SQL Server DBA I used https://www.apexsql.com/ tools, but knowing Microsoft's penchant to gobble up other company's ideas, SQL Server might already have something built in.

 

Regards,

Bill Mosca

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Jim Wagner
Sent: Friday, October 14, 2022 10:03 AM
To: MSAccessProfessionals@groups.io
Subject: [MSAccessProfessionals] Access Database Documentation Help

 

Hello all,

For the last 17 years I have worked at this job, we have had access to specific data that we have built queries and reports based on this data and make hiring and leave decisions on. Somewhere along the way we now have Data Stewards that have decided to take this data away. Even though it can be seen in PeopleSoft. I guess these data stewards after 17+ years have decided to do their job and deem these columns too sensitive for eyes in a table but not in PeopleSoft. This change is happening pretty quickly so I am really nervous that I will be without data soon.

My question now is with 15 databases that I support, how do I find out where these fields are in every query and report? I download the data using Alteryx into Excel and then import the data into Access and then send the updated tables to each of the respective databases. 

I could use the documenter and do a search on the PDF but if there is another way to find these fields, I am listening, intently to others for guidance. 

Thank You

Jim Wagner

[MSAccessProfessionals] Access Database Documentation Help

Hello all,

For the last 17 years I have worked at this job, we have had access to specific data that we have built queries and reports based on this data and make hiring and leave decisions on. Somewhere along the way we now have Data Stewards that have decided to take this data away. Even though it can be seen in PeopleSoft. I guess these data stewards after 17+ years have decided to do their job and deem these columns too sensitive for eyes in a table but not in PeopleSoft. This change is happening pretty quickly so I am really nervous that I will be without data soon.

My question now is with 15 databases that I support, how do I find out where these fields are in every query and report? I download the data using Alteryx into Excel and then import the data into Access and then send the updated tables to each of the respective databases. 

I could use the documenter and do a search on the PDF but if there is another way to find these fields, I am listening, intently to others for guidance. 

Thank You

Jim Wagner

_._,_._,_

---

Groups.io Links:

You receive all messages sent to this group.

View/Reply Online (#116316) | Reply To Group | Reply To Sender | Mute This Topic | New Topic
Your Subscription | Contact Group Owner | Unsubscribe [sugeng.panjalu.access@blogger.com]

_._,_._,_

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.

View/Reply Online (#116315) | Reply To Group | Reply To Sender | Mute This Topic | New Topic
Your Subscription | Contact Group Owner | Unsubscribe [sugeng.panjalu.access@blogger.com]

_._,_._,_

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.

View/Reply Online (#116313) | Reply To Group | Reply To Sender | Mute This Topic | New Topic
Your Subscription | Contact Group Owner | Unsubscribe [sugeng.panjalu.access@blogger.com]

_._,_._,_