Nov 15

Microsoft Access Query is Corrupt (Error 3340)

Critical Alert

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.

  • 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 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 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 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].

Future Microsoft Fixes (Estimated Availability Date)

  • Access 2010 C2R: Fixed Build TBD – December 10, 2019
    Open an Office program, select [File], click [Account], click [Update Options] and select [Update Now].
  • 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 2019 Volume License: Fixed Build TBD – December 10, 2019
    Open an Office program, select [File], click [Account], click [Update Options] and select [Update Now].

Solutions

  1. 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.
  2. 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.
  3. Rename each table and create a query selecting it with the original table name. Need to adjust table references.
  4. Deploy your Access application with Access 2007 or earlier. You can download the free Access 2007 runtime from our site.

For detailed information and step-by-step instructions, visit Microsoft Access Error 3340: Query is Corrupt.

Aug 22

Total Access Analyzer Update for 2019, 2016, 2013, 2010 and 2007

real-time-monitortotal-access-analyzeranalyzer-icon

analyzer

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
  • New Errors and Suggestions
  • Report Enhancements
  • View Explorer Enhancements
  • Quick Documentation Enhancements
  • About Box has Check for Updates
  • Improved User Interface
  • Revised User Manual and Help File
  • and more

For more information, visit:

Existing Total Access Analyzer owners are able to upgrade at a discounted price.

Dec 30

Government Shutdown’s Impact on Contractors and Employees

Contractors Face Considerable Financial Risk

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

Media Coverage

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.

That led to our inclusion in a Washington DC News article on WUSA Channel 9 (CBS) by John Henry that evening. A video of the coverage with our Amtrak letter and insight are in this article: #ShutdownStories: Government contractors tackle life without pay during partial shutdown

On December 27th, I was interviewed in the Huffington Post article by Sarah Ruiz-Grossman about the challenges facing contractors entitled
Shutdown Leaves Government Contractors Without Work And Likely No Back Pay.

“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.”

On December 27th, Bridget Johnson included us in her Homeland Security Today article:
Federal Employees, Contractors Tweet Worries with #ShutdownStories

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.

Dec 20

Total Access Analyzer 2019 is Shipping

real-time-monitortotal-access-analyzeranalyzer-icon

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:

  • analyzerSupports 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.
  • New Manual and Context Sensitive Help.
  • and more

Existing Total Access Analyzer owners are able to upgrade at a discounted price.


New updates for Total Access Analyzer 2016, 2013, 2010 and 2007

Enhancements

  • Additional Cross-Reference and validation of tables, queries, fields and controls across your database objects, including references in subforms.
  • Improved form and report blueprint documentation to capture large and complicated designs and layouts.
  • Improved memory management for documenting large Access databases to minimize the chance of running out of memory.
  • Data macro documentation and cross references to related tables.
  • Documentation of workgroup security settings for ACCDBs if they wer converted from MDBs with workgroup security.
  • Improved user interface to show progress of the documentation.
  • Revised user manual and help file.
  • and more…

For more information, visit:

Download the Free Trial to experience it for yourself.

Oct 09

Microsoft Access has detected that this database is in an inconsistent state

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

‘inconsistent state’, Error 3343 “unrecognized database format

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:

Microsoft Explanation

Microsoft discusses this problem here: Microsoft Access reports that databases are in an ‘inconsistent state’

Workaround

A potential workaround suggests to ensure that SMBv2 or SMBv3 is enabled on both client and server, as described in this Knowledge Base article:
How to detect, enable and disable SMBv1, SMBv2, and SMBv3 in Windows and Windows Server

We hope you find this helpful. Let us know your experience with this.

Aug 27

Total Access Emailer Update for Microsoft Access 2016

Total Access Emailer is the most popular email automation system for Microsoft Access. A new update was released for the Microsoft Access 2016 version.

Enhancements

  • Partially Restored Original SMTP ValidationTechnique.
  • Validation of FROM Address during Email Blasts.
  • Adjusted the saving of datasheet column settings on databases before Access 2007.
  • Improved Offline activation and uninstall.
  • Cosmetic changes to the program and sample databases.

For more information, visit:

Existing Total Access Emailer 2016 owners were notified to download the update.

Jun 27

Total Access Emailer Update for Microsoft Access 2016

Total Access Emailer is the most popular email automation system for Microsoft Access. Update 16.01.0014 was released for the Microsoft Access 2016 version.

