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 20007 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:
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 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].
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.
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.
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.
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.
With Congress and President Trump at a budget impasse, funding for significant portions of the US Federal government ended midnight Friday, December 18, 2018. Historically, government employees are not paid on time but are retroactively paid after the new budget is implemented. The same is not true for government contractors.
Government contractors compete against each other to provide a wide range of services such as computer programming, construction, security guards, cafeterias, janitorial services, technical experts, maintenance workers, and much more. When the government shuts down, contractor staff are not needed or can’t do their work. But they are real companies and people who suffer. Long-term, it’s bad for US taxpayers.
Our Contract with Amtrak is Suspended
We have a government contract with Amtrak which was suspended by this letter:
This contract provides help for Microsoft Access database programming and is relatively small. We do not have dedicated staff for the contract, so we are not really affected as our people will work on other projects.
Unfortunately, we have colleagues who are government contractors or in businesses more dependent on the government who are significantly impacted by the shutdown. This is especially true for companies who provide staff to work at government facilities that are now closed. Most are hesitant to speak publicly about their experiences in fear their government clients would be offended, so we’d like to share our experiences and theirs.
Very Tough Situation and Decisions for Government Contractors and Their Employees
Since government contractors are unlikely to be repaid when the budget is finally resolved, government contractors with dedicated staff for those contracts need to decide whether to pay their employees for time they never expected to not charge to the government. The impact is somewhat offset by the Christmas and New Year’s holidays, and maybe they can force employees to take vacation days. But as the shutdown lasts longer, contractors will have to pay their employees from their own funds. Not paying them runs the risk that the employees leave and won’t be available once the budget is resolved. One cannot expect employees to remain loyal if they aren’t paid. Meanwhile, employees are dusting off their resumes and seeking alternative employers.
Small businesses are especially vulnerable since many contracts have small profit margins, so without significant cash and lines of credit, a cash flow problem can quickly bankrupt a company. Even generous owners who try to do the “right thing”, may not be able to if their cash is depleted. That would lead to fewer government contractors in the future and higher costs to taxpayers.
A friend of mind who owns a firm completely focused on servicing the federal government shared:
“As contractors, our employees still get paid and yet we are unable to bill. Cash is king and small companies like mine live and die by cash. This is the worst.”
On December 26th, we sent a Twitter message with the letter we received from Amtrak and the sympathy we feel for government contractors and their employees.
“Business owners who have [dedicated] staff are making the decision: Do we pay people or not, even though we won’t get paid by the government? Either you force people to take vacation, or you pay them, to be a good company ― but depending how long it drags on, one may not have a choice.”
Self-Inflicted Wound that Should be Resolved Promptly
Eventually the budget will be resolved. We hope the parties come to their senses and do it sooner rather than later because innocent people who’ve dedicated their firms and lives to providing a professional service to US taxpayers are at risk and feeling real pain. That’s terrible for them now and our country long-term.
Having Microsoft Azure host SQL Server databases on their servers is very cost-effective and efficient. Within minutes, one can have a SQL Server database hosted in the cloud and available to applications on the cloud or on premise.
As with all cloud resources, and especially databases, security is a huge concern. Fortunately, SQL Azure includes features to restrict what can connect to your database server but you need to know how to use them and realize that the default settings do not protect you best.
Setting Firewalls and Virtual Networks
This is an important feature for cloud solutions so that only permitted sources are allowed to get data from your server and databases. You can set the IP Addresses you allow at the database level and server level. The database settings take precedence over the server settings.
Cannot Open Server Error
If you try to connect to the database from an unauthorized IP address, it triggers an error like this:
Cannot open server ‘ServerName’ requested by the login. Client with IP address ‘220.127.116.11’. is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.
By Default, All Azure Resources can Connect to Your Database
By default, all Azure resources can connect to your server and databases hosted on Azure:
Allowing All Azure Services to Connect to Your Server is a Huge Security Hole!
If you “Allow access to Azure Services” set to On, you create a huge security hole for your server and every database in it. Not only can all your resources connect to your databases, Any Azure resource from any organization can connect to your database.
This setting is NOT restricted to the Azure resources in your subscription. It’d be nice to restrict permissions to the current subscription or list of subscriptions but that’s not possible. It’s everything on all of Microsoft Azure or you need to specify each IP address.
Turn Off Permissions to All Azure Services
Set the permissions to OFF to disallow all Azure services to connect to your SQL server:
Explicitly Specify the IP Addresses Allowed
To avoid the ability of rogue Azure resources from breaching your database security, you need to manually specify the IP Address of every resource that may connect to your server and databases. This can be a real pain.
We are excited to announce the release of Total Access Analyzer for Microsoft Access 2019! Total Access Analyzer examines all your database objects to provide extensive documentation, code analysis, object cross-reference, and diagrams with over 390 presentation-quality reports.It detects 300+ 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.>New Features
Access Analyzer 2019 is an upgrade from the 2016 version and includes these enhancements:
Supports Microsoft Access 2019, 32-bit and 64-bit versions.
Document All Database types supported by Microsoft Access 2019.
Improved Blueprint Documentation.
Additional Cross-Reference and Validation including Subform References.
Improved Memory Management.
Data Macro Documentation.
Document Workgroup Security in ACCDBs.
Better Support of Documentation for Multiple Databases.
Module Bracket Reports.
Improved User Interface Shows more Progress Details.
Recently, Microsoft Access users are confronted with this error when they open their database on Windows 10 machines:
“Microsoft Access has detected that this database is in an inconsistent state, and will attempt to recover the database. During this process, a backup copy of the database will be made and all recovered objects will be placed in a new database. Access will then open the new database. The names of objects that were not successfully recovered will be logged in the ‘Recovery Errors’ table.”
This seems to be related to Microsoft security updates that were released over the past few months.Our investigations lead us to these two links: