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
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.
Happy to hear that you're enjoying it! And SQL is a handy skill to learn, think of it as two for the price of one :D You might have to wait a little longer for DAX stuff but it will happen. Thanks for your patience and for watching!
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!
@@WiseOwlTutorials Thanks a lot, Andrew! I have watched your command object tutorial again and also the W3School documentation, but I am still confused about the difference between command.execute and connection.execute, apart from that the command object allows the use of parameters. If I need to use the transaction method of the connection object, can I still use command.execute in between connection.begintrans and connection.committrans?
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
this heritage is way better than that of egiptian pharaohs! :) good people will remember for good help and knowledge passed on, not just useless gigantic tourist attractions
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
Hi Irene! I'm not familiar with Sybase but perhaps these links will help stackoverflow.com/questions/8895823/the-specified-dsn-contains-an-architecture-mismatch-between-the-driver-and-appli stackoverflow.com/questions/32778354/64-bit-sql-server-linked-server-to-sybase-via-32-bit-odbc-driver Basically, you need to make sure that the "bitness" of your applications match.
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 table......;" 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. In Microsoft help "docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/line-too-long", it states that no vba string can be more than 1023 char. Have you got any ideas as to how to solve this limitation. Ive spent a couple of days on this so far, and now can't see the wood for the trees. Any ideas would be much appreciated? Issue being that this limitation really diminishes the professional usefulness of using vba ado to push realistic real world client sql table solution build (DDL) instructions to a database. Happy to talk if this does not make sense. 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.
Hi Dave, you can use the continuation character (a space followed by an underscore) in VBA to split one long line into multiple shorter ones. The command text shown below contains 1087 characters but is split into 15 lines and successfully creates a table when executed: cmd.CommandText = "CREATE TABLE TableWithAStupidlyLongNameAndLotsOfColumns(" & _ "FirstColumnWithAReallyLongNameToHoldAWholeNumber INT PRIMARY KEY IDENTITY (1,1) NOT NULL" & _ ",SecondColumnWithAReallyLongNameToHoldAUnicodeString NVARCHAR(255) NULL UNIQUE" & _ ",ThirdColumnWithAReallyLongNameToHoldADateTime2Value DATETIME2 NULL" & _ ",FourthColumnWithAReallyLongNameToHoldALargeWholeNumber BIGINT NULL" & _ ",FifthColumnWithAReallyLongNameToHoldAFloatingPointNumber FLOAT NULL" & _ ",SixthColumnWithAReallyLongNameToHoldALongTextValue NVARCHAR(MAX) NULL" & _ ",SeventhColumnWithAReallyLongNameToHoldATinyWholeNumber TINYINT NULL" & _ ",EighthColumnWithAReallyLongNameToHoldAWholeNumber INT NULL" & _ ",NinthColumnWithAReallyLongNameToHoldAUnicodeString NVARCHAR(4000) NULL" & _ ",TenthColumnWithAReallyLongNameToHoldADateTime2Value DATETIME2 NULL" & _ ",EleventhColumnWithAReallyLongNameToHoldALargeWholeNumber BIGINT NULL" & _ ",TwelfthColumnWithAReallyLongNameToHoldAFloatingPointNumber FLOAT NULL" & _ ",ThirteenthColumnWithAReallyLongNameToHoldALongTextValue NVARCHAR(MAX) NULL" & _ ",FourteenthColumnWithAReallyLongNameToHoldATinyWholeNumber TINYINT NULL)" You can also build up the command string line by line in a variable: Dim MyCmd As String
@@WiseOwlTutorials Many thanks, I ended up going with an approach based upon your second suggested solution to write out the schema DDL ( as in the past, I even came up to a limit of how many & _ you can use, back in the day not sure if that is still the case with 64bit excel), based upon a simple list of tables and associated field names. Then it seemed to work but I'm not sure why given the Microsoft limit I ran into before, but am very grateful for your time and help. For others who like me might decide to create and run full multi line database schemas (DDL), straight from excel into the chosen database to stand up solutions quickly, then this code below might help avoid another problem which would be that I found that the excel ado sql engine is limited in that it can only push forwards one command at a time and cannot deal with multi line SQL script in one go ( as a normal Database Integrated Development Environment (IDE) can, as I write my DDL's in multiple easily to read lines which can be pasted into the database IDE screen if needed to help debug during development) i.e excel can only process one line at a time as it has basic functionality, hence the multiple DDL instructions in a normal end to end piece of SQL code ( drop table if found, then create table, the add table and field comments, add primary key etc..) would need to be split into an array based upon the end of line ; delimiter. I found this piece of code and amended it so that the solution is now working:- 'firstly make all the multiline instruction text one long string, note each instruction ended with a ; as per normal sql etiquette strReplace = "" strExecuteStrTT = Trim(Replace(strExecuteStrTT, Chr(10), strReplace)) ' excel vba chr10 is the line feed strExecuteStrTT = Trim(Replace(strExecuteStrTT, Chr(13), strReplace)) ' excel vba chr13 is the carriage return 'now split the concatenated string If vbalong = True Then ' its a multiline ddl string TextStrng = strExecuteStrTT ' this is the original > 1023 char string with multiple sql statements each ending with ; passed from another ' ' 'macro Result = Split(TextStrng, ";") For i = LBound(Result()) To UBound(Result()) objConnectionT.Execute (Result(i) & ";") cl.Offset(0, 6).Value = cl.Offset(0, 6) & "Y, " ' this just helps to idenitify if the code was processed. If it did not it would error above and be ' ' capture by an error handler where the 'N' woudl be written back tot he spreadsheet. Next i end if Once again many thanks for your time and help, I hope this helps others. Please keep up the great work.
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!
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.
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?
Hi Abdullah, I have checked your comment history on the Wise Owl channel and I can see that I replied to your question from 4 days ago asking about late-binding. I also replied to your previous comment from 2 months ago asking about highlighting the min and max values in a chart. It seems that TH-cam has deleted my reply on that question but you responded to my answer with a thumbs up. Prior to that you asked a couple of questions a year ago about us doing some paid work. Unfortunately I wasn't active on TH-cam at that time but if you have formal requests like that in future it would be better to use the enquiry form on our website. If you have posted any other questions recently please let me know because they haven't been listed on my comments page, nor has TH-cam notified me of them (I've already checked the Possible Spam page and can find nothing in there from you). To answer your question: you should pass a value to all parameters when writing simple parameterised queries as shown in the video. You can make use of optional parameters when calling a stored procedure and we'll have a video on this topic soon. Thanks for watching!
WiseOwlTutorials Thank you so much for your reply. Frankly speaking you are on the top of very few youtubers who deserve to be watched. Many many thumbs up 👍🏼 👍🏼 👍🏼
One more thing if you don’t mind. It’ll be highly appreciated if you decide to make video regarding what I mentioned to explain customized query from a drop list like equal, greater than, less than start with contains. That’ll be one of it’s kind on TH-cam and I’m sure no one did that so far.
Year after year I come back to your vba videos it’s simply the most comprehensive and best👍
Thanks Dave! Happy to hear you still find the videos useful and thanks for taking the time to leave a commet!
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
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.
Happy to hear that you're enjoying it! And SQL is a handy skill to learn, think of it as two for the price of one :D
You might have to wait a little longer for DAX stuff but it will happen. Thanks for your patience and for watching!
Very nice tutorial, thank you for sharing your knowledge
Thank you Khaled, happy to hear you enjoyed it!
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!
You might find this useful stackoverflow.com/questions/40134395/setting-cursortype-with-adodb-command-execute
I hope it helps!
@@WiseOwlTutorials Thanks a lot, Andrew! I have watched your command object tutorial again and also the W3School documentation, but I am still confused about the difference between command.execute and connection.execute, apart from that the command object allows the use of parameters. If I need to use the transaction method of the connection object, can I still use command.execute in between connection.begintrans and connection.committrans?
@@lastdance903 Hi, yes! Your command object requires a connection so all that should work fine!
Thank you very much for great efforts
You're very welcome Yasser, as always!
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
Unbelievable! Andrew, how do you find time for your amazing videos? Thank you, very much!
Basically, I don't have a life :D
@@WiseOwlTutorials Your life is our knowledge!
That's a very nice way to think of it!
this heritage is way better than that of egiptian pharaohs! :) good people will remember for good help and knowledge passed on, not just useless gigantic tourist attractions
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
Hi Irene! I'm not familiar with Sybase but perhaps these links will help
stackoverflow.com/questions/8895823/the-specified-dsn-contains-an-architecture-mismatch-between-the-driver-and-appli
stackoverflow.com/questions/32778354/64-bit-sql-server-linked-server-to-sybase-via-32-bit-odbc-driver
Basically, you need to make sure that the "bitness" of your applications match.
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 table......;" 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.
In Microsoft help "docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/line-too-long", it states that no vba string can be more than 1023 char. Have you got any ideas as to how to solve this limitation. Ive spent a couple of days on this so far, and now can't see the wood for the trees. Any ideas would be much appreciated? Issue being that this limitation really diminishes the professional usefulness of using vba ado to push realistic real world client sql table solution build (DDL) instructions to a database. Happy to talk if this does not make sense.
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.
Hi Dave, you can use the continuation character (a space followed by an underscore) in VBA to split one long line into multiple shorter ones. The command text shown below contains 1087 characters but is split into 15 lines and successfully creates a table when executed:
cmd.CommandText = "CREATE TABLE TableWithAStupidlyLongNameAndLotsOfColumns(" & _
"FirstColumnWithAReallyLongNameToHoldAWholeNumber INT PRIMARY KEY IDENTITY (1,1) NOT NULL" & _
",SecondColumnWithAReallyLongNameToHoldAUnicodeString NVARCHAR(255) NULL UNIQUE" & _
",ThirdColumnWithAReallyLongNameToHoldADateTime2Value DATETIME2 NULL" & _
",FourthColumnWithAReallyLongNameToHoldALargeWholeNumber BIGINT NULL" & _
",FifthColumnWithAReallyLongNameToHoldAFloatingPointNumber FLOAT NULL" & _
",SixthColumnWithAReallyLongNameToHoldALongTextValue NVARCHAR(MAX) NULL" & _
",SeventhColumnWithAReallyLongNameToHoldATinyWholeNumber TINYINT NULL" & _
",EighthColumnWithAReallyLongNameToHoldAWholeNumber INT NULL" & _
",NinthColumnWithAReallyLongNameToHoldAUnicodeString NVARCHAR(4000) NULL" & _
",TenthColumnWithAReallyLongNameToHoldADateTime2Value DATETIME2 NULL" & _
",EleventhColumnWithAReallyLongNameToHoldALargeWholeNumber BIGINT NULL" & _
",TwelfthColumnWithAReallyLongNameToHoldAFloatingPointNumber FLOAT NULL" & _
",ThirteenthColumnWithAReallyLongNameToHoldALongTextValue NVARCHAR(MAX) NULL" & _
",FourteenthColumnWithAReallyLongNameToHoldATinyWholeNumber TINYINT NULL)"
You can also build up the command string line by line in a variable:
Dim MyCmd As String
MyCmd = "CREATE TABLE TableWithAStupidlyLongNameAndLotsOfColumns("
MyCmd = MyCmd & "FirstColumnWithAReallyLongNameToHoldAWholeNumber INT PRIMARY KEY IDENTITY (1,1) NOT NULL"
MyCmd = MyCmd & ",SecondColumnWithAReallyLongNameToHoldAUnicodeString NVARCHAR(255) NULL UNIQUE"
MyCmd = MyCmd & ",ThirdColumnWithAReallyLongNameToHoldADateTime2Value DATETIME2 NULL"
MyCmd = MyCmd & ",FourthColumnWithAReallyLongNameToHoldALargeWholeNumber BIGINT NULL"
MyCmd = MyCmd & ",FifthColumnWithAReallyLongNameToHoldAFloatingPointNumber FLOAT NULL"
MyCmd = MyCmd & ",SixthColumnWithAReallyLongNameToHoldALongTextValue NVARCHAR(MAX) NULL"
MyCmd = MyCmd & ",SeventhColumnWithAReallyLongNameToHoldATinyWholeNumber TINYINT NULL"
MyCmd = MyCmd & ",EighthColumnWithAReallyLongNameToHoldAWholeNumber INT NULL"
MyCmd = MyCmd & ",NinthColumnWithAReallyLongNameToHoldAUnicodeString NVARCHAR(4000) NULL"
MyCmd = MyCmd & ",TenthColumnWithAReallyLongNameToHoldADateTime2Value DATETIME2 NULL"
MyCmd = MyCmd & ",EleventhColumnWithAReallyLongNameToHoldALargeWholeNumber BIGINT NULL"
MyCmd = MyCmd & ",TwelfthColumnWithAReallyLongNameToHoldAFloatingPointNumber FLOAT NULL"
MyCmd = MyCmd & ",ThirteenthColumnWithAReallyLongNameToHoldALongTextValue NVARCHAR(MAX) NULL"
MyCmd = MyCmd & ",FourteenthColumnWithAReallyLongNameToHoldATinyWholeNumber TINYINT NULL)"
cmd.CommandText = MyCmd
cmd.Execute
I hope that helps!
@@WiseOwlTutorials Many thanks, I ended up going with an approach based upon your second suggested solution to write out the schema DDL ( as in the past, I even came up to a limit of how many & _ you can use, back in the day not sure if that is still the case with 64bit excel), based upon a simple list of tables and associated field names. Then it seemed to work but I'm not sure why given the Microsoft limit I ran into before, but am very grateful for your time and help.
For others who like me might decide to create and run full multi line database schemas (DDL), straight from excel into the chosen database to stand up solutions quickly, then this code below might help avoid another problem which would be that I found that the excel ado sql engine is limited in that it can only push forwards one command at a time and cannot deal with multi line SQL script in one go ( as a normal Database Integrated Development Environment (IDE) can, as I write my DDL's in multiple easily to read lines which can be pasted into the database IDE screen if needed to help debug during development) i.e excel can only process one line at a time as it has basic functionality, hence the multiple DDL instructions in a normal end to end piece of SQL code ( drop table if found, then create table, the add table and field comments, add primary key etc..) would need to be split into an array based upon the end of line ; delimiter. I found this piece of code and amended it so that the solution is now working:-
'firstly make all the multiline instruction text one long string, note each instruction ended with a ; as per normal sql etiquette
strReplace = ""
strExecuteStrTT = Trim(Replace(strExecuteStrTT, Chr(10), strReplace)) ' excel vba chr10 is the line feed
strExecuteStrTT = Trim(Replace(strExecuteStrTT, Chr(13), strReplace)) ' excel vba chr13 is the carriage return
'now split the concatenated string
If vbalong = True Then ' its a multiline ddl string
TextStrng = strExecuteStrTT ' this is the original > 1023 char string with multiple sql statements each ending with ; passed from another ' ' 'macro
Result = Split(TextStrng, ";")
For i = LBound(Result()) To UBound(Result())
objConnectionT.Execute (Result(i) & ";")
cl.Offset(0, 6).Value = cl.Offset(0, 6) & "Y, " ' this just helps to idenitify if the code was processed. If it did not it would error above and be ' ' capture by an error handler where the 'N' woudl be written back tot he spreadsheet.
Next i
end if
Once again many thanks for your time and help, I hope this helps others. Please keep up the great work.
@@daves4026 Thanks for posting such a detailed reply, I'm sure others will find it helpful!
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!
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.
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?
Hi Abdullah, I have checked your comment history on the Wise Owl channel and I can see that I replied to your question from 4 days ago asking about late-binding. I also replied to your previous comment from 2 months ago asking about highlighting the min and max values in a chart. It seems that TH-cam has deleted my reply on that question but you responded to my answer with a thumbs up. Prior to that you asked a couple of questions a year ago about us doing some paid work. Unfortunately I wasn't active on TH-cam at that time but if you have formal requests like that in future it would be better to use the enquiry form on our website. If you have posted any other questions recently please let me know because they haven't been listed on my comments page, nor has TH-cam notified me of them (I've already checked the Possible Spam page and can find nothing in there from you).
To answer your question: you should pass a value to all parameters when writing simple parameterised queries as shown in the video. You can make use of optional parameters when calling a stored procedure and we'll have a video on this topic soon.
Thanks for watching!
WiseOwlTutorials Thank you so much for your reply. Frankly speaking you are on the top of very few youtubers who deserve to be watched. Many many thumbs up 👍🏼 👍🏼 👍🏼
One more thing if you don’t mind. It’ll be highly appreciated if you decide to make video regarding what I mentioned to explain customized query from a drop list like equal, greater than, less than start with contains. That’ll be one of it’s kind on TH-cam and I’m sure no one did that so far.
Thank you Abdullah!
Thanks for the suggestion, I'll add it to the list!