Excel VBA ADO SQL on a hosted SQL Server 'delay'

I have a hosted SQL Server I'm updating a table by running a SQL UPDATE via ADO. 50 rows, updating row-by-row from an Excel sheet. No problem there.

I just noticed that it takes a second for two for the table to actually update. That's no problem either. BUT immediately after the update I have a SELECT statement so that the update is immediately confirmed on the Excel sheet by the SELECT.

On my local PC it works great. But on the hosted server, because of the 1-2 second delay in the table actually updating the confirmation SELECT is getting the pre-update data. So it 'looks as if' the table didn't update :(

Is it possible to get a 'notification back from the SQL Server' so that I can have a Message Box saying "Done"? (then I can follow it with the confirmation SELECT)

Thanks
hindersalivaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
Is it possible to get a 'notification back from the SQL Server' so that I can have a Message Box saying "Done"? (then I can follow it with the confirmation SELECT)
we can always received a "message" from MS SQL after a statement execution. you probably need to do that in your Excel Macro.

the question is why do you need to "select" something just after an Update statement was being executed?

can you do the "select" after all 50 rows were executed?
hindersalivaAuthor Commented:
can you do the "select" after all 50 rows were executed?
Ryan, I'm finding that the SELECT (which I'm firing off immediately after the UPDATE) is being run by the SQL Server BEFORE the updates have taken place.

Anyway, I now think I'm being unduly fussy. However, for academic reasons, how would I get a message back?

My run command looks like this

   
rsData.Open sSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText

Open in new window


Thanks
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
I'm finding that the SELECT (which I'm firing off immediately after the UPDATE) is being run by the SQL Server BEFORE the updates have taken place.
so is that mean you executed the Select ... before the ... Update statement?

if we executed Update before Select statement in VBA code, it doesn't make sense if Update statement is getting executed first at MS SQL end...

However, for academic reasons, how would I get a message back?
rsData.Open sSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText
is that the "message" you trying to get here is to determine whether the statement is successfully executed? we can either return a field with a value or we simply check the recordset object's State property to determine if the connection is working.

is these what you trying to determine?
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

hindersalivaAuthor Commented:
Ryan, what I'm trying to determine is whether the UPDATE statement has completed executing on the SQL Server. That is, to confirm that all the updates are now done.

Thanks.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
sorry to come back to this question so lately.

is your problem resolved?
hindersalivaAuthor Commented:
Hi Ryan. Not resolved. I seem to recall from years ago that the server can return a message that the SQL has finished executing. (and maybe also other messages relevant to the process)

It may be the Recordset object's state property that you refer to. Can you give me the syntax for that please?
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
Recordset object's state property is only to indicate the status of your recordset's associated Connection, it doesn't tell you whether your actual data is updated at Server end.

anyway, SQL profiler may show you the info when your record is updated.

SQL Server Profiler Step by Step
https://www.codeproject.com/Articles/21371/SQL-Server-Profiler-Step-by-Step
hindersalivaAuthor Commented:
No Ryan. I need to check for it programatically.

In the absence of a message back from the server, my only option seems to be - to check last of the updating records with a SELECT statement, and if that confirms the update then take that to mean the SQL Statement has been completed executing.

(But i'm sure there was something me and my colleague used about 10 years ago)
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
ok, so it probably mean that we need to build a "custom" message that enable the front end to call and get that "custom" message.

to do that, I would suggest to create a stored procedure in which it can return that message accordingly.

is that make sense? by using a stored procedure, it can process multiple tasks but only return certain messages (or just one "custom" message) when necessary.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hindersalivaAuthor Commented:
Ryan, great. I think that's where I seem to recall (from about 10 years ago) - we had that returned message from a stored procedure. And not via VBA running the SQL on the database, in which case it looks like the VBA via ADO is sending SQL statements to the database but the database is not able to send anything back to indicate 'success'. or 'failure'.
hindersalivaAuthor Commented:
Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.