Microsoft Ignite 2018 recap: Access team showcases new data visualization features

I appreciate your work 

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

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

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.

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 

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."

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.

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'".

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.

@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.