Contact Tracing is critical to minimizing the spread of COVID-19 in your community. Coronavirus infections are particularly dangerous to vulnerable populations such as retirement homes, elderly family members, medical providers, and front-line service personnel. By collecting and managing the data of your population, you are prepared if and when an outbreak occurs.
The FMS Advanced Systems Group developed Sentinel Visualizer to visualize complex relationships hidden in traditional rows and columns. For instance, schools can store relatively static data on their students, teachers, staff, classes, clubs, sports, siblings, family members, etc. When positive tests occur, one can find common links to quickly identify those who need to be tested and quarantined while limiting the impact on the wider community.
Ebo Quansah from the Microsoft Access Team announced problems with decimal fields using the current Microsoft Access Build 12827.20010 that was released with the Monthly cycle. Decimal fields are not commonly used in Access database files, but they are used in linked SQL Server tables.
June 24, 2020 Update
The Microsoft Team has announced that the Decimal Field issue has been fixed in all channels.
Current Channel – Version 2005, Build 12827.20470 (User may need to force a manual update to get the version.)
Current Channel (Preview) – Version 2006, Build 13001.20198
There’s a new build update (13001.20198) released on June 18, 2020 for Current Channel (Preview) that has been reported to solve the issue related to using SQL/VBA to write to a Number Data type with a Decimal field size.
June 8, 2020, from Ebo Quansah
Very soon, we will ship a new data type, known as Date & Time Extended, which enhances syntax compatibility with SQL while increasing accuracy & level of detail in date & time records.
While the feature is not yet enabled in Current Channel builds, most of the code for the feature is in the product in an inactive state. Nonetheless, we are aware of a problem that currently exists with this new code. As of today, if you are on version 2005, build 12827.20010 or greater, and you manipulate Decimal fields (Access DataType=Number/FieldSize=Decimal, or SQL DataType=Decimal) using DAO (Data Access Objects), you might have identified your app crashing.
If you hover a variable with the type in VBA code, you may see that the field is not being displayed properly; either reading as ‘?????’ or giving a Type Mismatch error, or Access may crash.
We are very sorry for the inconvenience this causes in your Access app. The Access team is working on resolving this issue as soon as possible, and we will report back to you once this error has been fixed. In the interim, we’d recommend for you to roll back to a previous version (https://support.microsoft.com/en-us/help/2770432/how-to-revert-to-an-earlier-version-of-office-2013-or-office-2016-clic), or switch to a slower channel (e.g Monthly Enterprise Channel, or SemiAnnual Channel), until the issue is fixed in Current Channel.
The issue only impacts Decimal types so if avoiding DAO code that manipulates Decimal types is possible, we’d advise this as a short term solution.
FMS President Luke Chung was a presenter at the Virtual Microsoft Access DevCon 2020 in Vienna, Austria on April 23, 2020. He gave a presentation called “Remote access to Access“, which is available for everyone to watch.
If you missed the event, you can visit Virtual Access DevCon 2020 to watch all the presentations giving by all of the speakers.
Luke Chung’s video presentation
Remote Desktop and RemoteApp let your users run Access applications without having to installing anything on their local machine. That includes Access, the database, and any related programs. It lets users run the program across the network or Internet, from their PC or even a Mac. There are different approaches depending on whether the host is internal or in the cloud, and for backend databases in Access and Microsoft SQL Server.
PowerPoint Slides for the Presentation
Special thanks to Microsoft Access MVP Karl Donaubauer, who hosted the fourth annual Access DevCon and made it an online event during the COVID-19 lockdown.
An important announcement from the Microsoft Access team addresses the problems with connecting to Access ACCDB databases from other programs.
The ACCDB database format was introduced with Access 2007 and offered a new Access Database Engine (ACE) for external programs to connect to it. Connecting to the earlier MDB database format was never an issue because that requires Data Access Object (DAO) which is part of Windows.
Connecting to Microsoft Access Databases Outside of Access
ACE was available when Access 2007 and 2010 were installed. However, later Access versions sandboxed ACE so only Office could use it. It prevented other programs, including Microsoft programs such as PowerBI and the SQL Sever Migration Assistant (SSMA), from using it to support ACCDB databases.
The solution was to separately install the ACE Redistributable which provided ACE OLEDB (Microsoft.ACE.OLEDB.16.0, or Microsoft.ACE.OLEDB.12.0). That was a hassle and complicated because
Not every user had permissions to install it
Installations were 32 or 64-bit specific
Even if it were installed, it could be out-of-sync with the Access version that is installed.
This was especially frustrating because when Access is installed on the machine, it includes ACE but simply didn’t allow other programs to use it. And because Access/Office 365 was constantly being updated, its version of ACE may support features that the redistributable didn’t, creating conflicts.
ACE is Now Available with Access
With this Microsoft announcement, ACE is now exposed and available for external programs to use it.
If you have Office 365, or click-to-run versions of Access 2016/2019 Consumer installed, you no longer need to install ACE to support external programs.
This change enables previously unsupported scenarios, including Microsoft programs, to connect to Access ACCDB databases without installing ACE. It eliminates incompatibility issues between different versions of ACE. It also helps our programs Total Access Admin, Total Access Startup, and Total Visual Agent connect to Access ACCDB databases directly.
A set of Microsoft Office security updates released on November 12, 2019 causes Access databases to fail when it runs Update Queries to modify data. An error like this appears when the query is run:
Error 3340: “Query ‘qryName’ is corrupt”.
It doesn’t matter if the query is against a table in the current database, a linked table, or a linked SQL Server table. If the Access database engine is processing the UPDATE query, the error occurs.
In addition to Microsoft Access, other programs that update Access databases may also be affected. That includes Excel, PowerPoint, Word, etc. and programs written in Visual Studio .NET, VB6, and web applications.
Types of Update Queries Affected
When attempting to run an Update query, it may fail with the error: “Query ‘query name’ is corrupt”. This occurs for an UPDATE query that:
Updates a single table (i.e. it updates a table, rather than the output of a Select query or join)
Specifies a WHERE clause (i.e. has entries in the Criteria row in the query designer)
These queries can be saved Access query objects or SQL strings executed in VBA code (or other languages that use ACE).
Security Updates Causing Query is Corrupt Error 3340
The issue was introduced on November 12, 2019 via the following patch updates for MSI builds:
Office 2010: Description of the security update for Office 2010: November 12, 2019 (KB4484127)
Office 2013: Description of the security update for Office 2013: November 12, 2019 (KB4484119)
Office 2016: Description of the security update for Office 2016: November 12, 2019 (KB4484113)
Office 2016: Update for Office 2016 – November 12, 2019 (KB3085368)
Microsoft announced they’ll fix this in the December update, but that’s way too long to wait. We hope Microsoft will respond more quickly. Until then, we found multiple solutions to address this issue.
Current Microsoft Fixes
Here are the current Microsoft fixes for the issue.
There is a December 10, 2019 security update for the MSI builds, that will be available via WSUS (Windows Server Update Services) and will be automatically applied that fixes the issue.
Access 2010: KB4484193 – Build 14.0.7243.5000
Access 2013: KB4484186 – Build 15.0.5197.1000
Access 2016: KB4484180 – Build 16.0.4939.1000
Note: If you try to apply the patch and you receive a message that says “No products affected by this package installed in the system”, this means you have a click-to-run (C2R) installation of Office, rather than an MSI installation.
Access 2010 MSI, Access Database Engine 2010 Redistributable: Fixed Build 7241.5001 – November 27, 2019 This update is only available for manual download and installation from the Microsoft Download Center. To manually download the update, visit November 27, 2019, update for Office 2010 (KB2986256). Organizations that want to distribute the update without requiring each user to install manually, visit Distribute product updates for Office 2010 for more information.
Access 2010 C2R: Fixed Build 7243.5000 – December 10, 2019 Open an Office program, select [File], click [Account], click [Update Options] and select [Update Now].
Access 2013 MSI: Fixed Build 5189.1002 – November 27, 2019 This update is only available for manual download and installation from the Microsoft Download Center. The update can’t be installed on Office Home and Student 2013 RT. To manually download the update, visit November 27, 2019, update for Office 2013 (KB2965317). Organizations that want to distribute the update without requiring each user to install manually, visit Distribute updates for Office 2013 products for more information.
Access 2013 C2R: Fixed Build 5197.1000 – December 10, 2019 Open an Office program, select [File], click [Account], click [Update Options] and select [Update Now].
Access 2016 MSI, Access Database Engine 2016 Redistributable: Fixed Build 4927.1002 – November 18, 2019 This update is only available for manual download and installation from the Microsoft Download Center. To manually download the update, visit November 18, 2019, update for Office 2016 (KB4484198).
Access 2019 Volume License: Fixed Build 10353.20037 – December 10, 2019 Open an Office program, select [File], click [Account], click [Update Options] and select [Update Now].
Access O365 Monthly Channel/Access 2016 C2R/Access 2019 (Version 1910): Fixed Build 12130.20390 – November 18, 2019 Open an Office program, select [File], click [Account], click [Update Options] and select [Update Now]. For more information on the update, visit Version 1910: November 18.
Access for Office 365 (Microsoft Store Version): Fixed Build 12130.20390 – November 22, 2019 Open Microsoft Store, Click on […] in the upper right corner, Choose [Downloads and Updates]
Access for O365 Semi-Annual (Version 1808): Fixed Build 10730.20422 – November 22, 2019 Open an Office program, select [File], click [Account], click [Update Options] and select [Update Now]. For more information on the update, visit Version 1808: November 22.
Access for O365 Semi-Annual (Version 1902): Fixed Build 11328.20480 – November 22, 2019 Open an Office program, select [File], click [Account], click [Update Options] and select [Update Now]. For more information on the update, visit Version 1902: November 22.
Access for O365 Semi-Annual (Version 1908): Fixed Build 11929.20494 – November 22, 2019 Open an Office program, select [File], click [Account], click [Update Options] and select [Update Now].
Solutions
Uninstall the Security Updates
The best way to fix the problem is to uninstall the Security update for Office which is the source of the problem. There are different steps depending on whether you are on an Office 365 subscription or not.
Modify All Your Update Queries
If your solutions are deployed to users where you cannot uninstall their Security Updates, you can modify your queries so they don’t trigger the problem. This can be done by adjusting the queries or replacing them with recordsets updated in code.
Rename each table and create a query selecting it with the original table name. Need to adjust table references.
Deploy your Access application with Access 2007 or earlier. You can download the free Access 2007 runtime from our site.
FMS President Luke Chung attended the 2019 Portland Access User Group Conference in Silver Falls, Oregon. While there, Luke explored the beautiful Silver Falls State Park with fellow Microsoft Access Developers. Here are some of the breath taking photos. For more information on the PAUG 2019 conference, visit the Portland Access User Group site.
Microsoft Access Developers Molly and Alison Balter.
Here are some photos from the 2019 Netherlands Access Developer Day Conference. FMS President Luke Chung gave a presentation on the Microsoft Access Database Evolution from the Desktop to the Cloud. For more information on the NADD 2019 conference, visit Netherlands Access Developer Day.
Luke speaking at the NADD Conference.
Luke talking about the Access products FMS provides.
Luke giving his presentation on Access Database Evolution.
Luke and other Microsoft Access Developers at the NADD 2019 Conference.
Luke enjoying the beautiful canals in Amsterdam.
Luke went and visited the Maritime Museum.
Inside the VR Room on the ship. This let you experience what life a board the ship would have been like.
FMS President Luke Chung and Daniel Polak inside a Cheese Shop.
Microsoft Office 365 makes it easy to create mailboxes. For no additional cost, email aliases can be created and assigned to a mailbox. For instance, sales@domain.com and support@domain.com could be aliases assigned to specific people’s accounts. That makes it easy to maintain a general address that’s assigned to whomever is currently responsible for it.
How to set up Aliases
Before adding an email alias to a user, you must have admin permission to do so.
In the admin center, choose Users > Active users
Select the user > Manage email aliases
You won’t be able to see this option if the user does not have a license assigned.
Select [+ Add an Alias] and enter a new alias for the user.
Click [Save changes].
It may take up to 24 hours for the new alias to populate throughout Office 365.
When the email appears in the user’s Inbox and they reply, the FROM address is their email address. The alias is not the FROM address.
This makes it useful to have dedicated mailboxes rather than aliases. Someone can monitor the mailbox and respond from it. With Office 365, it’s easy to have an internet browser with Outlook opened to that account.
Unfortunately, it’s inconvenient to log in to the mailbox, and if it rarely receives emails, it’s easy to forget. It’d be much better to be notified at your regular email address when an email arrives.
Forwarding Email Messages
Microsoft allows you to easily forward your emails from your Office 365 account to another email account on Office 365 or external accounts such as Gmail or Yahoo. It’s very helpful for monitoring mailbox that are rarely used (e.g. webmaster, info, etc).
Choose Forwarding and enter in the email you would like your Office 365 emails to be sent to. There is an option that allows you to keep a copy of your forwarded messages so you can still log into that account and respond to them.
Total Access Analyzer examines all your database objects to provide extensive documentation, code analysis, object cross-reference, and diagrams with over 430 presentation-quality reports.It detects 430+ types of errors, suggestions, and performance tips, so you can learn and apply Best Practices to fix problems, improve your design, and speed up your Access applications.
Enhancements:
Ribbon Documentation
Data Macro Analysis
SQL Cross-Reference and Validation of Subform References
To connect to a Microsoft SQL Server database, it requires a login which includes a user name and password. Each database server has a login with administrator permissions that allows the creation and deletion of databases.
Often, this login is distributed which creates a security problem. Developers and end-users shouldn’t have administrator permissions to perform their tasks. Their permissions should be managed for each database.
Database User Permissions
Microsoft SQL Server makes it easy to create and manage database permissions. The permissions you grant are called roles and users are able to have multiple roles. Here are some examples of the permissions you are able to assign to users.
Full Database Permissions (Owner)
This allows the database developer full rights to make modifications to the database objects.
Editing Permissions (Writer)
End-users who need to add, delete and change data in user tables.
Read Only Permissions (Reader)
Only allows the end-user to view data in the user tables.
Read our Microsoft SQL Server Users and Permissions paper for more information on how to do this and some pitfalls to avoid. It applies to SQL Server whether it’s installed on premise or in the Azure cloud.
For no additional cost, email aliases can be created and assigned to a mailbox. For instance, sales@domain.com and support@domain.com could be aliases assigned to specific people’s accounts. That makes it easy to maintain a general address that’s assigned to whomever is currently responsible for it.
