3

I am working in ms-access (office 365, v1808), and today my code suddenly broke down. I have not been able to pinpoint the problem specifically nor have I been able to find useful information on the internet.

I'm retrieving a recordset with an ADODB.Recordset object from an external SQL-server table. This is the code that causes Access to crash without any useful information:

Private Sub Command_Click()
    Dim rs As ADODB.Recordset
    Dim strSQL As String
    Dim i As Long

    strSQL = "SELECT TOP 100 *" & _
                " FROM myTable;"

    Call OpenMyRecordset(rs, strSQL)
    Set Me.myListbox.Recordset = rs

    Me.myListbox.Value = Me.myListbox.ItemData(0)

    rs.Close
    Set rs = Nothing

End Sub

Usually the line 'Me.Listbox...' causes the crash (after experimenting with a break and stepping through the code)

I have tried this with no success:

i = Me.myListbox.ItemData(0)
Me.myListbox.Value = i

Sometimes the code also breaks down if I only use:

Debug.print Me.myListbox.Value

The weirdest part is that the errors are not consistent: sometimes it runs a once or twice and then crashes, while other times it crashes immediately.

Additional information gathered so far:

  • The rs that is returned is O.K. (and non-empty)
  • If I only use 'Set me.myListbox.Recordset = rs' and do not inquire/alter the .Value property all is good
  • Using the .Value property without loading a recordset is also no problem
  • Recreating the SQL table does not help
  • Using a different table/database does not help
  • Restarting Access/My computer/Creating a new Access file with only relevant code does not help

EDIT:

The field types for the recordset are int, int, nvarchar(255), nvarchar(255).

The code for OpenMyRecordset is copied and adjusted accordingly from http://accessexperts.net/blog/2011/01/21/easy-adodb-recordsets-and-commands-in-access/

braX
  • 11,506
  • 5
  • 20
  • 33
Ayam
  • 169
  • 1
  • 12
  • Can you provide the field type for the recordset? And preferably, the entire code for `OpenMyRecordset`? Some recordset types (server-side cursors, forward-only recordsets) should not be used when filling a listbox. – Erik A Nov 20 '19 at 13:31
  • I have added the extra information. The entire recordset code is in a module with interlinking functions. Therefore I only posted a link to the code, instead of flooding this page. – Ayam Nov 20 '19 at 13:51
  • 1
    I've removed my answer since it's not the solution. You might want to report this through official channels, since it's new behavior and Access has been plagued with another bug since the last Office update. This might be a new bug in Access. – Erik A Nov 20 '19 at 14:53
  • Have you tried Compact&Repair and [Decompile](https://stackoverflow.com/a/3268188/3820271) ? – Andre Nov 20 '19 at 15:11
  • What does "crashing" mean? Did you receive an error message? What did it say? – Wolfgang Kais Nov 20 '19 at 23:32
  • @Andre I have tried that to no avail – Ayam Nov 21 '19 at 08:10
  • @WolfgangKais No error message safe for a windows prompt saying 'microsoft access has stopped working' – Ayam Nov 21 '19 at 08:10
  • 1
    @Erik referred the bug since the Office update. The posted workaround from Microsoft may be worth trying: update the query so that it updates the results of another query, rather than updating a table directly. For example, if you have a query similar to: UPDATE Table1 SET Table1.Field1 = "x" WHERE ([Table1].[Field2]=1); You can create a new query (Query1) defined as: SELECT * from Table1; And update your original query to: UPDATE Query1 SET Query1.Field1 = "x" WHERE ([Query1].[Field2]=1); By replacing all references to Table1 with Query1 This will avoid the error. – JennyW Nov 21 '19 at 10:59
  • 1
    This is the link to the Microsoft page about the fault, with the above suggested workaround: https://support.office.com/en-us/article/access-error-query-is-corrupt-fad205a5-9fd4-49f1-be83-f21636caedec – JennyW Nov 21 '19 at 11:03

1 Answers1

0

A form control of type listbox gets its data from .RowSource - not .Recordset

If your code, as above, worked in the past, it may have been due to an error or oversight - but I would not expect it to work.

trevor
  • 257
  • 3
  • 9