Archive for May, 2015
What’s Up Duck?
Posted by Christopher J. McClellan in Rubberduck on May 29, 2015
I’ve been promising you an update on my pet project, Rubberduck, for quite some time now. I apologize for taking so long to get around to it. Both the project and this blog tend to come last in the grand scheme of my life, so it sometimes takes me some time to get around to things. I’m afraid that may be more or less the theme of this post.
Iterate over a date range in VBA
Posted by Christopher J. McClellan in Programming, VBA on May 13, 2015
After years of VBA development, I’m still learning about the language every day. I was absolutely surprised when I tried this today. You can iterate over a date range just like you can iterate over a range of integers.
Public Sub LoopOverDateRange() Const startDate As Date = #5/13/2015# Const endDate As Date = #5/20/2015# Dim d As Date Dim i As Integer i = 1 For d = startDate To endDate Debug.Print "Iteration " & i & ":" & vbTab & d i = i + 1 Next End Sub
This code will result in the following output.
Iteration 1: 5/13/2015 Iteration 2: 5/14/2015 Iteration 3: 5/15/2015 Iteration 4: 5/16/2015 Iteration 5: 5/17/2015 Iteration 6: 5/18/2015 Iteration 7: 5/19/2015 Iteration 8: 5/20/2015
Enjoy!
Capturing A Stored Procedure’s Return Value in VBA
Posted by Christopher J. McClellan in Programming on May 12, 2015
Nice little primer on accessing stored procedure return values through ADODB in VBA.
Those of you who have ever done any serious programming with SQL Server stored procedures will know that they return an integer value to their caller on completion. The default value is ‘0’, which indicates success, and any other value indicates a failure at some stage in its processing. VBA has the power to capture these return values through the ADODB.Command object but, in order to do so, there are a few points you should consider.
1. Invoke Parameters.Refresh before Execute.
2. Specify adCmdStoredProc as the CommandType.
3. Specify an OLE DB provider but don't use MSDASQL.
4. If the Stored Procedure consists of more than one statement (most do) make sure it starts with SET NOCOUNT ON.
5. Poke/prod any returned, open recordset before examining the Return Value.
6. Use a TRY..CATCH block in your Stored Procedure to handle exceptions.
7. Use additional error handling in your VBA code.
View original post 1,012 more words
What is SQL, PL/SQL, T-SQL and difference between them
Posted by Christopher J. McClellan in Programming on May 5, 2015
A brief history of SQL.
Today I got an email from a student who is pursuing his Bachelors degree in Computer Application (BCA). He visited my blog and sent me an email regarding his confusion with terms like SQL, T-SQL, PL/SQL, and asked me what is differences between them and how are they related? I had a chat with him and told the basic differences, but he further asked me how they are related to Microsoft SQL Server, Oracle, MySQL, etc? As he is studying SQL only based upon Oracle in his course curriculum, these all terms were not clear to him, so I cleared all his doubts while chatting with him.
After a while I had a same reminiscence that when I was a student I also had these doubts and confusions, and there was nobody to guide me, but I gradually came to know about this and it took some…
View original post 728 more words