Excel VBA Introduction Part 31 - ADO (ActiveX Data Objects) Modifying Data with Recordsets

By Andrew Gould

You can download the Access Movies database here:

You can download the script to create the SQL Server Movies database used in this video at the following link:

[kebincui]

The best VBA videos associated with ADO❤❤

[LesNessman]

This series is very good. Like, very good. Like, very very good. Ok, I'll just stop here and donate. Thank you, sir.

[jeetsakharkar]

Thnx andy i got what i was looking for.
How you got that much of knowledge?
Ur my guru u made my life. Thnx for all videos i will never forget your big contribution on knowledge in my career thnx guru ji

[houstonvanhoy]

59:24 "A quick look."

Over an hour!

😊

[arthurtripp]

This is the second time your vids have saved me. I knew how to do this with DAO for .mdb but was having a hell of a time with the .accdb files. Thanks so much!

[bruce]

HI Andrew, just another thanks to you for these videos. I have a spreadsheet where I track exercises. Usually walking, hiking, stationary biking, jogging and trail running. I've watched most of your VBA videos. The first thing I did was make entries easier by building a user form after watching your user form video. I like the spreadsheet because of all the different calculations I can see in real time, but I also like to enter them into a database for archiving. I watched this video and started thinking about how to do it all in one since I've been entering into the spreadsheet and them copying and pasting into the database and sometimes I forget. It finally hit me to connect to the database by calling a sub routine after inputting data into the user form and use the values in the form to add new records to the database and also of course add them to the spreadsheet. I had a few bugs to iron out, but that helps me understand how these things work. It's working flawlessly now. Cheers!!!

[ousmanetall]

Vba is the most fun programming language

[krzysztofmaciak]

Your videos are extremely helpful and incredibly clear. It is really nice to watch and listen to you, as you have very good enunciation and nice voice. It is important as well as the substance of video.
Is there a chance that you could prepare a simple video how to execute a SQL procedure with multiple parameters and different variable types ?

[leeblack]

Your video was fun and made it amazingly easy to set up ADO connections between the various db types. Thanks a ton

P.S. Eat a few Jaffa Cakes for us poor lads in the U.S.

[VincentHardwick]

Please tell me that in the 7 intervening years between this video being posted and now... you've got round to watching Gravity?! Great movie!

Also, this Excel VBA Working with Databases playlist is going to be absolutely priceless for what I'm working on at the moment, thank you. It follows on from the request I sent in a while back about covering the MSXML2 library to create new and/or extract data from existing XML files... I taught myself enough to do that bit, and have designed the SQL database it will be stored in (again, with your help)... now I need to populate the thing from Excel VBA!! Stored procedures ahoy!

[alexsandoval]

buenos temas y la forma de enseñar muy buena y clara.

[timcago]

Loving this series. Perhaps at the end of it you could show how to let a user filter on what they want out of your movies db using a userform which then activates the queries and updates the table in front of the user.

[luismorana]

Hello, needless to say that these series of videos are very good and I have learned a lot, indeed. In particular I learned the "why's" VBA works the way it does. I have followed all the videos typing and running all code and testing it. Everything went find till Andrew explained how to delete a record, towards the end of this lesson 31. I have re-type the example several times but when the marker hits the ".Delete" it jumps to CloseRecordSet error handler (clearly there is an Error). If I comment out the ".Delete" it prints all the records with runtimeminutes that I have specified to be deleted but they are not deleted. There is no Error Code displayed. It just does not Delete those records.
I have refreshed the table in the DB, closed it and re-open it, reboot the PC, re-write the complete Delete sub in a fresh VBA module and honestly I run out of ideas after a few hours. I compared Andrew's code with my code several times and they are exactly the same, or almost, since his run smoothly while mine does not.
I am stuck. Any expert idea as of why the ".Delete" does not delete the records?
I have succeed adding, modifying, updating records according to Andrew's tutorial, but I am not able to delete records.
Thank you in advance.
Luis.
Note: Excellent job Andrew!!

[antrikshsharma]

Hello Andrew, at 42:28 you are getting error on MoviesData.CancelUpdate because you have not written “Exit Sub” before CloseRecordset Label.

[klklk]

Hi, your videos are super. Thanks for uploading. 
can you please explain how cascading parameters from T-SQL query works in excel VBA.

[deninsrmic]

Thanks Andrew, great Tutorial, I am stil going through it, but very interesting. I must say, I had some issues with access DB where I frequently got Run time Error 3219, exactly the one you had and had explained for SQL Server DB (27:11). The issue was, that It would never Update recordset after adding new record for access. I realised after mulling about for whole day what exactly could have been the reason it turned to be that the line of code .Fields(“FilmID”).Value = 266 had to be written for Access DB as well. I have tried with different CursorType, using or adUpdate, it was of not avail. Now it works, however, FilmLanguageID, FilmCountryID would not be filled by Access DB as default. I am not sure what is the reason for all these troubles, but it was interesting learning process.
Also thank you for the tutorial in 56.1, it made my life much easier in terms of which connection strings had been deprecate by Microsoft and which one to use.

[xdasdaasdasd]

Very helpful! Just wondering but do you have a video at all where you can update a closed excel book? Trying to have a master excel file and a child excel file where they both write and read into each other

[DittoGlen]

(Same as hpshiker below, should have read the comments before troubleshooting) Wow, that took some digging.  I used Movies mdb for the last lesson, and kept getting an error when I got to MoviesData.Close.  The problem ended up being the FilmID Field in the mdb is not set to AutoNumber.  I manually added .Fields("FilmID").Value = 266 and then the update worked fine.

[manumadhavan]

Thank you for teaching Basics of VBA in Excel. 
I have a doubt of using Autofilter option in VBA and copy the filtered data to another worksheet.

[markwilliams]

Why would you need to change the tables via VBA when it is usually done via Data Entry? I can understand the need to retrieve records from the source to manipulate them in Excel, but not the other way around, unless the data is entered in Access while the SQL Server is offline and you want to append the records later.