Jan 28

Total Access Emailer and Office 365 SMTP with TLS

Total Access Emailer is the most popular email automation system for Microsoft Access. Total Access Emailer uses your SMTP server to send emails. A popular choice is the SMTP server provided by Microsoft Office 365’s Exchange Server.

Total Access Emailer and the Office 365 SMTP Server

Total Access EmailerAssuming the account is properly configured to relay email messages (Mailbox Delegation), Total Access Emailer has supported Office 365 since it was introduced.

Over the years, Microsoft has increased security on their site and adjusted configurations for their SMTP server. You can connect via SMTP protocol using an approved IP address or Transport Layer Security (TLS) protocol for a specific email address.

Instructions for configuring Office 365’s SMTP server are provided here:
Office 365 SMTP Relay Configuration to Send Emails via the Office365 Exchange Server

Problem with TLS Connections

In the last week, we confirmed that Office 365 and Windows made changes that cause Total Access Emailer to be unable to send emails with its TLS connection protocol with an error like this:

SMTP protocol error: TLS 1.0 and 1.1 are not supported

The message implies the need for TLS 1.2, but recent versions of Total Access Emailer already support TLS 1.2.

What is not supported is TLS 1.3 that recent changes with the Office 365’s SMTP server and Windows seem to require. Here’s a Microsoft article about TLS 1.3: Taking Transport Layer Security (TLS) to the next level with TLS 1.3.

NOTE: TLS still works with many other SMTP servers including Gmail’s smtp.gmail.com (details for Using Total Access Emailer and Gmail).

Workaround: Use SMTP Protocol

If you are having trouble using TLS protocol, the SMTP protocol still works for Office 365. You need to authenticate your IP Address where you’re sending the emails. Remember to use the Temail.txt to store the FROM email address to use for validation as instructed in the referenced page above.

We realize that is not possible for all the environments of Total Access Emailer users.

New Versions of Total Access Emailer

This has accelerated our ongoing development for a new version of Total Access Emailer to address the new protocols. Our development team has already created a solution that is working with the new protocols and hope to release it, as soon as it completes thorough testing. We expect to release these Access 32 and 64-bit versions:

  • New Product:
    • Total Access Emailer 2021 for Access 2021, 2019 and the current Office 365 version
  • Updates:
    • Total Access Emailer 2016
    • Total Access Emailer 2013
    • Total Access Emailer 2010

New versions are now available! Visit the Total Access Emailer page for more information on the new features or ordering information.

Receiving the New Version

Customers on Premium Support Contracts will receive a free update for their version when it is available. Existing customers will be able to purchase an upgrade at a discounted price.

Dec 15

Microsoft Access Could Not Lock File (Error 3050): Cannot Open Database Error

Microsoft confirmed the Microsoft Office security update released on Tuesday (December 14, 2021) causes Microsoft Access databases on shared drives to be locked after users exit the database. This prevents other users from opening the database and multiuser database sharing. Customers reported errors like:

Could not lock file (Error 3050)
Could not use 'Admin' (related to workgroup security)
This file is in use. Enter a new name or close the file that's open in another program.

Several issues appear to be happening:

  1. The first person opening the database is successful but an exclusive lock is placed on the database preventing others from opening it.
  2. The Access lock file (*.laccdb or *.ldb for ACCDB and MDB databases respectively) is not being deleted after the last person exits the database. This makes the database seem like it’s still being used.
  3. This can also impact workgroup security files (*.mdw) that also have their *.ldb lock file.

From Microsoft:

This is due to the December 14, 2021 (Patch Tuesday) update to Office. The problem was introduced by a security fix, so it impacts all active versions of Access.

We are working on a fix, and will deliver it as quickly as possible.

The update has only updated a small percentage of users, and we are pausing automatic updates.

There will be a page added to the Fixes or workarounds for recent issues in Access (microsoft.com), which will be the place to go for updates.

These perpetual license updates introduced the problem :

  • KB 5002104 for Office 2013
  • KB 5002099 for Office 2016
  • Office 2019 Version 1808, build 10381.20020
  • Office LTSC 2021 Version 2108, build 14332.20204

