1

I have some VBA that queries a SQL database using ADODB. Currently I have the CommandTimeout set to 30 seconds. If nothing is returned, that can either be because no such record exists in the SQL database, or because the timeout was reached. I would like to be able to distinguish these two cases and display a warning when the timeout is reached. Is there a way to achieve this?

kccu
  • 283
  • 2
  • 10
  • 2
    If the timeout is reached an error is thrown so its not a problem. – Alex K. Jan 12 '21 at 15:22
  • 1
    I think you prob want to investigate why you are getting a timeout in the first place – Charlieface Jan 12 '21 at 15:54
  • @Charlieface Well that's easy, I'm querying a giant database and don't want users to have to wait longer than 30 seconds for the query to run. I don't have any control over the structure of the database I'm querying, only the VBA. – kccu Jan 12 '21 at 17:07
  • 1
    I see. Still, giant databases can be queried in milliseconds with the correct indexes, so if you have permission to create indexes then there is plenty scope to speed up queries massively – Charlieface Jan 12 '21 at 20:02
  • Unfortunately I don't have the authority to change the query either. – kccu Jan 13 '21 at 15:29
  • 1
    Does this answer your question? [How to catch SQLServer timeout exceptions](https://stackoverflow.com/questions/29664/how-to-catch-sqlserver-timeout-exceptions) – Michał Lipok Jan 21 '21 at 17:22

1 Answers1

0

Firstly read this: ADO Event Instantiation: Visual Basic

Check EventStatusEnum for adStatusOK - which should not be set in case TimeOut.

You could also catch up "InfoMessage" InfoMessage Event to see information like in SSMS when you run query.

Michał Lipok
  • 365
  • 6
  • 24
  • Is it duplicated [link](https://stackoverflow.com/questions/29664/how-to-catch-sqlserver-timeout-exceptions) ? There was solution to NET but you are using VBA. – Michał Lipok Jan 21 '21 at 17:23