Solved

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

Posted on 2011-09-02

1 solution

330 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!

Comment

Question by:notawahoo2

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

1 Comment

Accepted Solution

by:notawahoo2

notawahoo2 earned 0 total points

ID: 364742072011-09-02

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!

Featured Post

Question has a verified solution.

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

MS Excel - using the Hyperlink formula

Article by: Thomas

Do you use a spreadsheet like Microsoft's Excel? Have you ever wanted to link out to a non excel file on your computer or network drive? This is the way I found to do it!

Deleting Shapes - A Warning

Article by: Martin

This article describes a serious pitfall that can happen when deleting shapes using VBA.

Enumerating Prime Numbers in Excel / VBA

Video by: Ed

Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210 (2 * 3 * 5 * 7) or 2310 (2 * 3 * 5 * 7 * 11). The larger templa…