Tag: MS Office

I found another way to force MS Access to crash to desktop, and it’s shockingly simple.

Sub SQLTest()
    Dim Query As ADODB.Command
    Set Query = New ADODB.Command
    With Query
        .ActiveConnection = CurrentProject.Connection
        .CommandText = "SELECT ?"
        .Parameters.Append .CreateParameter(, adChar, , 1, "a")
        .Execute
    End With
End Sub

Continue reading MS Access Crashes on “SELECT ?” Query

I found a specific situation where MS Access always crashes to desktop.  Here are the ingredients:

1. A bound Form with a Microsoft TreeView Control, version 6.0.
2. Form’s “Allow Additions” property set to “No”
3. TreeView populated using the Form_Current() event.
4. Prior to populating the TreeView, calling .Nodes.Clear on the TreeView object.
5. To set up the crash, filter the form to an empty recordset.
6. Click on the Home ribbon and the Toggle Filter button.

Download Testcase File: treeview-testcase.accdb

Workaround

In the Form_Current() event, add a DoEvents command immediately before Nodes.Clear.

Set MyTree = Me.TreeView0.Object
DoEvents
MyTree.Nodes.Clear

I solved a mysterious bottleneck last night while working with large string values in MS Access. My VBA code was reading a file to a string variable, checking several values near the beginning of the string, and then manipulating and saving the string to the database.

Oddly, one of the slowest parts of my code was the several InStr calls that were only checking the first few hundred bytes of the string. I could alleviate part of the problem by copying the beginning of my file to a shorter string value, but in so doing I also noticed unexpected results from the InStr return value.

Continue reading InStr Performance for VBA