Enhancements

  • SMTP validation uses your email address and eliminates the need to use a TEmail.txt file.
  • Changes you make in the datasheets settings such as column settings, sort order, and/or filters are saved where appropriate for the next time the form is opened.
  • Preview Text allows you to test your email blasts by sending the emails to your own email address.
  • When creating an email blast, the FROM email address is validated for an acceptable email format.
  • When the name of an attachment file includes invalid characters (e.g. :*?”<>|), a message indicates that problem rather than saying the file can’t be found.
  • Procedure TotalAccessEmailer_SendOne has two parameters (strMessageHTML and strHTMLFile) that are optional but were previously required.
  • Sample database updated with TLS option and larger fonts.
  • Cosmetic changes to increase font size, widen combo boxes, font consistency, and support for higher resolution monitors.
  • Manual and help file updated.

The latest update for Total Access Emailer 2016, Version 16.0 is:

Existing Total Access Emailer 2016 owners were notified to download the update.

May 18

Converting Microsoft Azure SQL Server Databases to SQL Elastic Pools to Share Server Resources

Microsoft SQL Server Databases on the Azure Cloud

Microsoft Azure lets you economically and quickly host enterprise quality SQL Server databases in the cloud. The cost of each database is relatively modest.

Managing Resources and Costs for Individual Databases

However, as you add more databases, larger databases, and/or databases that require more resources, costs increase. Providing more resources to a database is helpful when it demands it, but when users aren’t on it or during non-business hours, it may be wasted capacity. Even during business hours, one can have some databases being utilized more than others at unpredictable levels.

Pooled Resources Across Multiple Databases

Fortunately, Azure offers an Elastic Pool option to share resources across multiple databases. If the demand on your databases is inconsistent (spiky), you can provide a high level of capacity that’s available to the most demanding database while allowing other databases to share those abundant resources when needed.

  • You no longer need to set the limits of each database,
  • You are not charged a per database monthly fee which is great for supporting lightly used databases.

Migrating Existing SQL Server Databases to Elastic Pool

Microsoft provides information on SQL Elastic Pools but does not explain how to convert existing databases to an Elastic Pool.

FMS President Luke Chung wrote a new paper with step-by-step instructions on how to convert existing SQL Server databases on Azure to an Elastic Pool without the need to change the database connection strings:

Converting Microsoft Azure SQL Server Databases to SQL Elastic Pools to Share Server Resources

Here’s more information on Designing and Deploying Microsoft Azure Solutions

May 10

Remote Desktop Authentication Error Has Occurred. The function requested is not supported. CredSSP Workaround

Remote Desktop Connections Fail

Starting May 9, we received many reports of Remote Desktop connections failing globally. Users received error messages like this when they tried to remote to machines they connected to successfully for a long time:

Remote Desktop Connection Error

An authentication error has occurred.
The function requested is not supported

Remote computer: <computer name>
This could be due to CredSSP encryption oracle remediation.
For more information, see https:/go.microsoft.com/fwlink/?linkid=866660

The link goes to this page, https://support.microsoft.com/en-us/help/4093492/credssp-updates-for-cve-2018-0886-march-13-2018, and explains the Credential Security Support Provider protocol (CredSSP). It offers extensive information on a series of updates since March 2018. It recommends some steps but isn’t very clear what those changes are nor whether those changes are needed to be made by network administrators globally via group policies, or group policies on every PC and VM.

Caused by a Microsoft Security Patch

The Microsoft Security patch issued on Tuesday, May 8th triggered the problem by setting and requiring remote connections at the highest level (CredSSP Updates for CVE-2018-0886)::

Security update deployment information: May 08, 2018

It changed the default setting from Vulnerable to Mitigated which means that any PC using CredSSP is not be able to use insecure versions. If your PC received the May update but the target PC hasn’t implemented the CredSSP update, the PC receives the error message when it tries to connect to that PC.

The automatic Windows patch to raise the security level is not implemented if the PC doesn’t allow automatic updates. This mismatch between the implementation of a security requirement (which is not optional) without the corresponding automatic update may be the source of this problem.

However, there are many situations such as development, testing, build, staging, and deployment environments which require a stable environment that would be destroyed by automatic Windows updates.

We continue to research this.

Symptoms

The symptoms are rather strange because we found that some machines successfully connected while others didn’t.

For instance, we had a Windows 7 machine that hosted Remote Desktop. A Windows 7 PC had no problem connecting to it, but the same user connecting from a Windows 10 machine failed when that was never an issue before and the host machine allowed remote connection for years.

There are also reports of problems with Windows 10 machines connecting to Windows 10 machines, and people locked out of their Azure VMs.

