Excel VBA Introduction Part 56.6 - ADO Commands and Parameters

By Andrew Gould

[daves]

Year after year I come back to your vba videos it’s simply the most comprehensive and best👍

[sergeyt]

Dear Andrew - you are the champion as always! I watch your courses for a number of years and can hardly recall just one that I dislike, no matter how boring was the core topic itself :)
This might not fully fit the course's agenda, but I'd personally (and maybe many other viewers/subscribers too) heavily enjoy widening this already rich course content with more business-aligned cases on how to solve some real-life (short ones, of course) tasks with SQL and ADO; for example - how to efficiently upsert from excel table back to sql tables (not just one or two fields manually, but say a set of dynamically-sized excel smart tables and persisting its version parameters into sql tables, marking changes for change tracking in sql etc. ) - I love your teaching style on the technical side of things, but I somethimes hesitate how to turn this new knowledge straight to business problem solving

[joshcoder]

Wise Owl - I think I'm learning more about SQL than VBA from this series but I love it... Keep it coming. Also still waiting on the Power BI DAX tutorials.

[khaledchinobi]

Very nice tutorial, thank you for sharing your knowledge

[sashatv]

Unbelievable! Andrew, how do you find time for your amazing videos? Thank you, very much!

[carlosjoseoliveraangulo]

Wise Owl, first thanks a lot for the effort put into these videos. I've been watching the whole VBA-SQL series, and it has proved to be a wonderful help for what I'm looking forward to do!

Got a question: Just as you did in the last example of this video, I would like to create a Macro that loops through a Recordset (SQL Query) by using a parameter that references another Recordset, here is the trick: I have to do so for multiple queries (all of them filtered by the same parameter), so I was wondering if I can write down all my queries in the 'CommandText' property and then use the 'NextRecordset' method to run the following query, and copy the results into an Excel Worksheet before looping into the next value of the parameter.

Hope you can answer my question, anyways you have gained a new suscriber! Once again, thanks a lot!

[daves]

Dear Wise Owl firstly many thanks for your videos, top drawer second to none, and they have helped me in many many areas including improving my own VBA SQL solutions.

I have a question regarding the video on sending sql strings to a database via excel vba ADO.

I have used this method with great success for many years and was grateful and impressed that you developed this video series to help people as I believe its one of the top drawer capabilities of using VBA in advanced Excel solutions.

However recently, my sql "create string to be sent via the ADO execute command, length became more than 1023 characters ( and failed unless the sql string length processed by VBA execute command was less than 1024 characters although VBA displays the full string on screen), and when analysed in notepad plus plus (by copy pasting the debug.print of that string from the immediate window), you can see excel vba automatically throws in a sneaky crlf every 1023 chars to stick the multiple 1023 character strings together to make it look like one string on screen, but this machine generated crlf causes the ado execute command to fail when that line is run and it cannot be removed as VBA seems to put in automatically.

Again many thanks for the videos.

Even if you are unable to support this request, If you have a forum to help others on here with other questions they might have, I'd like to see if I can help if that's something you already do or are your looking at doing? Would be good giveback for me.

[lastdance]

Hi Andrew, you may have covered this in your tutorial already, but what is the default cursortype and locktype when using command.execute? Can we specify it using cmd.Cursortype = adLockOptimistic? Thanks a lot!

[marcq]

Hi, It is always a pleasure to follow your tutorials.
However, I have a problem with the "parameters" section and the ODBC connection at work.
We are connecting to a Chronus database via Microsoft Spark ODBC connection and when I create the command text with the "?" as parameters, at execution time, I receive error that "?" is not recognised.
Do you have any idea why?
Thank you

[henrikcarlsen]

11:40, It's bad practice not to use parameters. Scripts are prone to SQL injection, there's always a lot of problems with formatting the various data formats e.g. datetime or floats, and finally, on SqlServer it will create a new execution plan each time it is run. This stresses the server. Still, I see people build scripts without parameters over and over.

[ireneguez]

Getting an error below trying to connect to Sybase ASE database
"The specified DSN contains an architecture mismatch between the Driver and Application"
Would appreciate any help

[abdullahquhtani]

I always ask questions and you always ignore answer :-(. My question this time is:
When we put multi parameters, will the query be executed if we pass some of them or we should pass all of them?