Different Types of Access Queries

By now you should be pretty familiar with the standard Select query, used for displaying information in a datasheet format or for the record source for a form or report. There are number of other types of queries that can be used. Here are some of the other types of queries and where you could use them:

1. Action queries: Lets you perform bulk operations such as add new records (Append query), update records (Update query), delete records (Deleting query), and create a whole new table (Make Table query).
2. Crosstab queries: Displays a cross-tabulation of data, similar to Microsoft Excel's Pivot Table. An example of this is to sum up all the hours worked for all the projects across various months.

You can choose the queries just listed from the Query Type toolbar button, displayed in the MouseOver below, with the various types of queries available from the toolbar button.

The Access Dataverse Connector with Power Platform^[1] is a new feature in Microsoft Access 2024 that enables seamless integration between MS Access and Microsoft Dataverse, a cloud-based data storage solution within the Power Platform ecosystem. This feature allows users to leverage the power of cloud computing, mobile access, and integration with Microsoft Teams.
How It Works
The Dataverse Connector allows Access databases to interact with Dataverse, a low-code cloud data storage platform that enables secure, scalable, and collaborative data management across multiple Microsoft services. Here’s how it benefits Access users:

1. Cloud-Based Data Storage
   • Instead of storing data locally in Access tables, users can move their data to Microsoft Dataverse.
   • Dataverse stores data in the cloud, ensuring better security, accessibility, and backup capabilities.
   • This eliminates the limitations of Access’s on-premise data storage and allows data to be used across different devices.
2. Mobile Access
   • Since data is now stored in Microsoft Dataverse, it can be accessed from mobile devices using Power Apps.
   • Users can build mobile-friendly apps that interact with the same datasets stored in Dataverse, allowing remote access to data without needing Access installed.
   • Mobile users can update records in Power Apps, and those changes instantly reflect in MS Access.
3. Integration with Microsoft Teams
   • Dataverse is natively integrated with Microsoft Teams, allowing teams to collaborate in real-time.
   • Users can embed Access data within Teams and share records without manually exporting or emailing Access files.
   • Teams users can view and edit data stored in Dataverse directly within the Teams interface, ensuring a single source of truth for all users.
4. Power Automate and Power BI Integration
   • Power Automate: Automate workflows such as sending emails, creating alerts, and triggering actions based on data changes in Access.
   • Power BI: Users can create advanced analytics dashboards by connecting Access data stored in Dataverse to Power BI, gaining better insights.
5. Secure and Scalable Data Management
   • Role-based access control (RBAC): Secure data access by defining user permissions.
   • Data backup an recovery: Dataverse offers built-in redundancy and disaster recovery, which is superior to traditional Access file backups.
   • Multi-user access: Unlike traditional Access databases, which can be slow when multiple users access them, Dataverse enables simultaneous multi-user collaboration.

Summary of Benefits

Feature	Traditional MS Access	Access Dataverse Connector
Data Storage	Local MDB/ACCDB files	Cloud-based Dataverse
Accessibility	Desktop only	Mobile, Web, and Desktop
Multi-User Collaboration	Limited (file-sharing issues)	Seamless multi-user access
Security & Compliance	Local file-based security	Role-based cloud security
Integration	Limited to ODBC, SQL	Microsoft Teams, Power Apps, Power Automate

Conclusion: The "Access Dataverse Connector with Power Platform" modernizes MS Access by bridging the gap between traditional desktop databases and cloud-based enterprise solutions. This allows users to securely store, access, and collaborate on their Access data from anywhere, making it a powerful addition for businesses and enterprises that rely on Microsoft’s ecosystem.

In addition to the queries listed, there are total queries, which are used for displaying aggregate information, such as summing up and displaying all the hours worked on a project. The Totals queries and others listed above will be covered in the rest of this module. Other queries available are more advanced in their uses. Those types of queries are SQL specific, and are the Union, Pass-Through, and Data Definition queries. They can be found on the SQL Specific submenu, located on the Query menu choice in Query Design mode. These queries will not be discussed in this course.
In the next lesson, you will learn how to create action queries to perform bulk operations.

[1]Access Dataverse Connector with Power Platform:The Access Dataverse Connector bridges the gap between traditional MS Access databases and the cloud-based Power Platform, enabling users to migrate and leverage their existing Access data within a modern cloud environment. This connection transforms on-premise data into a secure, scalable, and accessible resource through Dataverse, allowing integration with Power Apps, Power Automate, and Power BI for enhanced functionality and collaboration. Ultimately, the Access Dataverse Connector facilitates the evolution of MS Access within the context of cloud computing by unlocking advanced capabilities and fostering seamless data management across the Power Platform ecosystem.