Bruno Lopes Bruno Lopes - 2 years ago 121
SQL Question

Ignore SQL Query error in Excel Macro

I have a set of queries in a EXCEL macro, that are executed when the macro is started

If, for some queries, the indicated fields don't exist in the database, executing the macro gives the following error:

Run-time error '3021': Either BOF or EOF is True, or the current record
has been deleted. requested operation requires a current record.

I want to protect the execution of the queries so that when this situation exists (lack of corresponding column in the database or any other kind of error related to the query), the macro simply continues the execution and don't output any kind of error, leaving the corresponding cell where the result supposedly would be outputted in blank.

Relevant piece of code:

Set rs = conn.Execute("SELECT ....")
strResult = rs.Fields(0)

Answer Source

You need to check that BOF and EOF arent true in an if statement. Using your code above, you could do this like so:

Set rs = conn.Execute("SELECT ....")

If Not rs.BOF and Not rs.EOF Then

    strResult = rs.Fields(0)

End If
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download