Microsoft Ignite 2018 recap: Access team showcases new data visualization features
Published Oct 18 2018 11:35 AM 11K Views
Copper Contributor

Microsoft Ignite 2018 has come and gone, but there’s still time to catch up on the Access content that was shared!

 

Michal Bar, Senior Program Manager for Access, and Tim Getsch, founder and CEO of COMC.com and former Access Program Manager, attended the conference on behalf of the Access team. Over the course of the week, Michal and Tim met and discussed Access with customers and developers from all over the world, and presented an engaging session highlighting the new visualization features in Access, “Modern data visualization in Microsoft Access: Enhance your forms and reports.” The session provided in depth demonstrations and customer use cases for the new Access charting and visualization features.

If you’d like to catch up on these exciting new features, check out the recording of the presentation:

 

 

Want to try it out for yourself? Download the data for Michal’s demo here, or try out discovery of Pivot Chart forms and checking for version-specific forms and reports using Tim’s sample code, here.

 

Edit November 29, 2018: By popular request, Tim has produced an additional code sample, showing how to open a PivotChart in Design View. Please note that this is a rough proof of concept; as such, it will not cover all use cases, and should be tweaked per application. 

15 Comments
Iron Contributor

@Michal_Barand  @Tim Getsch,

The new Pivot Charts in  Microsoft Access are absolutely incredible!

 

Together with Modern Charts they truly make Access a standalone alternative to Excel in so many cases, especially multi-user databases with user-friendly data entry and automated reporting/calculation and now for Data Visualization as  well!

 

And that's in addition to MS Access already being an attractive alternative to (or even front-end for) SQL Server and far easier to automate (with VBA, Macro & Query Design, Data Macros), extend (via VBA, C#, VB.NET, Addins, user-defined functions for Queries and Macros) and easier to deploy than PowerApps or any other option out there.

 

Thanks so much for bringing those new features (as well as any other exciting new tools and features of late, ranging from the new Linked Table Manager and Northwind template to Salesforce & Dynamics connectors).

 

Michal and @Elisabeth Jones, I also appreciate your making the MS Ignite Pivot Chart sample databases and Chart Helper VBA code from your presentation publicly available and pointing that out, as I'd love to see others do with more such sessions.

 

Thanks,

Dan

 

--

Dan Moorehead

PowerAccess – All-in-One Toolset | Framework for Microsoft Access

https://PowerAccess.net

Copper Contributor

@Sasha, please tell me how someone can outgrow Access because I have been trying for years.  Earlier this year I hit 4,000 forms and reports combined in my application. I am slowly heading towards 5,000.  I have the application talking, mapping physical addresses, and creating dynamic infographics.  I have absolutely no use for cloud.

Iron Contributor

Well said, David.

 

Microsoft Access even works well even for multi-user databases and I’ve even gotten it running on iPads over cellular with multiple app-window-only RDP sessions hosted from a single desktop PC over cellular connections.

 

You can also use Access with Power BI (via the On-Premises Data Gateway) for web and mobile access to Access, providing even more data visualization, analytics and BI dashboard tools for use with it, if/when needed (though with new Modern Charts & even Pivot Charts, as well as the wide range of other Access features available, I rarely find that it is necessary).

 

On top of that, you can easily extend and automate Microsoft Access as needed, through VBA, C# / VB.NET Add-in automation project development, and through new solutions such abs the PowerAccess All-In-One Toolset & Framework for Microsoft Access — for which you can subscribe at www.PowerAccess.net for early access to PowerAccess.

 

PowerAccess, for example, extends Access with dozens of new empowering tools and 800+ new functions and out-of-box common database features — like tools for CodeGen for VBA/SQL/Data Macro/RegFree .NET use, Database Builder tools (for Table/Field/Query/Macro generation), a new integrated SQL Editor with Intellisense, simplified & automated PowerGit versioning/edit merging, Global Find & Replace, Deployment, Auto Renaming, Template Injection, as well as PowerAccess Framework with its out-of-box support (install-free, via embedded/linked function library database) for everything from User Permissions, Edit Tracking, Logging, Telemetry, Calculated Tables, Auto-Relinking, Smart Combo Boxes, ZIP Codes/Cities, and Fuzzy Matching to SQL & VBA functions for Finance, BI, Analytics, Power Query-like PowerSQL, Excel and XPrevRow() (for Excel formulas in Access).

 

With such web/cloud/mobile multi-user use of Microsoft Access and Modern (even Pivot) Charts, combined with new tools and frameworks — like the PowerAccess All-In-One Toolset & Framework — there is absolutely no need to move away from Access, to the cloud or otherwise.

 

