Linked table to Dataverse shows blank rows while executing VBA code - Error 3021: No current record

[Trial4life]

Hi everyone.
I'm experience a strange issue while executing this VBA code in a MS Access .ACCDB file, which reads the data from a linked table that is stored in Microsoft Dataverse (in Power Apps):

Public Function getSelNodeData(db As DAO.Database, groupName As String) As Long()
    Dim returnVal(0 To 3) As Long
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim qSQL As String

    qSQL = "SELECT * FROM LINKED_DATAVERSE_TABLE WHERE mem_Req = '" & groupName & "'"
    Set rst = db.OpenRecordset(qSQL, dbOpenSnapshot)

    returnVal(0) = rst.Fields("PK_Req")
    returnVal(1) = rst.Fields("ID_Type")
    returnVal(2) = rst.Fields("ID_Parent")
    returnVal(3) = rst.Fields("dbl_Sort")
    rst.Close

    '(other code)

    getSelNodeData = returnVal
End Function

As you can see, the code is pretty simple: it runs an SQL query which reads the data from the linked table, and stores the field values of the resulting row (the result can always be only a single row) in the function return output.


When I run the code, I get the error:

Run-time error '3021' - No current record.

I've spent a lot of time debugging the code, and I found out that if I convert the table to a local table, it works flawlessly.

I've tried to open the linked table using VBA during the function execution, and I've noticed that the table has all of the rows with blank field values!
https://imgur.com/ncNDSBQ


As soon as I scroll with the mouse, or click on a row, the field values come back (but only for the clicked rows)
https://imgur.com/Y9HADI4

If I manually open the table (without running any code), I can see all of the field values correctly. The issue happens only while executing the code.

This is a really strange behaviour; I suspect that the VBA code is returning Runtime error 3021 because it is seeing a table with these blank rows.
I've tried to debug the code a bit deeper, and I've noticed the following properties of the rst object:

• rst.EOF = True
  
• rst.BOF = True
• rst.RecordCount = 0

So it seems that the code isn't even retrieving any after running the query, probably because the row is still blank as shown in the screens above.

How can I fix this issue? I've also tried to recreate manually the table on Dataverse (which was previously exported from the local table of MS Access), matching the field names and types, pasting manually the data and relinking it in MS Access, but I still get the same error.
I've also tried to compact and repair the database multiple times, and changing the table name, but with no results.


Thanks in advance for the help.

[Trial4life]

It turned out that the problem was caused by the function getSelNodeData being called from another function where the recordset was already opened. The workaround (well, probably a much better coding) was to pass the new field values to new variables by calling the getSelNodeData function, and then add directly the records using an INSERT SQL query instead of opening the recordset at all.

The previous method was working with local tables, but probably it was generating conflicts when the table was linked to Dataverse instead.