For Microsoft 365 subscribers:

  • Current Channel Version 2111, build 14701.20248
  • Monthly Enterprise Channel Version 2110, build 14527.20340
  • Monthly Enterprise Channel Version 2109, build 14430.20380
  • Semi-Annual Enterprise Channel (Preview) Version 2108, build 14326.20692
  • Semi-Annual Enterprise Channel Version 2102, build 13801.21086
  • Semi-Annual Enterprise Channel Version 2008, build 13127.21842

If you have one of these builds, the only solution is to move back to an earlier build in the channel.

Microsoft published this new page with the latest information on this issue Error in Access when opening a database on a network file share

Rolling Back to an Earlier Version

These are Microsoft support pages for reverting to a prior update depending on your Office license:

FMS Suggestion

This problem is even in the Semi-Annual Channel which is never supposed to include such poorly tested changes.

To avoid such problems, disable automatic updates. While this is a drastic step, Microsoft repeated failed to release stable updates thereby causing more problems than they fix.

Disable Microsoft Office Updates
Disable Updates for Microsoft Office

Additional Resources

We discuss this change in our previous blog: Error: “The operating system is not presently configured to run this application” with Access Database Engine (DAO.DBEngine)

Here’s our page on Changing the Office Channel for Microsoft 365 Apps

Jun 09

Decimal Field Problems in Microsoft Access Build 12827.20010

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

For more information on the issue, visit Microsoft’s support page Access VBA/DAO code may crash or report incorrect data for Decimal Columns.


June 18, 2020 Update

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.

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.

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

  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.

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.

May 15

Windows 8.1 PCs Connect to the Network but not the Internet

windows81This past Tuesday night, Microsoft released a security patch. On Wednesday morning, we and some of our customers encountered problems with connectivity that were quite unusual and different from past security updates. The update required servers to reboot which triggered some issues, but manageable. What was particularly troubling were multiple reports of problems with PCs using Windows 8.1.

The PCs could still connect to the network and see all the network resources, but they could not get on the Internet. Using different logins including Administrator logins didn’t make a difference. Other PCs using Windows 7 or older O/S were able to connect successfully even with the same cable as the Windows 8.1 PCs that couldn’t connect.

What Could be Wrong?

We struggled trying to see if there were issues with:

  • Hardware on the PC, network card, cable or switch
  • Software issues with the automatic Windows patches, DHCP network settings, IP addresses, Firewall, antivirus, etc.
  • Login rights and permissions

We couldn’t determine the problem or find a solution. Since the user could log into the network, permissions seemed sufficient.

From the Windows command prompt (Run cmd), we used the ipconfig command with the release and renew command lines to see if that would reset the IP address, but that didn’t make a difference either:

ipconfig /release
ipconfig /renew

Solution: Flush DNS

Finally, I posted a message to the Microsoft Access MVP group, which I’m honored to be a part of. Long time colleague, Tom Wickerath suggested flushing the DNS (Domain Name System) cache by using:

C:\> ipconfig /flushdns

We weren’t familiar with that command line option as it doesn’t appear when you enter

ipconfig ?

Well, it worked! We may never know whether the problem was caused by the Windows security patch or if it was just a coincidence. Regardless, the flushdns command reset the PCs that were affected by this problem.

Thanks for sharing your insight Tom!

Jul 10

Microsoft Access Class Not Registered Run-time Error -2147221164 (80040154) Class Not Registered

This error occurs in a Microsoft Access database that seems to work fine on every other machine but one. The MS Access database actually loads and runs, so the code is compiled and functional. Then it dies on some very common code such as CurrentProject.Connection for ADO to open a table or query recordset:

Microsoft Access "Class Not Registered" Run-time Error -2147221164 (80040154)

Confusion

The “Class Not Registered” is very confusing. It implies code that won’t compile or broken library references but that’s not the true cause. Is the Access database corrupt? No.

We’ve received reports for this error for years and were never able to reproduce it. We finally did and figured out why it occurs and how to fix it. Read our new paper Microsoft Access “Class Not Registered” Run-time Error ‘-2147221164 (80040154) to learn more.

Jul 01

Microsoft Access Inconsistent Compile Error for a Field Reference in a Form

Our Professional Solutions Group was recently asked to diagnose a Microsoft Access database experiencing recurring compile errors with code behind a form that looks like this:

If IsNull(Me.Comments) Then

where Comments is not a control on the form, but a field in the form’s RecordSource.