Access developers can and should continue to benefit from the rapid, simplified database creation enabled by MS Access and the unique advantages it provides for easy editing and maintenance even by non-technical end-users, as well as it’s ubiquity (with Access included in most Office editions and therefore available on many of the 1.2 billion PCs with Microsoft Office installed).

 

Access will always maintain it’s role as one of the most revolutionary solution development, data management and reporting tools ever developed, as it truly makes database creation accessible to anyone, technical and non-technical users, making it possible for even end-users & subject matter experts (like Financial Analysts and Sales Managers) to easily migrate complex, massive tedious Excel workbooks & workflows to compact, centralized, drop-in-folder deployed multi-user databases with auto-calculating reports and user-friendly data entry forms, all without needing to involve IT or hire consultant for every little change for their frequently changing business rules and formulas.

Copper Contributor

@David. Microsoft Access has several weaknesses or strengths, depending on your prospective. What makes it easy to use (single file) also makes it brittle from the prospective of change management, security and scalability.

 

Simply stated, the solution requires many users performing very complex business functions across a vast geographic landscape, then the technology is not appropriate.  Yet, it's the best working prototyping platform or work-group level software in the world.

 

At Help4Access.com, we help large clients with decades of legacy technical debt built on MS Access, migrates critical business functions to more robust technologies while supporting their existing MS Access applications during the transition period.

 

To read about some of these business cases, please see the following URL.

https://www.help4access.com/services/migration-services/

 

To answer your cloud question, MS Access migrated to the cloud allows for integrating it's data with other web services (SaaS) applications. I feel that it's this ability to share data with web applications makes or extends Microsoft Access's worth to many businesses.

Deleted
Not applicable
Unless someone can tell I'm incorrect (please tell that I am) If your MS Access encapsulated file size grows to 2GB, you have now out grown MS Access short of trickery via splitting files.
Copper Contributor

@Larry:  I'm sure there is a workaround, but the better question is, should you even try?  A Microsoft Access database always has the potential to get corrupt, so, if you have 2 gigs of valuable data that's updated often, by many people, then you definitely should be moving towards Microsoft SQL Server for the backend.  However, if you don't really care about the data loss, then stay in a pure MS Access frontend to MS Access backend architecture.

Iron Contributor

A typical model for the most massive of Access databases is to have an .accdb file per large primary table (for just those tables which approach 2GB in size), which is quite feasible to manage with Linked Tables (especially with the new Linked Table Manager in Office 365). This has been done with success (like discussed here, and in Access' ACE/JET Database Engine article here) for a 1 Terabyte database with 5 billion records. Also, Access supports 255 simultaneous users (along with unlimited authorized users) which can be upscaled to unlimited simultaneous users, as even larger databases simply by converting Tables into Linked Tables pointing to a SQL Server backend, using automated tools like SSMA and PowerAccess2SQL to automate that.


In fact, such Access databases provide out-of-box support for datasets nearly as large as SQL Server Express, and have the ability to scale far beyond what SQL Server Express can support. SQL Server Express is limited to just 1GB memory use (as is especially important for SQL Server) and SQL Server Express 2008 is limited to just 4 GB for the maximum database size, or 10 GB for 2012+.

 

However, unlike Microsoft Access, SQL Server can't easily (or isn't usually configured to) work around such limitations by converting the largest Table(s) to "Linked Table + .accdb per Table", which effectively results in a "2 GB per Table" size limit for Access instead. Unlike SQL Server Express, Access supports 2GB  of memory, and supports 1 GB per recordset (or set of results returned by running a query, per execution per user), instead of being limited to 1 GB for all memory used by SQL Server (including all memory-based storage requirements and processing activities shared by all simultaneous user connections).

 

Also, unlike the severe 1 GB memory limit for SQL Server Express, Access 64-bit supports effectively unlimited (8,000+ GB) memory use.  Instead, those interested in working around those limitations, or the many other features not available with SQL Server Express, it costs $7,500+ (or per server + per allowed end-user PC) even just to license just SQL Server Standard (let alone Enterprise) edition for 2 CPU core minimum. That doesn't even factor in the major cost and complexity of SQL Server setup, hosting, maintenance (with even just basic install being beyond most users), and much higher cost (plus need to hire consultants or involve IT) even just to tweak a query/view calculation or add a field or user.

 

I've seen 1+ GB databases even reduced down to 3 MB after normalizing and optimizing the data/design, making Access more than enough (the best fit even) and far better performance than just moving as-is to SQL Server (or, in in a case such as this, even better than optimizing *and* moving to SQL Server).   The huge difference that basic Data Normalization, as well as manual optimization, can make should not be discounted.  In fact, Microsoft Access even provides built-in tools for this, such as Analyze Table and Analyze Performance under the Database Tools ribbon, with wizards to help you to normalize your data, simplifying splitting imported spreadsheets into multiple tables with lookups.

 

