Thank you so much once again. I have figured out the your channel is not only designed for beginners but to intermediate and advance as well. Your ADODB tutorials and Scripting Runtime serves as an eye opener. I have followed your channel for a decade already. I hope you still continue your C# tutorials, who knows, your videos will benefit more people in the future. ☺️☺️☺️
I have watched this video 7 years ago and moved my VBA skills to the next level. This video inspired me to explore more about VBA. This tutorial helped me a lot to create a structured dll. I thought this was too complex to understand but after understanding the concept of class modules, it is just simply amazing. Thanks Mr. Andrew. By the way sir. I am the one who always asks you about using VBA to connect to Google Sheets and Drive if you still remember. I want to tell you that the first dll that I created was the dll that connects VBA to Google Sheets API. You are my inspiration that I even mentioned your channel to other channels how good you are. Thank you.
That's fantastic to hear! Congratulations on creating your DLL, it must be a satisfying feeling. Thanks so much for taking the time to share your experience!
@@WiseOwlTutorials Yes sir. I did not know back then that your tutorials about class modules are the foundations of dll. Every time I learn something new, I always visit your videos.
I have watched many tutorials on VBA and this has the best information I have found packed into a single video. Your method of teaching and explaining WHY you are doing certain things is terrific. I'm certain you saved me hours of time trying to figure out why the code doesn't work once I give this to others by simply explaining what happens if a user doesn't have the ADODB reference library. Thank you!
I'm with Stan on this one. My first experiences with office were in Access and up until the past couple of years when I really began playing with Excel I knew much more about Access. I have connected this way before, but I had lot of problems and was basically copying code and never understood why I had problems and in some instances when I got it working I wasn't sure what it was that I did that actually worked. You explain what is going on so well and give us resources we can use to learn more. Really appreciated, thumbs up and voted as usual. I've looked around for years trying to find videos like yours. Most videos I've watched confused me more than they taught me. Please keep up the good work.
One of those Holy Grail videos that once found makes everything so much clearer. I've sort of muddled through over the years, but now I actually feel that I understand the ADO process. Thank you so much!
As an IT intern I have to say thank you so very much! Your video helped me out quite a bit as just looking online for ado guides wasn't quite helping. THANK YOU! will definitely recommend your videos to others!
This is awesome and presented so well. I have been using some ADO code on and off for a little while but not really proficient enough to have confidence to use it more. I see so many benefits across applications, so I have started investing time into understanding it better and using it regularly. Thank you so much, this video has helped me get a greater understanding of the why and how. My best part was when you explained their where Query Wizard (Editors) in SQL and Access, I never knew but simply amazing.
Hey man! thanks for sharing your knowledge and congrats for your great work on this! Not only I'm learning a lot about VBA, but watching your lessons is a great way for me to improve my english listening skill too. Your pronunciation is absolutely clear and easy to understand. Greetings from Brazil!
You are truly a one of a kind teacher! Never anything short of an amazing job no matter how complicated the subject is. I truly appreciate your talent and effort!
For newbies: he's using integrated SQL Server security, it means your actual windows user must've enough permissions in the database and there is also another mode called sql security, with a db user that can be used with any windows user... the drawback of the last method is that the user password isn't encrypted and is easily hacked (in Access is using Admin user instead, but the password issue is the same)
Amazing professor. I am so grateful with you for taking the time to teach us. I hope some day I can learn at least 15% of your knowledge , that would be good enough to shine. God bless you.
Great, i learned a new param - a cursor param adOpenForwardOnly, probably for our dinasaur IBM as400 connection it is set as default and it blocks me from RecordSet.MoveFirst (for relooping) BTW for building a conditional part of string one can pass a collection of class for condition (with 'and' + operation, values) . Looping the collection one can build a string , concatenating the latter to 'where 1=1' Nice channel, thank you Sir!
Thank you Andrew! I love you're tutorials. I'v actually experience a 'like' statement in a where clause failing when run from excel. I had no idea why it wouldn't work at the time! I've heard UA-cam has changed the rules for ad partners. I hope you're not adversely affected. I can't contribute directly to WiseOwl right not but I make a point to watch every advertisement!
Weird, for a fresh workbook, for Actor I get male/female but for Director I get 1/2 even though both fields in Access are number data type. Odd (both 2010) EDIT: Strike that, not so in SQL, just Access EDIT: I see why, the SQL script built the table with Gender nvarchar, but Access uses GenderID number data type Cool, Trev, in addition to teaching new skills, you're also honing my debugging skills, well done ;)
It's funny you should ask! I have a few videos on that topic which I'll be sharing this week. The first one will appear tomorrow which shows how to connect to a single workbook with a single worksheet. Then there will be three more videos which will show how to connect to multiple Excel files and how to loop through the collection of worksheets in the workbooks. So make sure to check back here tomorrow 😀
@@WiseOwlTutorials You are the best. It was so hard for me to learn from other channels. You make complex codes and structures so simple and quite easy to follow. Thanks.
Hi Andrew. Am getting a Run-Time Error '3001' at 'For Each MoviesField' statement. Am using MS-Excel 2016, MS-Access 2016 and SQLServer 2014 to practice.
Excellent videos!!! I think WiseOwlTuts are the 9th symphony of VBA tutorials in the internet. Today I'm struggling with the section "Setting a SQL Server Connection String" from min=16:23 -> ahead. How did you get the SQL connection? I could do the Access connection but from min 16:29 I got totally lost with the Microsoft SQL Server Studio. How do I get to this part in the video?
This definitely is one of the best series that i have ever seen on VBA coding. But would appreciate if you can share a code which takes input from excel and then establishes a connection with SQL and then executes the queries in the code and fetches the data from the database and also performs some checks (original input excel and the result from Database) on certain columns to find any deviation.
@@WiseOwlTutorials I first watched your series on sql. In these vba videos get into details (ie auto instancing, early vs late binding). Your explanations are so thorough that at times I discover the reasons to issues I couldn't solve in prior projects or you predict challenges I will likely encounter in present projects. I don't usually comment but since I got your attention, it's worth noting that you are an excellent educator.
@@benjolin1623 Very kind of you to say Ben, thanks! Happy to hear that the videos have helped with your work. Thanks for watching and taking the time to comment!
Thank you for this video...but I see a lot of static in it. Maybe a setting on my own system? Anyway, I wanted to know if there is any youtube video that can show how to access the TableDef table definition within an ADO connection. This would help me tremendously.
Hi. Which connection string in website to use to connect company's database. I have installed SQL Server 2019 developer edition, I have username, password and server name information. My excel version is 2019. Thank you for great tutorials.
Great tutorial as always Andrew. I've learned so much from this channel. I wrote some macros to Fetch data from DB2 Tables. Most things work fine. However, the Timestamp, Date, and time are all messed up & don't have the same format as in the Database. For instance, Date in DB : 2021-05-21 , Date in Excel O/P: 5/21/2021 . Similar behavior for Timestamp. DB: 2021-05-21 05:06:49.233579 in XL: 5/21/2021 5:06:49 AM How do we fix this? Suggestions! @wiseowltutorials
Thanks, glad you enjoyed it! Is this just a formatting issue? Can you change the NumberFormat property of the destination range to "yyyy-MM-dd" after the data is imported?
@@WiseOwlTutorials Sure, that can be done. However, there has to be a generic solution. Imagine I'm doing a select * from an Oracle DB and there are Account nos of 15 digits that would turn into the E format in Excel, also dates, Times, etc would be messed up. I am hoping there's a: Put whatever is in the resultset in native DB as-is into an excel thing. otherwise, it'd be a pain to loop thru all columns and act accordingly, provided we know the schema of the Table and other specifics.
@@Friedena it would be nice if there was a generic way to do it but I don't know of one (that's not to say there isn't one though!). I think at some point you'll have to format your dates, either after the query using Excel's formatting tools, or within the query using whichever formatting functions are available for the provider you're using. You can see some examples in the first ten minutes of this video ua-cam.com/video/ANys4xRku5A/v-deo.htmlfeature=shared
Comment: Hello WiseOwl. Love your vidoes. Here is something I found while following this video. When I type, "Dim MoviesField As ADODB.Field" and hit carriage return, intellisense turns ADODB.Field to ADODB.FieldAttribEnum. I actually have to arrow down. Intellisense does not come up with ADODB.Field as possible type you can set to. Not sure why this oddity is there.... If I type in as you spec't and hit down arrow instead of carriage return, intellisense does not get chance to change my text, and the program works fine.
This is all very nice. But I have no idea how to create the data base to start with. Before I can query something, I must create the thing which is being queried. You're a great teacher, someday, if I ever can get out of financial difficulty, I will happily support your patreon account.
This is a very good tutorial! Am wondering if I were to use the same idea, but instead of MS Access, but a regular excel spreadsheet NOT SETUP in table, and do not have header names, what changes would it need to change? Any chance you have videos that shows how to copy 'recordset' (again, excel data interested is not in table format and no header) and paste it onto an existing sheet? If not, perhaps a similar method? Or other websites perhaps? Reason I ask this is that I am trying to consolidate multiple sheets of data. Tried to install power query, however, due to restriction issue and no one is using it, am not able to install/use power query. Thus the VBA via connection (createobject via late binding)
Is it possible to create a user defined function that can query data from Access? For example, create a function "=RetrieveDirector("The Hunger Games")" that can connect to Access and return the film's director to that cell. If possible, what resources are available to learn how to build this?
I first want to thank you for this amazing video series. It has been amazingly helpful. Question on how I could build the data Fields names without using the 'activecell' and 'select' method in the For Each loop. I have an excel document with a dashboard tab and a source tab. I am pulling data using ADOB to the source tab and then rolling up the data to be displayed on the dashboard tab. I have this functional now but when I run a refresh from the dashboard page the view changes back to source while the data is being pulled in which I assume is due to the activecell and select commands. I would like for the user to be able to stay on the dashboard view while the refresh works in background to repopulate source tab with new data.
Hi, I am curious if the same instructions would apply for creating a connection from the excel 2016 version on a mac to a database. I'm a windows person and have no problem connecting to a SQL and MySQL DB from windows, but I need to give my macro workbook to someone on a mac. Any advice or suggestions would be helpful. Thanks!
Note: the .Field class of the ADODB parent does not appear in the intellisense when referencing the ActiveX library; however, the Field class is contained within the Microsoft Office (16) Access Database Engine Object Library and/or the Microsoft Access (16.0) Object Library. I notice that when I reference one of those libraries, intellisense provides me with a Field, Recordset, etc. class option when I declare a variable: Dim var As DAO.Field. Comments welcome if someone can clarify why this may be. In fact, I would like to know why one would reference one over the other two libraries mentioned above. Note: using Excel 2016
Hi, Great Video.. Just had one Query.. While using the Late Binding Technique with Create Object, do we not require Set MoviesField = CreateObject("ADODB.Field") ? Also how does the "For Each MoviesField In MoviesData.Fields" loop work? I mean, how does it treat MoviesData.Fields as a collection if the above SET Statement is not mentioned?
Hi Andrew, Thanks to this awesome video. However, I have a question about ADODB.Field, is it possible to place the column headers to a multicolumn listbox?Thanks.
Great video. If in my SQL statement I have a number of inputs that I need to change time to time (say project number), is there a way to create a variable (or input parameter) somewhere so that the SQL can refer to this input and SQL can be retrieved? Say I run it for X project and the other person has to run it for Y project etc? Thank you in advance!
Hi, how can you add port information to an ado connection string? I have two mysql connections on my localhost so each of them are on different ports and I can't find port info for a connection string over on the MSDN. For example 127.0.0.1:5353 I tried just changing my string to "127.0.0.1:5353" I also tried "127.0.0.1,5353"
Hi andy i am trying to insert in sql data base but i am not getting correct process. I had generated a connection and it is getting open and close but how can i insert data in sql table?
I've done everything exactly as shown but on a different dBase type, SQLite. Unfortunately, I can't get any data from the dBase using the Recordset Open method - VBE constantly returns either a syntax error "near...: table_name(1)" or "no such table", although I can get the values of the same table through the SQLite Manager and console app. Can you please help? Thanks!
Every student needs a teacher like you sir hatts off to you....
Thank you so much once again. I have figured out the your channel is not only designed for beginners but to intermediate and advance as well.
Your ADODB tutorials and Scripting Runtime serves as an eye opener. I have followed your channel for a decade already. I hope you still continue your C# tutorials, who knows, your videos will benefit more people in the future. ☺️☺️☺️
Thank you again, I'm happy to hear that the channel has helped and inspired you!
I have watched this video 7 years ago and moved my VBA skills to the next level. This video inspired me to explore more about VBA. This tutorial helped me a lot to create a structured dll. I thought this was too complex to understand but after understanding the concept of class modules, it is just simply amazing. Thanks Mr. Andrew. By the way sir. I am the one who always asks you about using VBA to connect to Google Sheets and Drive if you still remember. I want to tell you that the first dll that I created was the dll that connects VBA to Google Sheets API. You are my inspiration that I even mentioned your channel to other channels how good you are. Thank you.
That's fantastic to hear! Congratulations on creating your DLL, it must be a satisfying feeling. Thanks so much for taking the time to share your experience!
@@WiseOwlTutorials Yes sir. I did not know back then that your tutorials about class modules are the foundations of dll. Every time I learn something new, I always visit your videos.
After I created my own DLL, I felt like somehow I am developer already. You need to create your own documentation as well. 😅
Love how you always show as many possibilities and then say the advantages and disadvantages of each. Many thanks for your time!
Beginner here. This was the best tutorial I've ever seen on UA-cam. Thanks heaps
Excellent Tutorial. Very happy that you took the time to explain Late Binding also.
World Need to Learn That How To Teach any subject from you., you have gifted techniques to teach anything...Just Supper..God Bless You.
I have watched many tutorials on VBA and this has the best information I have found packed into a single video. Your method of teaching and explaining WHY you are doing certain things is terrific. I'm certain you saved me hours of time trying to figure out why the code doesn't work once I give this to others by simply explaining what happens if a user doesn't have the ADODB reference library. Thank you!
Thanks so much Karen! Happy to hear that you enjoyed the video and found it useful!
I'm with Stan on this one. My first experiences with office were in Access and up until the past couple of years when I really began playing with Excel I knew much more about Access. I have connected this way before, but I had lot of problems and was basically copying code and never understood why I had problems and in some instances when I got it working I wasn't sure what it was that I did that actually worked. You explain what is going on so well and give us resources we can use to learn more. Really appreciated, thumbs up and voted as usual. I've looked around for years trying to find videos like yours. Most videos I've watched confused me more than they taught me. Please keep up the good work.
One of those Holy Grail videos that once found makes everything so much clearer. I've sort of muddled through over the years, but now I actually feel that I understand the ADO process. Thank you so much!
Andrew is head and shoulders above 90% of the video purporting to explain the machinations of the topic Andrew covers.
This is the best tutorial regarding Connecting to databases in VBA.
As an IT intern I have to say thank you so very much! Your video helped me out quite a bit as just looking online for ado guides wasn't quite helping. THANK YOU! will definitely recommend your videos to others!
Supper Tutorial, Just first time shown with Proper Error Handling in ADO tutorials till now on the web,
Your vba excel videos has made my work very easy.Thank you
Happy to hear that Luvo, thanks for watching!
You are bizarrely gifted at making tutorials.
You are awesome. I've grown so much as a developer and you're videos have been essential for me. Thank you.
This is awesome and presented so well. I have been using some ADO code on and off for a little while but not really proficient enough to have confidence to use it more.
I see so many benefits across applications, so I have started investing time into understanding it better and using it regularly.
Thank you so much, this video has helped me get a greater understanding of the why and how.
My best part was when you explained their where Query Wizard (Editors) in SQL and Access, I never knew but simply amazing.
Thanks Ammin! Happy to hear that you found this video useful, thank you for watching and for taking the time to leave a comment!
your videos are so wonderful. I cant take my attention off not even for a sec. Andrew Gould you are such an amazing tutor!!!
Hey man! thanks for sharing your knowledge and congrats for your great work on this! Not only I'm learning a lot about VBA, but watching your lessons is a great way for me to improve my english listening skill too. Your pronunciation is absolutely clear and easy to understand. Greetings from Brazil!
Obrigado, Rodrigo! Happy to hear that you're enjoying the videos and thank you for taking the time to leave a comment!
This is absolutely a gem of tutorials! Andrew, you are just awesome instructor, man!
You are truly a one of a kind teacher! Never anything short of an amazing job no matter how complicated the subject is. I truly appreciate your talent and effort!
Beautiful tutorial Andrew! Thank you for the time and effort you put into these videos!
You're so welcome! Thanks for watching and taking the time to comment!
I was really looking forward to this tutorial and it didn't disappoint. An instructional masterpiece! Thank you!
That is really awesome and honestly looked magic to me as a beginner . Thanks to WiseOwlTutorials.
You're very welcome Ahmed, happy to hear that you found it useful!
Aaaand thanks for teaching....you are simply God sent
This is what I exactly needed to do at my workplace. Great tutorial and excellent explanation at all the steps
Hello adarsh,
I need your small help ?
For newbies: he's using integrated SQL Server security, it means your actual windows user must've enough permissions in the database and there is also another mode called sql security, with a db user that can be used with any windows user... the drawback of the last method is that the user password isn't encrypted and is easily hacked (in Access is using Admin user instead, but the password issue is the same)
Love your videos! I have to move an excel front end from an access DAO backend to an SQL Server backend using ADO and this is a great refresher!
Great ado explanation, thank you Andrew
Thanks Janez! I appreciate the comments!
SQL,SSRS or VBA. You're a the best.
👏👏👏👏👏👏
Brilliant tutorial, and very popular, thank you for the detail around the query👍
Thanks Frik, I hope it helped!
Great job with this walkthrough!
Thanks Robert!
Amazing professor. I am so grateful with you for taking the time to teach us. I hope some day I can learn at least 15% of your knowledge , that would be good enough to shine. God bless you.
Great video! You explained it so thoroughly. I wish you had also added the option of a connection to another Excel file and its differences.
Awesome explanation sir 🙏🙏🙏🇮🇳🇮🇳 it just because of you i am gaining my knowledge and upgrading to upper level
Thank you Pradeep! As always, I appreciate the comments and support!
Thank you very much! i learned Alot! hope to see more of your very detailed videos! :)
Great video! I use this every day for my job, but mostly with Autoit. Looking forward to more.
Mate, this is just brilliant!
Cheers Dean!
What a perfect masterclass!!!
Thank you Bernhard!
If I had to do a tutorial about this topic I would do it the exact same way. Well done!
Thanks fam, this cool af... might build a home library, but it's also useful for work!
You're very welcome!
Great, i learned a new param - a cursor param adOpenForwardOnly, probably for our dinasaur IBM as400 connection it is set as default and it blocks me from RecordSet.MoveFirst (for relooping)
BTW for building a conditional part of string one can pass a collection of class for condition (with 'and' + operation, values) . Looping the collection one can build a string , concatenating the latter to 'where 1=1'
Nice channel, thank you Sir!
Very nice, thank you for the great tips!
Best example for late binding and early binding
Wonderful. First Class Lesson!!! Thank you so much!!!
Thank you for your tutorials!😀
You're very welcome, thank you for watching!
Thanks for the fairly simple tutorial. Helps a lot!
This is exactly what I've been looking for, thank you!
Excellent Tutorial! Thank you so much wiseowl!
great great tutorial and very thorough explanation. Thank you
Muchas gracias, realmente muy bien explicado el tema!!!, saludos desde Lima, Perú
Bro, thanks for everything. you are my life saver
You're very welcome! Happy to hear the video helped you, thanks for watching!
Thank you again Andrew!! now I learn VBA and SQL Server XDDDD.
Thank you Andrew! I love you're tutorials. I'v actually experience a 'like' statement in a where clause failing when run from excel. I had no idea why it wouldn't work at the time! I've heard UA-cam has changed the rules for ad partners. I hope you're not adversely affected. I can't contribute directly to WiseOwl right not but I make a point to watch every advertisement!
Got it all working now ! thanks
Happy to hear that Bret, thanks for letting us know!
This is incredibly well done. It's rare to find tutorials this clear and organized. Do you have the Excel VBA code available for download?
What an awesome video I liked it very much :-)
Wow sir awesome explanation literally thank you so much sir 🙏🙏🙏🙏🇮🇳
Weird, for a fresh workbook, for Actor I get male/female but for Director I get 1/2 even though both fields in Access are number data type. Odd (both 2010)
EDIT: Strike that, not so in SQL, just Access
EDIT: I see why, the SQL script built the table with Gender nvarchar, but Access uses GenderID number data type
Cool, Trev, in addition to teaching new skills, you're also honing my debugging skills, well done ;)
great explanation ,if possible can you please advice how to access IP/or serial port data
Why the Date Field Format becomes correct when you looped over the name of the fields? (43:54)
Thank you for your hard work!!!
very helpful for a beginner.. appreciate it.
Thank for sharing. I have learned a lot from you. Can you also teach connection to other Excel?
It's funny you should ask! I have a few videos on that topic which I'll be sharing this week. The first one will appear tomorrow which shows how to connect to a single workbook with a single worksheet. Then there will be three more videos which will show how to connect to multiple Excel files and how to loop through the collection of worksheets in the workbooks. So make sure to check back here tomorrow 😀
@@WiseOwlTutorials You are the best. It was so hard for me to learn from other channels. You make complex codes and structures so simple and quite easy to follow. Thanks.
@@sokcheaheng2594 Thank you! I'm really happy to hear that you like what we do, thank you for the kind words!
Hi Andrew. Am getting a Run-Time Error '3001' at 'For Each MoviesField' statement. Am using MS-Excel 2016, MS-Access 2016 and SQLServer 2014 to practice.
Thank you Sir ! Your videos are very helpful..
This is a really great tutorial! Thank you
Thank you dear Sir for this tutorial.
Excellent videos!!! I think WiseOwlTuts are the 9th symphony of VBA tutorials in the internet. Today I'm struggling with the section "Setting a SQL Server Connection String" from min=16:23 -> ahead.
How did you get the SQL connection? I could do the Access connection but from min 16:29 I got totally lost with the Microsoft SQL Server Studio. How do I get to this part in the video?
Again, incredible tutorial!!
You knocked that out of the park!
This definitely is one of the best series that i have ever seen on VBA coding. But would appreciate if you can share a code which takes input from excel and then establishes a connection with SQL and then executes the queries in the code and fetches the data from the database and also performs some checks (original input excel and the result from Database) on certain columns to find any deviation.
Thank you that is awesome!
Just what I needed.
Still love your videos
Thanks Ben, happy to hear it!
@@WiseOwlTutorials I first watched your series on sql. In these vba videos get into details (ie auto instancing, early vs late binding). Your explanations are so thorough that at times I discover the reasons to issues I couldn't solve in prior projects or you predict challenges I will likely encounter in present projects. I don't usually comment but since I got your attention, it's worth noting that you are an excellent educator.
@@benjolin1623 Very kind of you to say Ben, thanks! Happy to hear that the videos have helped with your work. Thanks for watching and taking the time to comment!
Thank you for this video...but I see a lot of static in it. Maybe a setting on my own system? Anyway, I wanted to know if there is any youtube video that can show how to access the TableDef table definition within an ADO connection. This would help me tremendously.
You're awesome teacher!
Hello great video, instead of worksheet. Add how do I drop the recordset into a named sheet?
Very good Video. Thank you very much!
Hi.
Which connection string in website to use to connect company's database.
I have installed SQL Server 2019 developer edition, I have username, password and server name information. My excel version is 2019.
Thank you for great tutorials.
Great tutorial as always Andrew. I've learned so much from this channel. I wrote some macros to Fetch data from DB2 Tables. Most things work fine. However, the Timestamp, Date, and time are all messed up & don't have the same format as in the Database. For instance, Date in DB : 2021-05-21 , Date in Excel O/P: 5/21/2021 . Similar behavior for Timestamp.
DB: 2021-05-21 05:06:49.233579
in XL: 5/21/2021 5:06:49 AM
How do we fix this? Suggestions! @wiseowltutorials
Thanks, glad you enjoyed it! Is this just a formatting issue? Can you change the NumberFormat property of the destination range to "yyyy-MM-dd" after the data is imported?
@@WiseOwlTutorials Sure, that can be done. However, there has to be a generic solution. Imagine I'm doing a select * from an Oracle DB and there are Account nos of 15 digits that would turn into the E format in Excel, also dates, Times, etc would be messed up. I am hoping there's a: Put whatever is in the resultset in native DB as-is into an excel thing. otherwise, it'd be a pain to loop thru all columns and act accordingly, provided we know the schema of the Table and other specifics.
@@Friedena it would be nice if there was a generic way to do it but I don't know of one (that's not to say there isn't one though!). I think at some point you'll have to format your dates, either after the query using Excel's formatting tools, or within the query using whichever formatting functions are available for the provider you're using. You can see some examples in the first ten minutes of this video ua-cam.com/video/ANys4xRku5A/v-deo.htmlfeature=shared
Comment: Hello WiseOwl. Love your vidoes. Here is something I found while following this video. When I type, "Dim MoviesField As ADODB.Field" and hit carriage return, intellisense turns ADODB.Field to ADODB.FieldAttribEnum. I actually have to arrow down. Intellisense does not come up with ADODB.Field as possible type you can set to. Not sure why this oddity is there.... If I type in as you spec't and hit down arrow instead of carriage return, intellisense does not get chance to change my text, and the program works fine.
i really like this video. i am very thankful toyou.
This is all very nice. But I have no idea how to create the data base to start with. Before I can query something, I must create the thing which is being queried. You're a great teacher, someday, if I ever can get out of financial difficulty, I will happily support your patreon account.
This is a very good tutorial!
Am wondering if I were to use the same idea, but instead of MS Access, but a regular excel spreadsheet NOT SETUP in table, and do not have header names, what changes would it need to change?
Any chance you have videos that shows how to copy 'recordset' (again, excel data interested is not in table format and no header) and paste it onto an existing sheet? If not, perhaps a similar method? Or other websites perhaps?
Reason I ask this is that I am trying to consolidate multiple sheets of data. Tried to install power query, however, due to restriction issue and no one is using it, am not able to install/use power query. Thus the VBA via connection (createobject via late binding)
How to import to a template with discrete columns and rows or mapping the data to specific filed?
Is it possible to create a user defined function that can query data from Access? For example, create a function "=RetrieveDirector("The Hunger Games")" that can connect to Access and return the film's director to that cell. If possible, what resources are available to learn how to build this?
Great tutorial Andew! Could you tell me how can I create a connection with txt files to import specific data fields into my access database?
I first want to thank you for this amazing video series. It has been amazingly helpful. Question on how I could build the data Fields names without using the 'activecell' and 'select' method in the For Each loop. I have an excel document with a dashboard tab and a source tab. I am pulling data using ADOB to the source tab and then rolling up the data to be displayed on the dashboard tab. I have this functional now but when I run a refresh from the dashboard page the view changes back to source while the data is being pulled in which I assume is due to the activecell and select commands. I would like for the user to be able to stay on the dashboard view while the refresh works in background to repopulate source tab with new data.
Nevermind, I was able to figure out a solution with some extra variables and a loop counter.
Hi, I am curious if the same instructions would apply for creating a connection from the excel 2016 version on a mac to a database. I'm a windows person and have no problem connecting to a SQL and MySQL DB from windows, but I need to give my macro workbook to someone on a mac. Any advice or suggestions would be helpful. Thanks!
Note: the .Field class of the ADODB parent does not appear in the intellisense when referencing the ActiveX library; however, the Field class is contained within the Microsoft Office (16) Access Database Engine Object Library and/or the Microsoft Access (16.0) Object Library. I notice that when I reference one of those libraries, intellisense provides me with a Field, Recordset, etc. class option when I declare a variable:
Dim var As DAO.Field.
Comments welcome if someone can clarify why this may be. In fact, I would like to know why one would reference one over the other two libraries mentioned above.
Note: using Excel 2016
Hi, Great Video.. Just had one Query..
While using the Late Binding Technique with Create Object, do we not require
Set MoviesField = CreateObject("ADODB.Field") ?
Also how does the "For Each MoviesField In MoviesData.Fields" loop work? I mean, how does it treat MoviesData.Fields as a collection if the above SET Statement is not mentioned?
Hi Andrew, Thanks to this awesome video. However, I have a question about ADODB.Field, is it possible to place the column headers to a multicolumn listbox?Thanks.
AMAZING WORK!!!
Great video. If in my SQL statement I have a number of inputs that I need to change time to time (say project number), is there a way to create a variable (or input parameter) somewhere so that the SQL can refer to this input and SQL can be retrieved? Say I run it for X project and the other person has to run it for Y project etc? Thank you in advance!
Hi, how can you add port information to an ado connection string? I have two mysql connections on my localhost so each of them are on different ports and I can't find port info for a connection string over on the MSDN. For example 127.0.0.1:5353 I tried just changing my string to "127.0.0.1:5353" I also tried "127.0.0.1,5353"
Hi andy i am trying to insert in sql data base but i am not getting correct process.
I had generated a connection and it is getting open and close but how can i insert data in sql table?
very useful video!!
thank you
I've done everything exactly as shown but on a different dBase type, SQLite. Unfortunately, I can't get any data from the dBase using the Recordset Open method - VBE constantly returns either a syntax error "near...: table_name(1)" or "no such table", although I can get the values of the same table through the SQLite Manager and console app.
Can you please help? Thanks!
Nice Video @WiseOwlTutorial I am trying to copy the result from the Query into a named tab in Excel. How do i do that?
Excellent!
Thanks!