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!
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)
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!
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.
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!
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.
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!
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.
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?
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 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)
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!
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.
Amazing video - I have not found anything like this online. I do have one question, however. I work in a cost accounting department; I do not use SQL, but there are two other people in the company that write SQL reporting. I am reasonably competent in VBA, all thanks to your videos. That said, there is a report they have created using SQL that I would like to automate into one of my spreadsheet---I would like to do this via VBA. If I wanted to do this, based on your video, do I just need to ask them for the server name, specific database name, and instance of SQL, and source name from them?
Thank you! That helps a lot! Last question: do I need to have the SQL server downloaded to my computer in order to make the connection work, or do I just need my coworkers to provide the items you described above?
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?
Thank you for the tutorial! I was wondering if the SQL syntax written in vba is T-SQL (the Microsoft version of SQL) or actually depends on type of database we connected to? Because although SQL is standard, there are certainly variations in MySQL, SQL Server, Oracle for example. Thank you!
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 TH-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!
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.
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, how to connect to Sybase by going to excel -data-get external data-from other sources? I don't see a sybase option but there is sybase installed in my computer. thanks.
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 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.
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 ;)
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!
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"
Another problem is, when I replace the "tblActor" with the long SQP codes generated by Access, as you did during 58-59mins, the pasted results in Excel do not have headings in the first line, which did appear if "tblActor" is applied. So could you help?
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.
It's great tutorial, but I'm facing a small issue, while opening the record set, system shows 'runtime error 446 object doesn't support named arguments ' error, how can I fixed it?
+WiseOwlTutorials I am getting a "Run-time error '-2147217900 (80040e14)': Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. " at the .Open line when test the code @36:11. I am using an Access database. My code looks just like yours aside from the filename. This is the first error i have received while going through this video.
+333mathias333 found the error... dont name the database table the same as the database :( only took a few hours to find out, hahahahaha also wanted to say this video was fantastic! i did a lot of searching on how to do exactly whats in this video but most of the time it was just random code with no explanation on what was happening. this made it very easy to figure out and modify my own code to do what i wanted :) im most likely going to be watching the next few that go along with this one!!
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 Mehran, I didn't even know this was possible! I've just had a quick look at how to do this, you can see the required connection string here www.connectionstrings.com/html-table/ - I've had a quick play but can't seem to make this work. If I get some spare time I'll look into it further. J ust curious as to why you need this technique in particular? There are many other ways to get data from HTML tables which might prove to be a little easier to work with.
Hi Andrew, I am not sure if this is the right video under which to ask my question but I will do it anyway. I am aiming to create a cross-referencing tool which would aim to relate different products with similar values (i.e. a user inputs a name - Product 1 and needs to see whether there are other products with similar characteristics, lets say they are with similar weight (1kg with a buffer of -+ 100g) while other products in the database can be 2,3,4 kg, etc.). My question: is there a way to take input from user (possibly from GUI) and then relate it to a database on Excel where the product names are in Column A and weight in Column B? Can you suggest a good starting point as well as materials to look at? Thanks a lot for the tutorial videos!
When I followed your tutorial on the 38 minutes, I got different results. Instead of Gender (male or female), i got 1 or 2. Is there any way of changing this using for each loop and offset to (male or female)? or is there other setting that i need to do?
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 Faiz, are you selecting your data from an Excel workbook using the ACE.OLEDB provider? If so, there's a limit of 255 columns, as you've discovered. One solution would be to separate the worksheet into sections using range names or just cell references and use multiple recordsets (or one recordset populated multiple times with different columns) to import the data you need.
+WiseOwlTutorials great help 1 issue though: it throws a "Syntax error in FROM clause" if you have spaces in the file path. How to fix that? Some people suggest adding double quotation marks ("") before and after the string, but it does not work for me. Any advice massively appreciated.
DBFilePath = "C:\Users\PP\Downloads\VBA App\MasterDB.accdb" ' above space is the culprit, but the app needs to be immune to it Dim sSQL As String sSQL = "SELECT * " & _ "FROM " & DBFilePath & ".Table1 Table1 " & _ "WHERE Item = '" & xCell & "';" 'xCell as Range
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.
Does this strategy work for SQL Server views as well as tables? I ask because I've swapped the '.Source code in VBA to choose a view instead of a table. I know this view takes at least five minutes to run, in SQL Server. When I run the macro, with the view, literally nothing happens. Any ideas on how to get this code to work when the query takes a long time to run (up to 10 minutes in some cases).???
I set my constant = Const ConStrSQL As String = "$N$9", where N9 is a drop down list of the connection strings for the different databases, and it keeps gving me an error saying "[Microsoft] [OBDC Driver Manager] Data source name not found and no default driver specified". Any ideas what is causing this?
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.
@@WiseOwlTutorials Thank you, it seems to be working now. When i get to ActiveConnection line i now get Object variable or with block variable not set.
Hi, I want to know how can we connect excel with access database table which is synced with sharepoint. i am able to connect to local access db but when i connect to access db which is synced with sharepoint it fails.
Hi Andrew have you got any ideas on how to import data from OLAP cubes into excel using vba? I keep getting an error message referring to the MDX query when I try. Thanks
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 th-cam.com/video/ANys4xRku5A/w-d-xo.htmlfeature=shared
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!
Andrew is head and shoulders above 90% of the video purporting to explain the machinations of the topic Andrew covers.
Beginner here. This was the best tutorial I've ever seen on TH-cam. Thanks heaps
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!
Excellent Tutorial. Very happy that you took the time to explain Late Binding also.
You are bizarrely gifted at making tutorials.
This is the best tutorial regarding Connecting to databases in VBA.
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!
Your vba excel videos has made my work very easy.Thank you
Happy to hear that Luvo, thanks for watching!
This is absolutely a gem of tutorials! Andrew, you are just awesome instructor, man!
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)
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!
You are awesome. I've grown so much as a developer and you're videos have been essential for me. Thank you.
World Need to Learn That How To Teach any subject from you., you have gifted techniques to teach anything...Just Supper..God Bless You.
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!
Supper Tutorial, Just first time shown with Proper Error Handling in ADO tutorials till now on the web,
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!
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!
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!
I was really looking forward to this tutorial and it didn't disappoint. An instructional masterpiece! Thank you!
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 ?
Great job with this walkthrough!
Thanks Robert!
Aaaand thanks for teaching....you are simply God sent
Mate, this is just brilliant!
Cheers Dean!
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.
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!
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.
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 a perfect masterclass!!!
Thank you Bernhard!
👏👏👏👏👏👏
Brilliant tutorial, and very popular, thank you for the detail around the query👍
Thanks Frik, I hope it helped!
SQL,SSRS or VBA. You're a the best.
Best example for late binding and early binding
great explanation ,if possible can you please advice how to access IP/or serial port data
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!
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!
Why the Date Field Format becomes correct when you looped over the name of the fields? (43:54)
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?
How to import to a template with discrete columns and rows or mapping the data to specific filed?
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 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)
Thank you for your tutorials!😀
You're very welcome, thank you for watching!
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!
Hello sir
Is there any way I can connect the ms access database from Excel vba but my database is stored in SharePoint plz help
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.
Amazing video - I have not found anything like this online.
I do have one question, however. I work in a cost accounting department; I do not use SQL, but there are two other people in the company that write SQL reporting. I am reasonably competent in VBA, all thanks to your videos. That said, there is a report they have created using SQL that I would like to automate into one of my spreadsheet---I would like to do this via VBA. If I wanted to do this, based on your video, do I just need to ask them for the server name, specific database name, and instance of SQL, and source name from them?
Thank you! That helps a lot! Last question: do I need to have the SQL server downloaded to my computer in order to make the connection work, or do I just need my coworkers to provide the items you described above?
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?
If I had to do a tutorial about this topic I would do it the exact same way. Well done!
Thank you for the tutorial! I was wondering if the SQL syntax written in vba is T-SQL (the Microsoft version of SQL) or actually depends on type of database we connected to? Because although SQL is standard, there are certainly variations in MySQL, SQL Server, Oracle for example. Thank you!
Hi there! You're quite right: the SQL you write depends on the type of database you're connecting to.
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 TH-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!
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?
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.
Hello great video, instead of worksheet. Add how do I drop the recordset into a named sheet?
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, how to connect to Sybase by going to excel -data-get external data-from other sources? I don't see a sybase option but there is sybase installed in my computer. thanks.
actually is to get connectionstring
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 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.
Great video! I use this every day for my job, but mostly with Autoit. Looking forward to more.
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 ;)
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!
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"
Another problem is, when I replace the "tblActor" with the long SQP codes generated by Access, as you did during 58-59mins, the pasted results in Excel do not have headings in the first line, which did appear if "tblActor" is applied. So could you help?
***** Yea,I think so.
Thanks fam, this cool af... might build a home library, but it's also useful for work!
You're very welcome!
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.
Thanks for the fairly simple tutorial. Helps a lot!
It's great tutorial, but I'm facing a small issue, while opening the record set, system shows 'runtime error 446 object doesn't support named arguments ' error, how can I fixed it?
+WiseOwlTutorials I am getting a "Run-time error '-2147217900 (80040e14)': Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. " at the .Open line when test the code @36:11. I am using an Access database. My code looks just like yours aside from the filename. This is the first error i have received while going through this video.
+333mathias333 found the error... dont name the database table the same as the database :( only took a few hours to find out, hahahahaha
also wanted to say this video was fantastic! i did a lot of searching on how to do exactly whats in this video but most of the time it was just random code with no explanation on what was happening. this made it very easy to figure out and modify my own code to do what i wanted :) im most likely going to be watching the next few that go along with this one!!
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!
how can i ensure the data outputs on the same worksheet, which gets cleared when i run the output again.
Hi Peter! You can clear a worksheet with an instruction similar to:
Worksheets("Sheet1").Cells.Clear
I hope that helps!
Can you please provide example of how to query a html table using OLE DB?
Many thanks
Hi Mehran, I didn't even know this was possible! I've just had a quick look at how to do this, you can see the required connection string here www.connectionstrings.com/html-table/ - I've had a quick play but can't seem to make this work. If I get some spare time I'll look into it further. J
ust curious as to why you need this technique in particular? There are many other ways to get data from HTML tables which might prove to be a little easier to work with.
Hi, I have used your method but getting an error message "Syntax error in FROM clause". Can you please help. If required I can paste the code.
Hi! I'd recommend this playlist for help in writing SQL in VBA th-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html
I hope it helps!
Hi Andrew, I am not sure if this is the right video under which to ask my question but I will do it anyway. I am aiming to create a cross-referencing tool which would aim to relate different products with similar values (i.e. a user inputs a name - Product 1 and needs to see whether there are other products with similar characteristics, lets say they are with similar weight (1kg with a buffer of -+ 100g) while other products in the database can be 2,3,4 kg, etc.). My question: is there a way to take input from user (possibly from GUI) and then relate it to a database on Excel where the product names are in Column A and weight in Column B? Can you suggest a good starting point as well as materials to look at? Thanks a lot for the tutorial videos!
Thanks a lot, Andrew! Your videos were really helpful
When I followed your tutorial on the 38 minutes, I got different results. Instead of Gender (male or female), i got 1 or 2. Is there any way of changing this using for each loop and offset to (male or female)? or is there other setting that i need to do?
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
Could u please help me to export more than 300 columns using recordset, only 255 fields are exporting using select *from tbl
Hi Faiz, are you selecting your data from an Excel workbook using the ACE.OLEDB provider? If so, there's a limit of 255 columns, as you've discovered. One solution would be to separate the worksheet into sections using range names or just cell references and use multiple recordsets (or one recordset populated multiple times with different columns) to import the data you need.
+WiseOwlTutorials great help
1 issue though: it throws a "Syntax error in FROM clause" if you have spaces in the file path. How to fix that? Some people suggest adding double quotation marks ("") before and after the string, but it does not work for me. Any advice massively appreciated.
DBFilePath = "C:\Users\PP\Downloads\VBA App\MasterDB.accdb"
' above space is the culprit, but the app needs to be immune to it
Dim sSQL As String
sSQL = "SELECT * " & _
"FROM " & DBFilePath & ".Table1 Table1 " & _
"WHERE Item = '" & xCell & "';" 'xCell as Range
Many thanks Andy,
works like a charm.
DBFilePath = "C:\Users\PP\Downloads\VBA App\MasterDB.accdb"
...
Dim sSQL As String
sSQL = "SELECT * " & _
"FROM Table1 " & _
"WHERE Item = '" & xCell & "';"
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.
Pls tell me the link in which you use connection string for import data from csv to excel or you can paste a code if possible to my post
Nice Video @WiseOwlTutorial I am trying to copy the result from the Query into a named tab in Excel. How do i do that?
Does this strategy work for SQL Server views as well as tables? I ask because I've swapped the '.Source code in VBA to choose a view instead of a table. I know this view takes at least five minutes to run, in SQL Server. When I run the macro, with the view, literally nothing happens. Any ideas on how to get this code to work when the query takes a long time to run (up to 10 minutes in some cases).???
Is there any way to toggle between databases? I can't think of any way besides manually changing the constant.
I set my constant = Const ConStrSQL As String = "$N$9", where N9 is a drop down list of the connection strings for the different databases, and it keeps gving me an error saying "[Microsoft] [OBDC Driver Manager] Data source name not found and no default driver specified". Any ideas what is causing this?
That fixed it, thanks!
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.
Can we use this method to add record to excel? I mean use Excel to replace Access.
Got it all working now ! thanks
Happy to hear that Bret, thanks for letting us know!
Hi Andrew. Is there a way to import the database to a Word document instead of a Worksheet?
.open does not work for me, it asks for SQL query ?
cant see the full connection string. I cant get my string to run when try to access access db
Hi Bret, try connectionstrings.com www.connectionstrings.com/access/
I hope it helps!
@@WiseOwlTutorials Thank you, it seems to be working now. When i get to ActiveConnection line i now get Object variable or with block variable not set.
I have nearly got through this, but .open does not work for me and it wants SQL query
Hi,
I want to know how can we connect excel with access database table which is synced with sharepoint. i am able to connect to local access db but when i connect to access db which is synced with sharepoint it fails.
+Tushar Saha Did you manage to figure this out, Tushar?
Hi Andrew have you got any ideas on how to import data from OLAP cubes into excel using vba? I keep getting an error message referring to the MDX query when I try.
Thanks
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!
Wonderful. First Class Lesson!!! Thank you so much!!!
very nice educational series, is there a source code available for VBA ?
great great tutorial and very thorough explanation. Thank you
Excellent Tutorial! Thank you so much wiseowl!
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 th-cam.com/video/ANys4xRku5A/w-d-xo.htmlfeature=shared