That said, I would suggest considering SQL Server backend for Access databases that large, or before approaching 255 simultaneous users.  But, even then, Access remains an excellent option (and sometimes the only viable option, if needs to be end-user editable/maintainable) for such databases. Often when I saw the term migration thrown around, such solutions still remain Microsoft Access database solutions, just with Tables replaced with Linked Tables pointing to data stored on SQL Server.

Iron Contributor

@Sasha Froyland, when you suggest the need for "migrating away from Access", are you instead suggesting here (or often end up suggesting to clients) updating existing (or developing new) Access database "all-in-one solutions" (complete with forms, queries, reports, macros, tables, data macros, settings, import presets, VBA, and all) and just migrating the *data* to SQL Server (by converting Tables to Linked Tables with SQL Server backend) if/when database size or number of simultaneous users reaches there point to merit it. If so, I am fully on board with that.

 

However, in many cases where I've seen suggestions or discussions of the need for "migrating from Access", I believe it's worth clarifying that in many cases that does not mean "(fully) migrating away from  Access" or even that "Access can't handle it" since SQL Server-backed Access databases are a very typical use case and type of Access database solution which Microsoft Access is specifically designed to handle well. 

 

In such cases, I think it's worth emphasizing that you are "optimizing, enhancing their Access or splitting (across types of backends) their database", instead of "redeveloping it" or "migrating completely away from Access", not just for accuracy, but because that plays to Access' strengths, leaving them with the best of both worlds, with SQL Server-backed scalability and performance together with all the benefits of Access, with ease of use/editing/extending/maintenance, as well as its ubiquity and avoided need for vendor-lock-in or need for ongoing reliance on consultants/IT for every change. Though I would suggest engaging consultants for significant changes, the customer can still appreciate the reduced risk and potential cost savings of that not being entirely required in all cases like with custom web apps. 

 

In short, I suggest that more people refer to "Migrating Access databases to SQL Server backend use for improved performance and multi-user scalability" or "In-place upgrading of Access databases for optimized, scalable SQL Server backend use without requiring re-training or changes to your existing workflows or losing the low-cost, flexibility, and unique end-user-editability advantages that Access provides" or even just "Migrating Access to 'Access + SQL Server' the 'best of both worlds'".

Iron Contributor

