vba access error 3349 reading record in linked table

Question

I have a linked excel table in my access db. I need to read out individual records from this table. Some of the cells unfortunately contain errors of #NA type in the cells. When my code comes to such a cell it throws a Run-time error 3349.

How can I either capture or simply read out the value in the cell?

The run time error appears on the If rs.Fields("SFDR_SUSTAINABLE") = 1 Then line

Here is all of my code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT SFDR_SUSTAINABLE, E_SUSTAINABLE, SDG_E_CONTRIBUTION, SDG_S_CONTRIBUTION FROM ShareClasses WHERE ShareClassISIN='" & ISIN & "'")

Dim strMsg As String
If rs.RecordCount = 0 Then
    Exit Function
Else
    If rs.Fields("SFDR_SUSTAINABLE") = 1 Then strMsg = vbNewLine & "SFDR Sustainable" 'SFDR Sustainable
    If rs.Fields("E_SUSTAINABLE") = 1 Then strMsg = strMsg & vbNewLine & "Envir. Sust." 'Environmentally Sustainable
    If rs.Fields("SDG_E_CONTRIBUTION") = 1 Then strMsg = strMsg & vbNewLine & "Sust. Cont. (Envir.)" 'Sustainable Contribution (Environmental)
    If rs.Fields("SDG_S_CONTRIBUTION") = 1 Then strMsg = strMsg & vbNewLine & "Sust. Cont. (Social)" 'Sustainable Contribution (Social)
End If

strMsg = Mid(strMsg, 3)
ESG_Rating = strMsg

End Function```

Gustav · Accepted Answer · 2022-08-15 14:12:48Z

1

The simple method is to ignore the error:

If rs.RecordCount = 0 Then
    Exit Function
Else
    ' Ignore errors and continue.
    On Error Resume Next

    If rs.Fields("SFDR_SUSTAINABLE") = 1 Then
        If Err.Number = 0 Then     
            strMsg = vbNewLine & "SFDR Sustainable" 'SFDR Sustainable
        End If    
    End If
    If rs.Fields("E_SUSTAINABLE") = 1 Then 
        If Err.Number = 0 Then     
            strMsg = strMsg & vbNewLine & "Envir. Sust." 'Environmentally Sustainable
        End If    
    End If
    If rs.Fields("SDG_E_CONTRIBUTION") = 1 Then 
        If Err.Number = 0 Then     
            strMsg = strMsg & vbNewLine & "Sust. Cont. (Envir.)" 'Sustainable Contribution (Environmental)
        End If
    End If
    If rs.Fields("SDG_S_CONTRIBUTION") = 1 Then 
        If Err.Number = 0 Then     
            strMsg = strMsg & vbNewLine & "Sust. Cont. (Social)" 'Sustainable Contribution (Social)
        End If    
    End If
    
    ' Resume error handling.
    On Error Goto 0
End If

That will simply skip the field if it holds N/A.

edited Aug 15, 2022 at 14:12

answered Aug 15, 2022 at 12:18

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

rohrl77 Over a year ago

On error resume next actually returns a TRUE for each IF statement. Thus it yields a wrong answer. Changing it to On Error Goto Someplace works, but prevents me from checking the data in the other fields, if for example the error occurs on the first one.

rohrl77 Over a year ago

Btw... IsError() returns FALSE. IsNull() returns FALSE. IsEmpty() returns FALSE. Using Nz() just produces the error again.

Gustav Over a year ago

Then try the expanded variation. See edited answer, please.

Collectives™ on Stack Overflow

vba access error 3349 reading record in linked table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related