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
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!
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!
Hi Vincent! Yes, indeed I have! Although I'm afraid that I didn't manage to avoid spoilers before seeing it so I think some of the effect was lost but I enjoyed it anyway (not quite in the same league as The Martian though!). Awesome work on figuring out the XML stuff, sorry I didn't get around to creating a video before you learnt how to do it. I'm glad that the SQL videos helped with the database design - you'll have fun with stored procedures, enjoy!
@@WiseOwlTutorials no need to apologise, I know the list is long and time is short! Besides, I learned a lot just by wading in and figuring it out as I went along. The videos you've been posting recently on SQL for Excel are on the watch list for after I've got my stored procedures done. If you like a good space movie, First Man and Ad Astra are both worth a try.
@@VincentHardwick Thanks Vincent! They're both on my list, I just haven't gotten round to making the time to see them yet. Perhaps they'll make it into the next version of the Movies database!
@@WiseOwlTutorials I haven't had time for movies at all for months, been too busy with study and work. Finished uni module a couple of weeks ago though, so now I just need to tear myself away from VBA and SQL for long enough to watch a few. Oh and please, call me Vince, only my dad calls me Vincent! 🙈😂 Btw, kudos to you for being one of the VERY few people who doesn't pronounce this... * ...like it's a Gaulish cartoon warrior 🤦🏻♂️ drives me mental!
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 ?
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!!!
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.
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.
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
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!!
Hello, and sorry for the delay. Yes I managed to fix the issue. Tried every single step several times. It was a silly mistake but I learned that spacing and putting sigle quotes and double quotes in the sql statement is fundamental. That was it. Thanks for the tutorial. They are excellent and I learnt how to deal with SQL from VBA. I will continue with the other tutorials. Keep up with the good job. Thanks again. Luis.
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 Recordset.Supports(CursorOption:=CursorOptionEnum.addNew) 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.
Thanks Denin! Sorry you had trouble with the Access database, it may be that the version used in the video is slightly different to the one available for downloading so apologies for that! Happy to hear that other videos have helped you as well! Thanks for taking the time to comment and for watching!
No worries Andrew, you have done here brilliant job explaining how to use ADO with both Database. I can only recommend your Chanel, best on VBA on the web. Thank you for all that knowledge you shared with us. All the best. 🍀👍
You're very welcome! And yes, you can write your code in Access rather than Excel. We have multiple videos on working with SQL Server using VBA which you can find in this playlist ruclips.net/p/PLNIs-AWhQzclbRVLCZlsFvpz6fz2nPGbt I hope it helps!
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.
Hey you know that the access file in the exercise example download has a primary key in the tblFilm table? Well it seems that this primary key isn't AutoNumber like it is in the tutorial, because of this VBA will throw an error because it requires that primary key to be amended by the user. To fix this you need to change it back to AutoNumber or manually add a number yourself in VBA to each saved record.
(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.
Hi, That's superb! I am struggling with the connection routine. and your video just gets to the point and very detailed. I have one question, Instead of Table, can we import queries to the recordset? because i have a particular queries which are outputs from the other preceding queries in Access. thanks,
You seem to gloss over key points in the Update part of the tutorial. Could you explain how data can be passed from excel sheets into the update statement like you explained for the insert ?
i was following up with this tutorial and it's it was fine until minute 50:50 when i tried to delete the films with length 150 minutes code finishes up without any error but no records being deleted
Hi, I know I am 5 years too late here but if you can explain how to connect excel to excel, read, update, delete, it would be great. I thinks ADO is a great way to work with multiple workbooks without opening the files. Also, how can we deal with password protected workbooks? Passing password in the connection string does not work.
Hi. As Everyone mentioned, your videos were really a good source for a offline learner. I'm using Excel 2013 and I'm trying to practice the above class and I ran into an issue. when I did "step into" my code, it automatically skips the new fields and closing the connection. I go through the code several times but wasn't able to identify the mistake. Could you please help me with the issue, because of which, I couldn't able to make any process in this tutorial. By the way, I'm using AccessDatabase Option Explicit Const ConstrAccess As String = "Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:\Users vundavi\Desktop\Testing\Excel VBA Testing\Movies.accdb;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False;" Sub CopyDatafromDatabase() Dim Moviesconn As ADODB.Connection Dim MoviesData As ADODB.Recordset 'Dim MoviesField As ADODB.Field Set Moviesconn = New ADODB.Connection Set MoviesData = New ADODB.Recordset Moviesconn.ConnectionString = ConstrAccess Moviesconn.Open On Error GoTo CloseCOnnection With MoviesData .ActiveConnection = Moviesconn .Source = "tblFilm" .LockType = adLockOptimistic .CursorType = adOpenDynamic .Open On Error GoTo CloseData .AddNew .Fields("FilmID").Value = 265 .Fields("FilmName").Value = "The Hitmans Bodyguard" .Fields("FilmReleaseDate").Value = #8/18/2017# .Fields("FilmDirectorID").Value = "Patrick Hughes" .Fields("FilmLanguageID").Value = "English" .Fields("FilmCountryID").Value = "USA" .Fields("FilmRunTimeMinutes").Value = "118" .Fields("FilmBudgetDollars").Value = "30000000" .Fields("FilmBoxOfficeDollars").Value = "64800000" .Update End With 'Worksheets.Add 'For Each MoviesFields In MoviesData.Fields 'ActiveCell.Value = MoviesFields.Name 'ActiveCell.Offset(0, 1).Select 'Next MoviesFields ' Range("A1").Select 'Range("A2").CopyFromRecordset MoviesData ' For Each sht In ThisWorkbook.Worksheets 'sht.Cells.EntireColumn.AutoFit 'Next sht 'Range("A1").CurrentRegion.EntireColumn.AutoFit CloseData: MoviesData.Close CloseCOnnection: Moviesconn.Close Set Moviesconn = Nothing Set Moviesconn = Nothing End Sub
Thanks for the response but I added the FilmID only after I had encountered problems. Even after, I converted it to comment, the issue persists. You are saying that Fields FilmDirectorID, FilmLanguageID and FilmCountryID would only accept numeric values but when you open the Access Database, you can see those fields were populated with the string variables and when I convert the table to design view, I do see those fields were assigned with Number Data types but they still have the string values in the data base. Could you please explain me how this was possible. By the way, your videos were very effective
Hi, Really excellent series!! Thank you! I am trying to call the recordset into an array instead of a worksheet. Using the variant v, the following works: v = MoviesData.GetRows then when/if i need the data in the sheet i do the following: Range("B1").Resize(UBound(v, 1) + 1, UBound(v, 2) + 1).Value = v But for some reason the array is transposed. Are you familiar with another method that will not transpose the result of the query?
HI, thanks a lot for the videos!! I have a question, Is it possible to have several sources, I mean, several tables? I'd like to print a field from one table and their correspondent data from other one.
***** Can I use this method (joins) to add new data in a recordset? Something like: ... AddNew. .Fields("F.filmname").Value = X .Fields("D.directorname").Value=Y .Update ...
please reply how to make a loop for .addnew ... as we want to create a loop for add new data from every cell in excel sheet.. i am not able to apply a for loop to add new data automatically..plz reply
Hi,Thanks for the fantastic tutorials! Wondering if you might have a suggestion for a problem I'm running into. I'm using MySQL 5.7.9 and Excel 2013 running on Windows 10. With the insert record macro in Part 31, I can step through all the code without error until I put in the .AddNew ..... .Update block. When I do, I get a 3219 error at the MoviesData.Close line with the error text "Operation is not allowed in this context."The database is not updated even though the step through works fine all the way through the End With statement.By the way, I'm using the default sample database called sakila that comes with MySQL and am populating the store_id, first_name, last_name and email fields.Any idea on what might cause this? (Note: I tried pasting the code here, but that doesn't seem to work ... any hints on that?)
+Jeff Wiseman Never mind. I was trying to work through that issue before proceeding in the video tutorial, but now I see that you address that problem further on. I must have a problem with one of my .Field statements.Thanks anyway.
Hi I am having a problem similar to Luis Monrana. I have no problem with the all the other subs in this video but in the DeleteExistingData() sub the .delete method seems to cause an error. When I step over the .delete it jumps to CloseRecordset error handler. If I comment the .delete out I can run through the Sub and fill up the immediate window with movies that equal 150. I gone over the code several times and it appears it should work. The only thing I can think of is that I am using the movies data base from your SQL Server 2016 series and there is some setting that is preventing the delete. I should note that it has worked with the other subs in this video. If anyone has any ideas I would like to try them. So unsatisfying when the code doesn't work lol. Great series Andrew!
I watched for another few minutes and I got a big hint. I had to change the table relationships of the Role to Film table. Under Insert and Update Specifications I changed the delete rule to Cascade. It worked when I did this
Hi Andrew, I must be doing something wrong. When I import the Access sample database the filmid field is not an autonumber, it's just a regular number and the relationship between the filmid and castid is not as shown in your video. I can delete any film in Access itself without a problem. Any idea what I'm doing wrong?
sorry sir, I have a database which has more than 100,000 rows, I used .Delete to delete all data in the database but it has just delete 10,000 rows. Could you explain this case for me? .MoveFirst Do Until .EOF .Delete .MoveNext Loop Whether this code can't exactly execute on the data which has too many rows?
Hi, Thank you for your tutorials! I am trying to add the three fields to the recordset (for new film name, release date, and film run time), however, when I step through it I get a run time error of 3219 on the line that closes the MovieData connection. In addition, the fields are not updated in the Access database. Could you advise on why that might be happening? Everything else was working fine up until then. Thanks!
+Wendy Chen I had the same issue. The reason is because in the video, the Access DB has FilmID set as AutoNumber, which automatically generates an ID. The downloadable Access DB from the WiseOwl site has it set as a Number. Since it's a key, you cannot have a blank ID and you have to explicitly state the ID in the code. So if you modify your code to be: .AddNew .Fields("FilmID").Value = 266 .Fields("FilmName").Value = "Gravity" .Fields("FilmReleaseDate").Value = #11/7/2013# .Fields("FilmRunTimeMinutes").Value = 91 .Update This will solve the issue.
Why you always using Microsoft.Ace. ? How can we successfully run your this same macro with using oledb ? Is there are some problem using oledb? ..please revert with your findings..
Hi Andrew... My code is giving error on .addnew... Ther error is about locktype. It is showing runtime error. Also giving error about my recordset doesnt support this locktype.
Sorry for bothering. If I like to Query TextBox1, which I will do Dim TextBox01 As String, TextBox01 = TextBox1... How could I write .Source?? Here below is my example Dim TextBox01 As String TextBox01 = TextBox1 .Source = "SELECT [02Inventory].SKU FROM 02Inventory WHERE ((([02Inventory].SKU) Like 'TextBox01 %'));" Would you please, help me to Search all of data populate it from TextBox1, please?
I have watched your WiseOwl Excel tutorial from 4~5 years ago. And now, I am using all of Excel VBA for my data analysis project, and it is my first time to express how much I appreciate you, WiseOwl, and it changed my career over here. Thank you, Wise Owl.
Hi! You need to concatenate the value of the text box into your SQL string. When you write this: .Source = "SELECT [02Inventory].SKU FROM 02Inventory WHERE ((([02Inventory].SKU) Like 'TextBox01 %'));" Your query is looking for values that begin with the literal text 'TextBox01' If you do this instead: .Source = "SELECT [02Inventory].SKU FROM 02Inventory WHERE ((([02Inventory].SKU) Like '" & TextBox01 & "%'));" It will concatenate the value you entered into the text box. I hope it helps!
@@WiseOwlTutorials Thank you, it works! For sure, many people are watching your video, and those are all worthy to their career or project. I am one of them, I do appreciate all of your effort to putting all these videos. Thank you!
Are there anybody who get Run-time error '3219': Operation is not allowed in this context ? Please give me the way how to resolved this error. Thanks in advance.
Hi!! The field FilmID in the database is not an Identity AutoIncremental anymore so when you write .update it is not saving the record. You have 2 option...1 just add another field with this .Fields("FilmID").Value = 266 o you set identity the field in the access db...hope that helps...
Hi sr. I have a problem with the code im working in excel 2016 and it seems that .update (method) is no working Const AccessConStr As String = _ "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\AIR\Desktop\31. ADO Modifying Data with Recordsets\Movies - Copie.accdb;Persist Security Info=False;" Sub ConnectToDB() Dim MoviesConn As ADODB.Connection Dim MoviesData As ADODB.Recordset Set MoviesConn = New ADODB.Connection Set MoviesData = New ADODB.Recordset MoviesConn.ConnectionString = AccessConStr 'test MoviesConn.Open On Error GoTo CloseConnection With MoviesData .ActiveConnection = MoviesConn .Source = "tblFilm" .LockType = adLockOptimistic 'the provider lock records only when calling update .CursorType = adOpenForwardOnly 'whatchanges would be detect, we use the most efficient .Open On Error GoTo CloseRecordSet .AddNew 'to add a new record set, un registro nuevo, agregar una linea de datos .Fields("FilmName").Value = "Gravity" .Fields("FilmReleaseDate").Value = #11/7/2013# .Fields("FilmRunTimeMinutes").Value = 91 .Update 'commit the data to the database End With ' CloseRecordSet: MoviesData.Close 'close the recordset ' CloseConnection: MoviesConn.Close Set MoviesData = Nothing Set MoviesConn = Nothing End Sub
El archivo Access que se muestra en el video tiene el campo "FileID" en formato "Autonumerar", con lo cual genera los ID automáticamente, pero la versión que se descarga no tiene esa propiedad. Por tal motivo, tenes que agregar una línea de código que genere el ID, u optar por la opción de que el nuevo ID se genere automáticamente sumando 1 al último de la tabla (requiere que previamente declares una variable Long para guardar el número de ID): With MoviesDatos .ActiveConnection = MoviesConn .Source = "tblFilm" .LockType = adLockOptimistic .CursorType = adOpenKeyset .Open
'Calculo el último número de ID .MoveLast NextID = .Fields("FilmID").Value + 1
On Error GoTo CerrarRecordset
'Agregar nuevo registro .AddNew .Fields("FilmID").Value = NextID .Fields("FilmName").Value = "Gravity" .Fields("FilmReleaseDate").Value = #11/7/2013# .Fields("FilmRunTimeMinutes").Value = 91 .Update End With
'Calculo el último número de ID .MoveLast NextID = .Fields("FilmID").Value + 1 On Error GoTo CerrarRecordset
'Agregar nuevo registro .AddNew .Fields("FilmID").Value = NextID .Fields("FilmName").Value = "Gravity" .Fields("FilmReleaseDate").Value = #11/7/2013# .Fields("FilmRunTimeMinutes").Value = 91 'método que actualiza el Recordset .Update End With
The best VBA videos associated with ADO❤❤
Thanks so much for watching and for the kind comments!
This series is very good. Like, very good. Like, very very good. Ok, I'll just stop here and donate. Thank you, sir.
Ahh thanks! Happy to hear that you like them!
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
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!
Vba is the most fun programming language
Happy to hear that you're enjoying it!
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!
Hi Vincent! Yes, indeed I have! Although I'm afraid that I didn't manage to avoid spoilers before seeing it so I think some of the effect was lost but I enjoyed it anyway (not quite in the same league as The Martian though!).
Awesome work on figuring out the XML stuff, sorry I didn't get around to creating a video before you learnt how to do it. I'm glad that the SQL videos helped with the database design - you'll have fun with stored procedures, enjoy!
@@WiseOwlTutorials no need to apologise, I know the list is long and time is short! Besides, I learned a lot just by wading in and figuring it out as I went along.
The videos you've been posting recently on SQL for Excel are on the watch list for after I've got my stored procedures done.
If you like a good space movie, First Man and Ad Astra are both worth a try.
@@VincentHardwick Thanks Vincent! They're both on my list, I just haven't gotten round to making the time to see them yet. Perhaps they'll make it into the next version of the Movies database!
@@WiseOwlTutorials I haven't had time for movies at all for months, been too busy with study and work. Finished uni module a couple of weeks ago though, so now I just need to tear myself away from VBA and SQL for long enough to watch a few. Oh and please, call me Vince, only my dad calls me Vincent! 🙈😂
Btw, kudos to you for being one of the VERY few people who doesn't pronounce this... * ...like it's a Gaulish cartoon warrior 🤦🏻♂️ drives me mental!
@@VincentHardwick Vince it is then! Hope you manage to get some time for films before the next uni module starts!
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 ?
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!!!
Great series, thank you Andrew
My pleasure Janez, happy to hear that you're enjoying it!
59:24 "A quick look."
Over an hour!
😊
😀and it could have been so much longer!
buenos temas y la forma de enseñar muy buena y clara.
Muchas gracias Alex!
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.
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.
Hi, your videos are super. Thanks for uploading.
can you please explain how cascading parameters from T-SQL query works in excel VBA.
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
Hi! Have a look at the last few videos in this playlist ruclips.net/p/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK
I hope it helps!
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!!
Hello, and sorry for the delay. Yes I managed to fix the issue. Tried every single step several times. It was a silly mistake but I learned that spacing and putting sigle quotes and double quotes in the sql statement is fundamental. That was it. Thanks for the tutorial. They are excellent and I learnt how to deal with SQL from VBA. I will continue with the other tutorials. Keep up with the good job. Thanks again. Luis.
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 Recordset.Supports(CursorOption:=CursorOptionEnum.addNew) 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.
Thanks Denin! Sorry you had trouble with the Access database, it may be that the version used in the video is slightly different to the one available for downloading so apologies for that!
Happy to hear that other videos have helped you as well! Thanks for taking the time to comment and for watching!
No worries Andrew, you have done here brilliant job explaining how to use ADO with both Database. I can only recommend your Chanel, best on VBA on the web. Thank you for all that knowledge you shared with us. All the best. 🍀👍
@@deninsrmic4165 Thanks so much for the kind comments!
Hello Andrew, at 42:28 you are getting error on MoviesData.CancelUpdate because you have not written “Exit Sub” before CloseRecordset Label.
It's very useful video thanks to you, I have small doubt can we use these techniques in Ms Access vba for connecting and modifying SQL server database
You're very welcome! And yes, you can write your code in Access rather than Excel. We have multiple videos on working with SQL Server using VBA which you can find in this playlist ruclips.net/p/PLNIs-AWhQzclbRVLCZlsFvpz6fz2nPGbt
I hope it helps!
Yes all of your tutorials are very useful.. thank you 🙏
Thank you very much
You're very welcome, thanks for watching!
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.
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.
Hey you know that the access file in the exercise example download has a primary key in the tblFilm table? Well it seems that this primary key isn't AutoNumber like it is in the tutorial, because of this VBA will throw an error because it requires that primary key to be amended by the user.
To fix this you need to change it back to AutoNumber or manually add a number yourself in VBA to each saved record.
Genius!!!
🦉
Thanks!
(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.
Hi, That's superb!
I am struggling with the connection routine. and your video just gets to the point and very detailed.
I have one question, Instead of Table, can we import queries to the recordset? because i have a particular queries which are outputs from the other preceding queries in Access.
thanks,
WiseOwlTutorials thanks, I've learned so many from your videos. 8D
You seem to gloss over key points in the Update part of the tutorial. Could you explain how data can be passed from excel sheets into the update statement like you explained for the insert ?
i was following up with this tutorial and it's it was fine until minute 50:50 when i tried to delete the films with length 150 minutes code finishes up without any error but no records being deleted
Nice Video, it help me a lot understanding queries. I'm trying to download the access movie data base from the link but i could not find it. Thanks
***** Thanks a lot
That's great training video but so fast for me. Can you add "closed caption" into this video?
Hi, I know I am 5 years too late here but if you can explain how to connect excel to excel, read, update, delete, it would be great. I thinks ADO is a great way to work with multiple workbooks without opening the files. Also, how can we deal with password protected workbooks? Passing password in the connection string does not work.
Is there a maximum number of fields for an ado recordset in VBA ? After the 10th .Fields did close the Recordset.
Hi. As Everyone mentioned, your videos were really a good source for a offline learner. I'm using Excel 2013 and I'm trying to practice the above class and I ran into an issue. when I did "step into" my code, it automatically skips the new fields and closing the connection. I go through the code several times but wasn't able to identify the mistake. Could you please help me with the issue, because of which, I couldn't able to make any process in this tutorial. By the way, I'm using AccessDatabase
Option Explicit
Const ConstrAccess As String = "Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:\Users
vundavi\Desktop\Testing\Excel VBA Testing\Movies.accdb;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False;"
Sub CopyDatafromDatabase()
Dim Moviesconn As ADODB.Connection
Dim MoviesData As ADODB.Recordset
'Dim MoviesField As ADODB.Field
Set Moviesconn = New ADODB.Connection
Set MoviesData = New ADODB.Recordset
Moviesconn.ConnectionString = ConstrAccess
Moviesconn.Open
On Error GoTo CloseCOnnection
With MoviesData
.ActiveConnection = Moviesconn
.Source = "tblFilm"
.LockType = adLockOptimistic
.CursorType = adOpenDynamic
.Open
On Error GoTo CloseData
.AddNew
.Fields("FilmID").Value = 265
.Fields("FilmName").Value = "The Hitmans Bodyguard"
.Fields("FilmReleaseDate").Value = #8/18/2017#
.Fields("FilmDirectorID").Value = "Patrick Hughes"
.Fields("FilmLanguageID").Value = "English"
.Fields("FilmCountryID").Value = "USA"
.Fields("FilmRunTimeMinutes").Value = "118"
.Fields("FilmBudgetDollars").Value = "30000000"
.Fields("FilmBoxOfficeDollars").Value = "64800000"
.Update
End With
'Worksheets.Add
'For Each MoviesFields In MoviesData.Fields
'ActiveCell.Value = MoviesFields.Name
'ActiveCell.Offset(0, 1).Select
'Next MoviesFields
' Range("A1").Select
'Range("A2").CopyFromRecordset MoviesData
' For Each sht In ThisWorkbook.Worksheets
'sht.Cells.EntireColumn.AutoFit
'Next sht
'Range("A1").CurrentRegion.EntireColumn.AutoFit
CloseData:
MoviesData.Close
CloseCOnnection:
Moviesconn.Close
Set Moviesconn = Nothing
Set Moviesconn = Nothing
End Sub
Thanks for the response but I added the FilmID only after I had encountered problems. Even after, I converted it to comment, the issue persists. You are saying that Fields FilmDirectorID, FilmLanguageID and FilmCountryID would only accept numeric values but when you open the Access Database, you can see those fields were populated with the string variables and when I convert the table to design view, I do see those fields were assigned with Number Data types but they still have the string values in the data base. Could you please explain me how this was possible. By the way, your videos were very effective
Hi,
Really excellent series!! Thank you!
I am trying to call the recordset into an array instead of a worksheet.
Using the variant v, the following works:
v = MoviesData.GetRows
then when/if i need the data in the sheet i do the following:
Range("B1").Resize(UBound(v, 1) + 1, UBound(v, 2) + 1).Value = v
But for some reason the array is transposed.
Are you familiar with another method that will not transpose the result of the query?
HI, thanks a lot for the videos!! I have a question, Is it possible to have several sources, I mean, several tables? I'd like to print a field from one table and their correspondent data from other one.
***** Can I use this method (joins) to add new data in a recordset? Something like:
...
AddNew.
.Fields("F.filmname").Value = X
.Fields("D.directorname").Value=Y
.Update
...
please reply how to make a loop for .addnew ... as we want to create a loop for add new data from every cell in excel sheet.. i am not able to apply a for loop to add new data automatically..plz reply
Hi,Thanks for the fantastic tutorials! Wondering if you might have a suggestion for a problem I'm running into. I'm using MySQL 5.7.9 and Excel 2013 running on Windows 10. With the insert record macro in Part 31, I can step through all the code without error until I put in the .AddNew ..... .Update block. When I do, I get a 3219 error at the MoviesData.Close line with the error text "Operation is not allowed in this context."The database is not updated even though the step through works fine all the way through the End With statement.By the way, I'm using the default sample database called sakila that comes with MySQL and am populating the store_id, first_name, last_name and email fields.Any idea on what might cause this? (Note: I tried pasting the code here, but that doesn't seem to work ... any hints on that?)
+Jeff Wiseman Never mind. I was trying to work through that issue before proceeding in the video tutorial, but now I see that you address that problem further on. I must have a problem with one of my .Field statements.Thanks anyway.
You are amazing...
Hi I am having a problem similar to Luis Monrana. I have no problem with the all the other subs in this video but in the DeleteExistingData() sub the .delete method seems to cause an error. When I step over the .delete it jumps to CloseRecordset error handler. If I comment the .delete out I can run through the Sub and fill up the immediate window with movies that equal 150.
I gone over the code several times and it appears it should work. The only thing I can think of is that I am using the movies data base from your SQL Server 2016 series and there is some setting that is preventing the delete. I should note that it has worked with the other subs in this video.
If anyone has any ideas I would like to try them. So unsatisfying when the code doesn't work lol.
Great series Andrew!
I watched for another few minutes and I got a big hint. I had to change the table relationships of the Role to Film table. Under Insert and Update Specifications I changed the delete rule to Cascade. It worked when I did this
How would you update excel protected sheet using adodb
Hi Andrew,
I must be doing something wrong. When I import the Access sample database the filmid field is not an autonumber, it's just a regular number and the relationship between the filmid and castid is not as shown in your video. I can delete any film in Access itself without a problem. Any idea what I'm doing wrong?
sorry sir, I have a database which has more than 100,000 rows, I used .Delete to delete all data in the database but it has just delete 10,000 rows. Could you explain this case for me?
.MoveFirst
Do Until .EOF
.Delete
.MoveNext
Loop
Whether this code can't exactly execute on the data which has too many rows?
Hi,
Thank you for your tutorials! I am trying to add the three fields to the recordset (for new film name, release date, and film run time), however, when I step through it I get a run time error of 3219 on the line that closes the MovieData connection. In addition, the fields are not updated in the Access database. Could you advise on why that might be happening? Everything else was working fine up until then.
Thanks!
+Wendy Chen I had the same issue. The reason is because in the video, the Access DB has FilmID set as AutoNumber, which automatically generates an ID. The downloadable Access DB from the WiseOwl site has it set as a Number. Since it's a key, you cannot have a blank ID and you have to explicitly state the ID in the code. So if you modify your code to be:
.AddNew
.Fields("FilmID").Value = 266
.Fields("FilmName").Value = "Gravity"
.Fields("FilmReleaseDate").Value = #11/7/2013#
.Fields("FilmRunTimeMinutes").Value = 91
.Update
This will solve the issue.
This was helpful thanks.
Why you always using Microsoft.Ace. ? How can we successfully run your this same macro with using oledb ?
Is there are some problem using oledb? ..please revert with your findings..
Hi Andrew... My code is giving error on .addnew... Ther error is about locktype. It is showing runtime error. Also giving error about my recordset doesnt support this locktype.
It is runtime error 3571. I have choosen forward only locktype
Sorry 3251
Sorry for bothering. If I like to Query TextBox1, which I will do Dim TextBox01 As String, TextBox01 = TextBox1...
How could I write .Source??
Here below is my example
Dim TextBox01 As String
TextBox01 = TextBox1
.Source = "SELECT [02Inventory].SKU FROM 02Inventory WHERE ((([02Inventory].SKU) Like 'TextBox01 %'));"
Would you please, help me to Search all of data populate it from TextBox1, please?
I have watched your WiseOwl Excel tutorial from 4~5 years ago. And now, I am using all of Excel VBA for my data analysis project, and it is my first time to express how much I appreciate you, WiseOwl, and it changed my career over here. Thank you, Wise Owl.
Hi! You need to concatenate the value of the text box into your SQL string. When you write this:
.Source = "SELECT [02Inventory].SKU FROM 02Inventory WHERE ((([02Inventory].SKU) Like 'TextBox01 %'));"
Your query is looking for values that begin with the literal text 'TextBox01'
If you do this instead:
.Source = "SELECT [02Inventory].SKU FROM 02Inventory WHERE ((([02Inventory].SKU) Like '" & TextBox01 & "%'));"
It will concatenate the value you entered into the text box.
I hope it helps!
@@WiseOwlTutorials Thank you, it works! For sure, many people are watching your video, and those are all worthy to their career or project. I am one of them, I do appreciate all of your effort to putting all these videos. Thank you!
Are there anybody who get
Run-time error '3219':
Operation is not allowed in this context ?
Please give me the way how to resolved this error. Thanks in advance.
I have it if i find a solution i will post it here
Hi!! The field FilmID in the database is not an Identity AutoIncremental anymore so when you write .update it is not saving the record. You have 2 option...1 just add another field with this .Fields("FilmID").Value = 266 o you set identity the field in the access db...hope that helps...
But is your site shut down?
Oh my, good to know, for a second I was afraid you had failed!
.. so it chopped 14 mins of the runtime of "Titanic" that can only be a good thing ain't it? ...Excellent😜
I was struck at timeline 27:00 with error 3219 and it took me 3 hrs to research as why this was happening?
Hi sr.
I have a problem with the code im working in excel 2016 and it seems that .update (method) is no working
Const AccessConStr As String = _
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\AIR\Desktop\31. ADO Modifying Data with Recordsets\Movies - Copie.accdb;Persist Security Info=False;"
Sub ConnectToDB()
Dim MoviesConn As ADODB.Connection
Dim MoviesData As ADODB.Recordset
Set MoviesConn = New ADODB.Connection
Set MoviesData = New ADODB.Recordset
MoviesConn.ConnectionString = AccessConStr
'test
MoviesConn.Open
On Error GoTo CloseConnection
With MoviesData
.ActiveConnection = MoviesConn
.Source = "tblFilm"
.LockType = adLockOptimistic 'the provider lock records only when calling update
.CursorType = adOpenForwardOnly 'whatchanges would be detect, we use the most efficient
.Open
On Error GoTo CloseRecordSet
.AddNew 'to add a new record set, un registro nuevo, agregar una linea de datos
.Fields("FilmName").Value = "Gravity"
.Fields("FilmReleaseDate").Value = #11/7/2013#
.Fields("FilmRunTimeMinutes").Value = 91
.Update 'commit the data to the database
End With
'
CloseRecordSet:
MoviesData.Close 'close the recordset
'
CloseConnection:
MoviesConn.Close
Set MoviesData = Nothing
Set MoviesConn = Nothing
End Sub
Adolfo Izquierdo Ramos Did you resolve the issue?
El archivo Access que se muestra en el video tiene el campo "FileID" en formato "Autonumerar", con lo cual genera los ID automáticamente, pero la versión que se descarga no tiene esa propiedad. Por tal motivo, tenes que agregar una línea de código que genere el ID, u optar por la opción de que el nuevo ID se genere automáticamente sumando 1 al último de la tabla (requiere que previamente declares una variable Long para guardar el número de ID):
With MoviesDatos
.ActiveConnection = MoviesConn
.Source = "tblFilm"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
'Calculo el último número de ID
.MoveLast
NextID = .Fields("FilmID").Value + 1
On Error GoTo CerrarRecordset
'Agregar nuevo registro
.AddNew
.Fields("FilmID").Value = NextID
.Fields("FilmName").Value = "Gravity"
.Fields("FilmReleaseDate").Value = #11/7/2013#
.Fields("FilmRunTimeMinutes").Value = 91
.Update
End With
With MoviesDatos
.ActiveConnection = MoviesConn
.Source = "tblFilm"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
'Calculo el último número de ID
.MoveLast
NextID = .Fields("FilmID").Value + 1
On Error GoTo CerrarRecordset
'Agregar nuevo registro
.AddNew
.Fields("FilmID").Value = NextID
.Fields("FilmName").Value = "Gravity"
.Fields("FilmReleaseDate").Value = #11/7/2013#
.Fields("FilmRunTimeMinutes").Value = 91
'método que actualiza el Recordset
.Update
End With
37:18 Loop over a recordset
48:33 Delete records
you got a FB account?
facebook.com/don.willson.92
here is mine
What does a man do at 5 am when the neighbor's rooster wakes him up on a Saturday morning? Correct! Watch a vba video.
😀 I was going to suggest moving house but your solution is less expensive!