Even professional SQL Server developers frequently use Microsoft Access for rapid database prototyping, before easily "upscaling" to SQL Server (via SSMA, PowerAccess2SQL, or similar too), as well as even long-term (as a front-end with Forms, Reports, Ad-hoc User-Editable Queries, Import/Export Saved Steps, Macros for their SQL Server database, instead of the order of magnitude greater complexity, cost and delay in developing and maintaining a custom Web App for that.

 

Microsoft Access was specifically designed to make this "upsizing" to SQL Server process as painless as possible, even including the Upsizing Wizard as a built-in tool (under Database Tools > Move Data > SQL Server) before split off (since Access 2010) into a standalone tool.

 

Not only does this result in 10x+ lower cost vs. custom Web App + SQL Server development, but use of Microsoft Access is even critical - or the only viable option - in many cases, such as where there are frequently changing business rules/requirements (eg. compensation, tax. etc. calculation rules and forecasting formulas) or where users need to be able to customize themselves (eg. with Financial Analysts or Sales Manager adding/editing fields, ad-hoc queries, data entry-forms, reports, edit calculation formulas and variables, or even links to Excel workbooks/CSV files/data sources) without needing to hire consultants or bring in IT (if they even have dedicated IT staff) for every little change.

 

However, because SQL Server backend (as well as Split Frontend + Backend databases) complicates making edits and can much increase the cost of development (though still an order of magnitude less expensive than custom web app + SQL Server database, even then) I would suggest delaying until the end or only migrating to SQL Server backend if/when shown to be needed based on daily usage performance, even after optimization. 

 

Also, like Power Query, Access excels at being a mashup engine where you can combine data from SQL Server (with Tables and Query-like Views as Linked Tables), Web Services, Salesforce, SharePoint, other Access databases and even Excel workbook (preferably in XSLB format) as Linked Tables.

Iron Contributor

For reference, by "effective data normalization / optimization / compression", specifically I am referring to some basic design principals and tips, including:  using value list/lookup tables (vs. text fields directly in most tables) (as the built in Analyze Performance/Table wizards can help users to automate), avoiding repeated values in subsets of record fields (eg. Contact Info shared by Company/Project/Employee tables vs. inlined and duplicated, and Product.Category.Group vs. Product.Category+Product.Group), avoiding Calculated Fields (using Queries to auto-calculate), use of Inherited Values + Infrequent Overrides (ProductGroup.IsTaxable vs. ItemTax), avoiding sparsely populated fields (replacing Fax 1, Fax 2, Work Phone 1, Work Email, Company Address, fields with RecordID+Label/AttributeType+Value fields, in some cases), use of Lookups/Numeric Keys (vs. Text IDs), appropriate data type sizes (Byte/Integer vs. AutoNumber/LongInt/GUID, Single/Currency vs. Double/Decimal, Number vs. Text), enabling Unicode Compression for text fields, and occasional Compact & Repair (which can be automated).

 

Such basic data normalization (which can be assisted even by wizards built into Access) combined with support for up to 255 simultaneous user connections and one-click (even or even scheduled/automated) "Compact & Repair", together with the easy built-in ability to split/migrate/mashup data (whether across multiple Access databases, files, etc.), new & improved Linked Table Manager for managing those data source links, simplified "file copy" deployment, lack of licensing fees, ability to even exceed database and memory limits of SQL Server Express, lack of expensive licensing/hosting cost & complexity requirements ($7500+ licensing + hiring consultants for every minor change and ongoing IT maintenance/hosting) of SQL Server, and 10+x faster and lower-cost of development and maintenance, ubiquity (with nearly 1.2 billion installs and many millions familiar with it), and unique Excel-like ability to allow end-user editing (for rapidly changing business requirements, by subject matter experts like Analysts) nearly as easily as Excel workbooks – all together makes Access not just sufficient, but in many cases the only viable option for most database projects.

 

It's for that reason that, without Access, you would see millions more of the far-too-common massive 100-sheet spreadsheets with complex, tedious lookup & calculation formulas and copy-paste workflows (with no other database solution feasible or cost-effective, and with SQL Server as a backend-only half-solution) instead of "automated, user-friendly, drop-down editing, drop-in-folder-deployed, auto-calculating, fast, compact all-in-one Access database solutions."

Copper Contributor

Microsoft Access is a natural extension from Excel and gives the business user the ability to develop quick work-group solutions at low cost. It's basically free and already installed on many corporate computers so it's accessible by business thought leaders. There will always be these types of business user friendly tools available.

 

After years of accumulating technical debt, these simple MS Access database applications (which are working prototypes of potential enterprise IT solutions) become brittle and unable to continue to scale.  These immature legacy systems require senior IT professions to re-architecture to remain effective.  (Sorry, there is no silver bullet).

 

Each instance is different, like each patient entering a hospital is different.  The general protocol for nurturing and maturing a corrupting MS Access system is similar across all cases (think common cold) but each individual instance is different and requires a careful examination of the business requirements in order to prescribe the best individual treatment plan.

 

Not all MS Access systems deserve to be invested in to promote to more modern version. 

 

 

In large organizations, Microsoft Access fills a gap between IT Enterprise systems. When developing a risk mitigation plan, you must examine the target solution from the business, data, application and technical prospective.  Often the first step is performing either a discover across the client's network to build an IT asset inventory.  After further investigation, the best candidates (highest business risk) can be identified and then further examined to customize the treatment plan.

 

In general, if you have a simple MS Access database application, then migration path is simple. 

 

If you have 65,000 MS Access databases like Safeway/Albertson, then the path forward is much more complex because your treating an epidemic of instances and not a single instance.  https://www.help4access.com/ms-access-risk-mitigation-pt1/

 

My hope here, is to elevate the discussion from a pure technical discussion to a more holistic approach.

 

If you would a private consultation, please book an appointment here:  https://www.help4access.com/schedule/

 

Kind regards,

Sasha Froyland - President / Enterprise Architect

www.Help4Access.com 

 

 

 

Copper Contributor

Sasha, the Safeway/Albertson situation is definitely one that needs attention.  Before discussing what to do with all of those databases, first tell us how and why this happened.  How many departments and people contributed to this situation?  Which third-party programs are integrated with Access?  I am sure a few of them are GIS (e.g., ESRI, Intergraph, Autodesk, MapInfo).  Are some of their database integrated with SafesForce or Dynamics? 

 

Thanks.

 

Brass Contributor

At 25:10 in the video you see Tim using the macConvertPivotChartToModernChart macro. Where can I get such macro?

Copper Contributor

This stinks!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Copper Contributor

I appreciate your work 

Version history
Last update:
‎Nov 29 2018 01:03 PM
Updated by: