Access Error: "Query is Corrupt"

By now, most of you would have already known about this latest Access bug from Microsoft as explained on their website and discussed in many forums and blog posts. If so, you may be wondering why am I writing this blog.

That's actually a very good question. Well, the simple answer is I decided to do my part and offer a temporary solution I think is a lot simpler than what Microsoft recommended. This approach avoids the need to modify any saved queries or SQL statements in VBA code (up to an extent - I can't imagine all the possible scenarios out there).

This utility can be deployed to your users, and they can apply the fix themselves. And when the actual fix becomes available from Microsoft, the users can rerun the utility to remove the temporary fix.

To download the "Query is corrupt" bug fix utility, go to this post at UtterAccess. If you find any problems or have any recommendations, please do not hesitate to let me know.

I hope this helps at least those using Access 2010 or 2013, where a fix from Microsoft is still pending. (If you're using Access 2016 or 2019, the fix is already available to you.)

Listbox Selected Property and the Form Load Event

Here's something new I just learned from a recent forum discussion: A Listbox's AfterUpdate event will fire when VBA is used to select an item on the list using the Form's OnLoad event. What? Did you all know that? I wasn't aware of it until now.

Typically, the AfterUpdate event of a control does not fire when the value of that control was updated using code. If you want the code behind the AfterUpdate event to execute when you update a control using VBA, you will have to specifically call it. For example:

Call ListboxName_AfterUpdate

However, as I was saying, there is a specific scenario (I know of only one, so far) where a Listbox's AfterUpdate event will fire, without specifically calling it, when the Listbox is manipulated using VBA code. Here's the deal...

As demonstrated in this forum discussion, the OP used the Selected property to "highlight" an item in a Listbox during the Form's Load event. Interestingly enough, this causes the form to fire the Listbox's AfterUpdate event as well. Typically, using the Selected property does not update the value of a Listbox, which means the AfterUpdate event shouldn't fire. But for some reason, this is not the case during the Form's Load event. It turns out, setting the Selected property of an item in a Listbox during the Form's Load event also updates the Listbox's Value property. However, there is a twist... If we used the "normal" way of updating the Value of the Listbox, then the AfterUpdate event does not fire. To clarify, we can use any of the following methods to change the Value of the Listbox.

Me.ListboxName = "SomeValue" 'if it's bound to a Text column
Me.ListboxName = 1 'if it's bound to an ID column
Me.ListboxName = Me.ListboxName.ItemData(2)

So, the puzzle is, what is going on here? Why does it happen when we use the Selected property? Also, why does it only happen during the Form's Load event? Once the Form is fully opened, using the Seleted property merely highlights the item. It does not update its Value, and the AfterUpdate event does not fire. Equally, using any of the methods shown above to modify the Value of the Listbox at this point also does not fire the AfterUpdate event.

If you have any ideas or logical explanation for this, I would really like to hear it. If I find out the answer somewhere else, I will definitely let you know.

Thank you for reading!

A Recordset Bug

While trying to help a MSDN Forum user recently, I came across a potential bug when using a Recordset to loop through the records in a query. If you are interested in reading the discussion thread, you can find it using this link: https://social.msdn.microsoft.com/Forums/office/en-US/c9b98d99-083a-49fc-859a-43b9dcbd263f/combine-multiple-rows-from-a-query-into-1-row-string?forum=accessdev

The poster was trying to build a Text file from the table records for an external application. His requirement was to combine all the field values and all the records into one long text data. Naturally, when someone asks to combine all table records, I typically recommend this SimpleCSV() function. Unfortunately, the function did not work for him as it produced weird characters in the output string. See image below...

I have never run into this issue before, so I started doing some research. It turns out, when you use a Recordset with a calculated column where multiple fields are concatenated to combine the data, the calculated column only looks normal if the resulting value is less than 255 characters.

To demonstrate this issue, I created a demo file, which you can download from my website.

In the demo, I created a table with two Text fields, both containing exactly 255 characters. When we use a Recordset to pull just Field1, the result looks okay. But when we pull both Field1 and Field2 into one column in a Recordset, then we run into this issue. Interestingly enough, I also found out that using DLookup() does not produce a problem.

I brought this issue to Microsoft's attention, and it was confirmed by the Access Team. They also promised to work on a fix for future release. In the meantime, if you must combine multiple fields using a Recordset, I recommend pulling all the fields individually when you create the Recordset and then just loop through the fields and concatenate them from within your procedure.

I hope you find this article helpful. As always, any comment is welcome.

Thank you very much for reading.