Workaround Solution

One could rollback the security update, but rather than risking other security problems, there’s a quick fix.

Simply adjust the Remote Desktop settings on the host machine to a lower security level. From File Explorer, choose Computer, right-click and select Properties, then click Change Settings, and go to the Remote tab.

From Windows 10, uncheck the option to “Allow connections only from computers running Remote Desktop with Network Level Authentication (recommended)”:

From Windows 7, it’s setting the option to the Less Secure option rather than More Secure:

Once these are set, users can remote to the machine again.

Microsoft Comment

Based on this blog post, a Microsoft colleague told us this:

“I double checked the Windows bug database and they are aware of the problem. No ETA on a fix yet unfortunately. Your workaround is what’s suggested to temporarily get around the error, although it is not suggested as a long-term fix.”

Alternative Solutions

This section was added after our initial workaround and is based on the experience of many users struggling with this problem.

The problem is often caused because the local machine is patched with the Windows Update and the machine it’s connecting to is not patched for the CredSSP issue. If both systems were patched then this error would not occur.

There are two options:

Update the Target Machine

Update the target machine with the patch for the CredSSP issue (preferable).

Update the Local Machine

In many cases, you don’t have the option to modify anything on the target machine. You may even be prevented from modifying your own machine, but assuming you have administrator rights, you can change the Group Policy on your local machine to use the Vulnerable setting.

Big picture, it’s ridiculous to lower one’s security settings to connect to a machine that wasn’t updated. It would be much better if it prompted or automatically connected to lower level machines without turning off the higher security level for everything else. All it takes is one target machine that you can’t modify to force this change on your machine. But at least you can get your work done.

  1. Enter run “gpedit.msc” to edit group policy, or from Windows start, enter “Group Policy” and select “Edit group Policy”:
    1. Windows 10
    2. Windows 7
  2. From the treeview, choose Computer Configuration -> Administrative Templates -> System -> Credentials Delegation
  3. Select “Encryption Oracle Remediation” from the right pane (if it’s not there, it probably means your machine wasn’t patched):
  4. Enable and set the Protection Level to Vulnerable:

Hope this helps.


Additional Problem: Cannot Connect via VPN

We’ve discovered problems with VPN connection if the PC has Remote set to the higher security level.

The network connection fails with error: Cannot load the Remote Access Connection Manager service. Error 711:

Lower Your Remote Desktop Security to have the Security to Make the VPN Connection

Apparently, the Remote Desktop setting on the client side impacts its ability to connect via VPN to the host side.

By lowering the setting to less secure for others to connect to the PC, the PC can now successfully connect to the VPN. What a mess.

Additional Discussions

I’ve also been involved in other online discussions:

Summary

It’s late August, and it’s shocking that this problem remains after so many months. I am extremely frustrated by the Windows update policies and Microsoft’s inadequate testing before these security patches are deployed. This is very disruptive and dangerous to many organizations trying to fulfill their missions expecting their PCs to be reliable.

Microsoft security “purists” claim the current approach is necessary to address the serious threats facing users. I guess it wouldn’t be an issue if the updates worked without disruption. However, the downside of this medicine may exceed the illnesses they are trying to prevent.

Hope you are able to resolve this and move on.


Additional Remote Desktop Connection Resources

Apr 23

Microsoft Azure SQL Server Usage and DTU Limit Resource Graphs are Confusing

microsoft-azure-h60 SQLServerText

Microsoft Azure lets you easily create and deploy enterprise quality SQL Server on the cloud and scale it to suit your application’s needs. From the SQL Server database’s Azure dashboard, you can see the Database Transaction Unit (DTU) usage against the specified DTU limit for the database.

One Hour Usage Graph

This is what we saw for usage over one hour. The cyan line across the top is the DTU limit. The dark blue line is the DTU used. The limit is what you pay, so it’s important to scale it to what the application needs.

monitor-hour

One Hour Azure SQL Server DTU use versus limit

While everything seemed fine at the weekly level, looking at the hourly graph gave us a shock. It looks like the database is maxed out for most of the hour. It seems conclusive that we need to increase our DTU level.

65 Minute Graph

But when we set the range to 65 minutes and saw this:

monitor-65-minute

65-minute Azure SQL Server DTU use versus limit

These are completely different displays of the same period of time. The 65 minute graph never hits the maximum DTU. What’s going on?

Visit our page Monitoring SQL Server Usage on Microsoft Azure and Setting DTU Limits for an explanation.