Wise Owl Answers - How do I count the rows in an ADODB recordset?

[@imranbhatti8580]

These kinds of technicalities really needed to be addressed. The row count for a recordset had been really tricky. Thanks for explaining this Andrew.

[@WiseOwlTutorials]

My pleasure Imran, thank you for your comments as always!

[@youssefsedkey7616]

As I expected before watching, I will find out new things. thanks a lot Andy.

[@WiseOwlTutorials]

My pleasure Youssef, thank you for your comments and support!

[@krn14242]

Much more elegant way then what I hacked together. Thanks Andrew

[@WiseOwlTutorials]

Cheers Kevin, I'd be ashamed to show you my first efforts to do this, I'm sure yours is better than that!

[@janezklun]

Nicely explained, useful tutorial, Thank you Andrew, Cheers

[@WiseOwlTutorials]

Cheers Janez, happy to hear that you found it useful!

[@frikduplessis8849]

Thank you Andrew, for a short but powerful tutorial, I wish this ADO series never ends 😀 I always look forward and enjoy your tutorials...

[@WiseOwlTutorials]

😀 thanks Frik, I'm really happy to hear that you're enjoying it and thank you for the support!

[@tejamarneni]

Hi Andrew, I don't know if you remember about the SQL question I asked you couple of weeks ago and you gave me a solution by using string_agg function to get the movies released in a Pivot format with comma separated movie names. I tried a different method to display each movie name in a row using CTE. Here is I did it. First I wrote two separate CTEs' using movie name and Row_Number() function which gives the row number (Id in this case) . Then I joined these two CTEs' using Full join on the row_number column. The idea came to when I was at Gym.😛😛

[@WiseOwlTutorials]

Ha that's nice! Great stuff Ravi, thank you for sharing!

[@tejamarneni]

@@WiseOwlTutorials I forgot to post the solution. With CTE2010 As ( Select Film, Row_Number() over(order by Film) As Id Where Year(ReleaseDate) = 2010 ), CTE2011 As ( Select Film, Row_Number() over(order by Film) as Id Where Year (ReleaseDate) = 2011 ) Select CTE2010.Film As [2010], CTE2011.Film AS [2011] From CTE2010 Full Join CTE2011 ON CTE2010.Id = CTE2011.Id I hope this is right.

[@kashifkhanspecial]

Hi Andrew, Thank you so much for this knowledgeable tutorial, I just want to add one thing that I experienced this problem before, and on that time I found the solution that use cursor location "adUseClient" instead of "adUseServer", it will also give you the correct recordscount. rs.CursorLocation = adUseClient rs.Open Debug.Print rs.RecordCount I just wanted to share with you. Thanks, and God bless you. Thanks Kashif

[@WiseOwlTutorials]

Hi Kashif! Yes, that's because using a client cursor location automatically changes the cursor type to adOpenStatic. As per Microsoft's documentation "Only a setting of adOpenStatic is supported if the CursorLocation property is set to adUseClient." docs.microsoft.com/en-us/sql/ado/reference/ado-api/cursortype-property-ado?view=sql-server-ver15 Thank you for the input and for your support!

[@kashifkhanspecial]

Thank you so much for the input/reply. Thanks Kashif

[@KhalilYasser]

Thank you very much for this awesome tutorial. Isn't it better to use goto skipper statement to move to the lines of rs.close and cn.close instead of exit sub?

[@WiseOwlTutorials]

Hi Yasser! In this case it doesn't make any difference - the connection (and any associated recordsets) are closed automatically when the variable goes out of scope. If we were using module-level or project-level variables then I agree with you, it is definitely good practice to ensure that cn.close is always executed. I've written lots of code following this practice it just wasn't necessary here. Thank you for your comments and support as always!

[@devanand2355]

Hello Mr. Andrew.....It was a very nice video. Is there any way to find a specific row/column in a record set using any unique id?

[@WiseOwlTutorials]

Hello! If you want to search within a recordset that you've already populated you can use the Find method as described here docs.microsoft.com/en-us/sql/ado/reference/ado-api/find-method-ado?view=sql-server-ver15 You'll need to know the column to which you want to apply the criteria. I hope it helps!

[@itsshopboy]

Hey Andy, thanks for the great tutorials man. You're a savior of a kind. Just a quick query, how would you handle the situation where you are adding student payment, but you have to check the balance for the specific student from a different sheet, factor the payment, and record the new balance. Thanks as you consider

[@WiseOwlTutorials]

Hi Shopboy! That's not a lot of information to go on but I guess you already have some way to identify the student who is making the payment? Without knowing how your data is structured (and I'm assuming that you're using ADODB to do this as you're asking the question on an ADODB video!) you could retrieve the current balance using a recordset and a Select statement (assuming each student has only one record in the Students worksheet) rs.Source = "SELECT [Balance] FROM [Students$] WHERE [StudentID] = " & StudentIDVariable rs.Open 'assume that one row has been returned in the recordset BalanceVariable = rs.Fields("Balance").Value You can then do whatever calculation you need before inserting the values into the Payments table (I'm assuming you're using an ADODB Command object to do this?) 'assuming all your variables are numeric cmd.CommandText = "INSERT INTO [Payments$] ([StudentID], [Payment], [NewBalance]) VALUES (" & StudentIDVariable & "," & PaymentVariable & "," & NewBalanceVariable & ")" cmd.Execute

[@minitv3447]

Thanks a lot sir, Need help? Want to set password in access database using excel vba I have created the database and table using vba but can't set the password Can you help me on this Using office 365

[@WiseOwlTutorials]

Sorry I don't know the answer to that question.