In general, this compiles and runs fine, but on seemingly random occasions while the program is running, it generates a compile error saying that that field was not found. But the field always existed on the form’s RecordSource, so why was this happening?

Solutions

There are a few ways to avoid this problem:

  • Change all the Me. to Me! which is the proper way to reference a field in VBA code, if there is no control bound to this field.
  • Create an invisible  text box that assigns its ControlSource to that field, give the text box a different name (e.g. txtComments), and reference the text box in code.
  • Deploy the database so its compiled state cannot be changed (ACCDE or MDE)

We prefer the use of the invisible text box so that we can reference the control name via the “Me.” syntax rather than “Me!”. The “Me.” syntax is verified when the code is compiled so that a typo with the control name is caught. This is preferable to a runtime error that gets triggered when the user encounters that line of code.

But Why?

Though we knew how to fix this, we were curious to understand why the compilation wasn’t consistent across users. It also didn’t fail when a specific event occurred. It seemed almost random when the compile error arose. And the form triggering the error seemed perfectly fine with a reference to a field that exists in its RecordSource.

The Real Cause for the Compile Error

Through our own research and help from our Microsoft Access MVP colleagues, we discovered that the compile error was tied to programmatically changing the RecordSource of a form. The change is not necessarily on the form where the compile error is triggered.

Microsoft Access seems to reset its internal list of field references some time after the RecordSource is modified, which triggers the compile error. This explains why some users experienced it and others did not since it depended on whether the user opened a form that changed its RecordSource. It also explained why the error didn’t occur immediately after a RecordSource was modified.

Special thanks to Dirk Goldgar for pointing this out. Hope you never encounter this!

Additional Resources for Database Compile and Field Reference Issues

For additional tips on Microsoft Access application development, visit our:
Microsoft Access Developer and VBA Programming Help Center

Sep 07

Microsoft Access Error 3045 Could Not Use Database; File Already in Use

Error Message

When you try to open a database, you may encounter this error message: Could not use [Database Name]; file already in use.

Explanation

This error occurs if another user (or instance of Access) has opened up the database exclusively and you try to open it. Maybe the other user opened the database in exclusive mode or made some modifications such as editing modules, which put the database in exclusive mode.

However, that may not be the case. This error can occur even if the other user has the database opened without opening it exclusively. What’s going on? Why would the second instance think it’s being opened exclusively?

What if the Other User isn’t Opening it Exclusively?

It turns out that another cause of this error is not immediately obvious and is related to workgroup security files. This applies to MDB files (not ADP or ACCDB formats). If two instances of Microsoft Access (DAO database engine) are using different system MDW files, a conflict may occur where the database appears to be exclusively locked by the other instance. The MDW file used by your instance of Access can be checked in VBA by examining the value of DBEngine.SystemDB.

Another copy of Access or DAO may have that database opened using another MDW file causing this conflict. This can come from another instance of MS Access, a Microsoft Access add-in or library reference, a VBA Add-in, or other program such as VB6, .NET, etc. that’s connecting to that database.

To avoid this, if you’re using workgroup security, make sure every instance is pointing to the same shared MDW file. This is often defined in the shortcut used to launch Microsoft Access, or if you are opening a different database programmatically within your VBA code.

Additional Resources

Visit our Microsoft Access Error Number and Error Description Reference site for a complete list of Microsoft Access error numbers, many with links to resources explaining and resolving them.

total-access-admin[1]If you’re interested in seeing who’s going in and out of your database in real-time, check out our Total Access Admin program. It’ll let you monitor multiple databases across your network to see when users enter and exit your database and flag instances where connections are dropped suspiciously.

Nov 28

Microsoft Access 2010 Error Number and Description Resource

As a resource for the Microsoft Access community, we’ve updated our comprehensive list of Microsoft Access Error Codes and Descriptions for Microsoft Access 2010.

There are PDF reports of the error codes and descriptions, and a database containing a with each error code and its different descriptions access MSAccess versions 2010, 2007, 2003, 2002, and 2000.

Here’s the detailed HTML page that lists every Microsoft Access 2010 error code with its description and for some of them, hyperlinks to resources that address the error and/or provide more information on the topic. Since this page is so large, please be patient while it loads.

This resources is part of our Microsoft Access Developer Help Center where you’ll find lots of other information to improve your skills. Hope this helps!