Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel VBA How to detect that SQL query returned no data?

Posted on 2011-09-02
1
Medium Priority
?
344 Views
Last Modified: 2012-05-12
I have Excel VBA code that calls a SQL Server 2008 stored proc, and all is fine when the user supplies a valid parameter. How can I tell in VBA when the user supplies a bad account number and there is no data returned from SQL Server to Excel?

I know how to check for EOF on the returned data with ADO, but this project is using OLEDB.

 'Execute stored procedure and return to a query table
    Set oQt = ActiveSheet.QueryTables.Add(Connection:=sConn, Destination:=Range("B1"), Sql:=sSql)

    oQt.Refresh     ' it stops right here when there's no data, otherwise it runs fine...

I would like to be able to trap the fact that there's no data, send the user a msg, and exit the sub.

Thanks!
0
Comment
Question by:notawahoo2
1 Comment
 

Accepted Solution

by:
notawahoo2 earned 0 total points
ID: 36474207
Never mind. I just added a teeny query to check the SQL database for the account number, and returned a '0' if it was not found. Since the teeny query will ALWAYS return something, I'm able to handle it on the VBA side.

Thanks anyway!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Loops Section Overview
Screencast - Getting to Know the Pipeline

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question