[German]Microsoft has confirmed the database error when accessing Access 97 .mdb databases via the Jet Database Engine. The problem is caused by the January 8, 2019 security updates in all supported versions of Windows.
Some background information
I had covered this topic yesterday in the blog post Windows January 2019 Updates breaks access to Access DBs (thanks to a comment from German blog reader Ollert, thanks for that). Also a user on German site administrator.de commented the issue. Heres a short overview:
- In all versions of Windows, the January 8, 2019 security updates fix a vulnerability in the Jet Database Engine shipped with Windows.
- As a result of this patch, open databases in Access 97 MDB format fail with a database error “unknown database format” – if the database contains field names with a length greater than 32 characters..
- The problem occurs when using the Microsoft.Jet.OLEDB.4.0 providers – the older Microsoft.Jet.OLEDB.3.51 variant seems to work.
Some details and workarounds (from uninstalling the update, or replacing a DLL, or changing the data source strings for the provider to open the database) are described within my blog post Windows January 2019 Updates breaks access to Access DBs.
Note: On Friday I tried to bring this issue to Microsoft’s attention. I posted a text within Microsoft Answers forum with a link to my above mentioned blog post and escalated the thread to all Microsoft forum moderators. It seems, that it worked and some developers has been notified.
Microsoft confirms the issue
This morning I stumbled uppon a oost from Susan Bradley at askwoody.com and within my blog (thanks for that). Susan mentioned, that Microsoft has updates the kb articles from the following list. They added the Access 97 database bug to the ‘known issue’ section.
- KB4480116 for Windows 10 Version 1809
- KB4480966 for Windows 10 Version 1803
- KB4480978 for Windows 10 Version 1709
- KB4480973 for Windows 10 Version 1703
- KB4480961 for Windows 10 Version 1609
- KB4480962 for Windows 10 Version 1507
- KB4480963 (Monthly Rollup) for Windows 8.1
- KB4480964 (Security Only) für Windows 8.1
- KB4480970 (Monthly Quality Rollup) for Windows 7 SP1
- KB4480960 (Security-only update) for Windows 7 SP1
Microsoft has added the following passage to the ‘known issue’ section of all KB articles:
Applications that use a Microsoft Jet database with the Microsoft Access 97 file format may fail to open if the database has column names greater than 32 characters. he database will fail to open with the error, “Unrecognized Database Format”.
So pretty much exactly what I had already documented in the blog post mentioned above, based on the hints of blog reader Ollert. Microsoft promises a fix until February 2019 and suggests some workarounds.
Use one of the following options:
Option 1: Modify the database to ensure that all column names are less than or equal to 32 characters.
Option 2: Convert the database to the .accdb file format. To use the .accdb file format, you must change the Connection string after conversion.
The easiest way to convert is to use Microsoft Access 2010 or earlier.
- Use Microsoft Access to open a database that has an older file format.
- You will be asked if you would like to convert. Click Yes and save the database with the .accdb extension.
Option 3: Convert the database to a newer .mdb file format. This doesn’t require a change to the Connection string.
- Use Microsoft Access to open a database that has an older file format.
- You will be asked if you would like to convert. Click Yes and save the database with the .accdb file extension.
- Open the .accdb.
- From the File menu, click Save as and select Access 2002-2003 Database.
These three options are, in my view, extremely cautious and in practice maybe not realistic. Correct me if my following remarks are incorrect – I have been out of the Jet Database Engine, ADO, DAO and OLEDB business for at least 15 years.
Beware of the Microsoft suggestions
The scenario where the errors happens, will with applications that access the databases in Access 97 MDB format via runtime libraries. In most cases, these are also applications that have grown historically and are planned to be replaced at some time in future. Since the bug occurs in January 2019, ad hoc solutions are now required. Let’s take a closer look at the Microsoft proposals.
Option 1: Shorten database field names
Microsoft’s proposal in Option 1 to limit the database field names to a length of 32 characters in order to work around the error is in most cases simply not useable.
- If you change a field definition (field name) within the database, it is usually necessary to modify the code of the application in question. Apart from trivial cases, this approach fails a) because the users do not have access to the code (turn key applications bought from a vendor or developer) and b) because in non-trivial applications and databases the effort to validate this adaptation is simply too huge.
- Who tells me this is the only bug in the Jet Database Engine? The limitation to a field name length of 32 characters ‘smells’ to me that a strict type check or value check for buffer overrun is performed when compiling the DLLs in question. Whether this only leads to problems with ‘database fieldnames that are too long’ or also in other places can only be clarified by code evaluation. This is not possible for outsiders – and Microsoft has not announced anything.
An administrator, who currently has dozens of applications in a production environment, will not be able to do much with such theoretical constructions. Also comments a la ‘anyone who still works with Access 97 is to blame’ doesn’t help the admin, who has to make the (legacy) stuff work and can’t simply replace the application for various reasons.
Option 2/3: Convert the database
Microsoft’s second and third proposal is to open the .mdb database in (a newer version of) Microsoft Access and then let the program converted the database. The database is then available in a new format (e.g. *.accdb) or an .mdb database format for Access 2002-2003.
Sounds good, may work, but there is no guarantee. I remember the ancient times when I wrote beginner’s books about Access. Even my trivial examples had to be adapted when I switched to a new Access version (especially, if VBA macros were included). My last Microsoft Press ‘At a glance’ title ended with Microsoft Access 2002.
With a non-trivial database and application, the solution would have to be validated after conversion before release in a production environment. Even if the software does not provide any errors, this does not guarantee that the data will be correctly stored in the database with the new format.
I’ve asked form comments about the conversion of a data base. One blog reader warned, that this may cause a chain of serious issues – and it can be a huge task to validate the application/converted database. Also German blog reader Detlef Jäger pointed out within this comment and other comments, that he observed issues within ADO. Using ADO to compress or repair a .mdb database in Access 97 format will remove all fields with field names greater 32 characters.
Now every affected person has to see which workarounds and fixes can be implemented. At this point I would like to thank the blog readers for their contributions.
Patchday: Updates for Windows 7/8.1/Server Jan. 8, 2019
Patchday Windows 10-Updates (January 8, 2019)
Update KB971033/KB4480960/KB4480970 bricks Windows 7 Genuine (0xc004f200)Microsoft explains the Windows 7 KMS activation issue
Network issues with updates KB4480970 and KB4480960
Windows January 2019 Updates breaks access to Access DBs
Cookies helps to fund this blog: Cookie settings