How to use ADO and VBA to Read from a Database

[@Excelmacromastery]

Let me know what you think of this video in the comments below. Make sure to download the code examples from the description.

[@thuthaonguyen8424]

Thank u so much for your helpful video. I have a question which i will be very greatful if getting ur answer for. Could ADO connects Bigquery (data source in Google cloud) and excel worksheet? And if yes, please walk me through the codes 🙏🙏🙏

[@eeius986]

Amazing once again Paul, Like ALL your videos! Now using it on my project :). What about an Userform to take pictures from the webcam and once image is approved it uploads to a specific cell in Excel? Just an idea for your next video ;)

[@TheAjayiscool]

Great resource. I am your biggest fan Paul. Facing a bottleneck in my project. Details are in my post. Appreciate any guidance

[@danielmadariagavaldes893]

I already knew most of these stuff, but man, what a brilliant explanation!!!!!! Marvelous job!!

[@Excelmacromastery]

Thanks Daniel

[@philipmcdonnell7168]

My unending work project has to be modded to use Access/SQL Server at some point so this is very timely, Paul. Thanks. Will you be doing a write changes back to the DB and how to 'delete' data in future videos?

[@efraintrejogomez6185]

me sorprende cuanto conocimiento sobre este lenguaje VBA, Saludos cordiales maestro, estoy aprendiendo mucho de usted, a pesar de la barrera del idioma...

[@musabashraf]

Thanks for the video. You cleared many doubts I had.

[@ascarrunz]

Paul, awesome video. Thanks a lot for you tips. But Could you do video of what is faster: ADO+VBA or PowerQuerry?

[@javiermolinaperez1671]

Really valuable ! I've been searching on the internet for a solution with no chances at all since now ! Many thanks, Late binding seems to work with other computers :)

[@officetricks6303]

Thanks a lot, learned a very simple way to solve complex data, keep posting...

[@persaq]

Perfect! Thank you very much, Paul.

[@SimpleExcelVBA]

I haven't yet used this for actual databases, only for Excel worksheet. Need to try with that. Good video :)

[@tigranhayrapetyan8357]

Very hard for me to understand all of this code, but it's definitely useful thing in job. Will return to it later and make it usable in my everyday work) Thank you Paul for your videos!

[@jimfitch]

Great tutorial, Paul. And perfectly timed - just started a project to extract Access data to Excel workbooks for daily & on-demand status reporting & analysis. Thank you!

[@Excelmacromastery]

Thanks Jim. Glad it was timely for you.

[@lalitthapa007]

There should be a heart ❤ button in addition to 👍 button.. thank you !!

[@jillianonthehudson1739]

This was awesome! Thanks! I really don't understand how anyone could downvote content like this

[@Excelmacromastery]

Thanks Jillian

[@kebincui]

Mind blowing👍👍. Thanks Paul

[@Excelmacromastery]

Glad you enjoyed it

[@hichamhadj9640]

As always, Excellent video !

[@big1975E]

Awesome video! I’ve used the knowledge I gained from this video multiple times. It has been a real game changer for me! Is it possible to use an ADO connection to connect to a SharePoint online list? SharePoint online seems to be being used more and more. Thanks!!

[@victorgarmin2311]

Thanks for sharing, nice video, do you have an example of the conexion string for a oracle database??

[@granadosvm]

It might be a good idea listing a site or page where the standard SQL syntax code could be explored. For people who are new to this to understand the power of these connections, it would be good to know the potential of SQL code. When you pass parameters from user's input (they could be cells in a sheet) these type of code can be used to create very useful and flexible dashboards.

[@MrKhaldonalali]

Excellent video, thank you very much for it

[@Excelmacromastery]

You're welcome.

[@frikduplessis8849]

Thank you Paul, it has been a long time ⌛ clear explanation on the error handling 👍

[@Excelmacromastery]

Happy to help Frik.

[@mike_case]

Great video. Thank you Paul!!!

[@Excelmacromastery]

Glad you liked it Michal!

[@kuaatrichard5806]

Hello Paul, you make great vodeos. Very good to understand. However, I would also be very happy about a database query from a MySQL database.

[@kamalam29]

Hi Paul. Thanks a lot for this video. Although the best part of this tutorial is "How to close the connection correctly" which is very important for big professional projects. Cleaning the objects before the cursor leaves procedure or a function, is a very very delicate and yet complex thing to understand and I think every professional should have an idea of that. it would be great if you can create a tutorial just explaining the different ways to cleaning the objects like acrobat objects, excel objects, word objects, so objects, etc, etc.

[@VS.R3LeVant]

Easy to understand thanks for sharing

[@Excelmacromastery]

Glad it was helpful!

[@kurtisfickle]

This is fantastic! Thank you!

[@Excelmacromastery]

You're very welcome Kurtis!

[@Ronaldog2207]

Great Video!!! Congrats!

[@vincentlee7995]

Thanks Paul.

[@Excelmacromastery]

You're welcome Vincent.

[@Gg-oh2zy]

Neat n clean explanations 😀

[@johnbutler2750]

Brilliant stuff! Thanks

[@Excelmacromastery]

Glad you enjoyed it John!

[@leeblack2103]

Very Nice! Can you work on a video to work on doing API calls using VBA?

[@Excelmacromastery]

It's been on my list for a while now but I hope to do it soon.

[@carlosfinholdt4190]

Great job. Thanks.

[@Excelmacromastery]

You're welcome Carlos.

[@oliveroshea5765]

Thanks Paul!

[@Excelmacromastery]

You're welcome Oliver.

[@lqc1014]

good skill

[@engrvarsi3774]

Many thanks

[@Excelmacromastery]

You are welcome

[@JStruthers89]

Hi Paul, I was wondering if it is possible to pass a sharepoint list directly to the Named Manager, instead of printing it to a worksheet with ListObject? Thanks in advance

[@TheAjayiscool]

Awesome video Paul. I have implemented late binding and close connection the way you explained in a project with MS Access as backend. Back end is deployed in a shared drive to which my users have read/ write permission. There are action queries in the project using SQL INSERT and UPDATE also SELECT for reading data into a recordset. So far so good. Tool works like charm! However on a stress test with 40 + users simultaneously using tool, saw error saying "The database has been placed in a state by user 'Admin' on Machine that prevents it from being opened or locked" This some times keeps the backend in exclusive mode and at times corrupts the database. Paul, where am going wrong. Any help will be greatly appreciated Ajay

[@govindkumarsodani3290]

very usefull

[@zakimoustapha7296]

Is it possible to create a crystal report from excel workbook? If yes then I think it's an important topic to talk about Teacher.

[@danilklimov3233]

Спасибо

[@azul3k]

Thanks, I'd like to know how to execute a procedure and how to close the connection.

[@akdhunt]

Brilliant explanation! With late binding is posible to use ADO with Excel for Mac?

[@nealonions7654]

No unfortunately not. The only (sensible/practical) way I know for Mac is to use ODBC and then use a QueryTable. Then assign the result to a variant. May also need an ODBC drive like the commercial ones from Actual Technologies.

[@averagebodybuilder]

I actually use late binding for excel.

[@nealonions7654]

@@averagebodybuilder What on a Mac not running windows??? As far as I know there is no library to link to and then late bind that runs on OSX?

[@averagebodybuilder]

Oops I didn't read the part where he wrote Mac

[@serdip]

Great video. Thanks for posting. In addition to looking up standard connection strings, I like to use the DataLinks object as follows: Dim objDL As Object Dim vntConnection As Variant On Error Resume Next Set objDL = CreateObject("DataLinks") vntConnection = objDL.PromptNew() If Err 0 Then Err.Clear Exit Sub End If The PromptNew() method will display the Data Link Properties dialog in which the user can specify the Access database, SQL Server database, Excel workbook etc and return the connection string, which does not have to be edited.

[@Excelmacromastery]

Thanks for the feeback. I haven't used the DataLink.

[@big1975E]

If I’m connecting connecting to an Excel workbook with ADO is it possible to edit data in that workbook in addition to querying data from that workbook?

[@AlexB_o7]

Please do a video on how to store the RS into an array and to operate the data from inside the vector. (index match into the array).

[@phpvbacoder9000]

Write to array with rs.getrows

[@muhannad_youssef]

Thank you sir ... how i can find last purchase price from table ( date - product - price - purchase or sales )

[@oliveroshea5765]

Hi Paul, could this be achieved by using Office Scripts? I like VBA, but was wondering if Office Scripts offered similar methods. Cheers Oliver

[@Excelmacromastery]

Good question Oliver. At the moment Office Scripts interacts with everything through Power Automate. There may be the possibility to use external libraries in the future but for the moment it's no possible.

[@kuldar.]

Interesting, but next level...

[@Excelmacromastery]

Glad you like it.

[@rajsurendra7040]

How to import to a template with discrete columns and rows or mapping the data to specific filed

[@gavdownes100]

This works great, thanks. Question: How do you use JOIN/ INNER JOIN with regards to Junction tables? I cannot find any tutorials online. To clarify what I am trying to learn, my junction table has a client_ID and product_ID linking to tables Clients and Products. How do I retrieve the Junction table as Clients.name and Products.name

[@nealonions7654]

First give your ‘junction table a name, say tblClientProduct. Along with that let’s have tblClient and tblProduct Could try SELECT p.product_name , c.customer_name FROM tblClientProduct AS cp INNER JOIN tblClient AS c ON cp.client_id = c.client_id INNER JOIN tblProduct AS p ON cp.product_id = p.product_id

[@gavdownes100]

@@nealonions7654 hmmm. Thank you. Will look at that very closely soon

[@johnwayne8059]

Very good video Paul!👍👍👍 Btw: When I use "exit sub" in sub (e.g. "DoSomething") and then use "DoSomething" in an other sub (e.g. "Main"), I often had the problem that Main also got stopped! Do you've some experience with these "syndrome"? I decided to apply a "goto" to jump over the error handling! Do you think that's a good way? B|R from Germany 🤟😎 and thanx in advance!😉

[@Excelmacromastery]

Error Handling is tricky when unwinding the code. Check out this video kzbin.info/www/bejne/ooOYlmudrqZpb7c

[@johnwayne8059]

@@Excelmacromastery okay, thank you, that's also a good explanation! But how can I manage that the Code don't stop. Because I call a sub which is not neccesary for following actions, it's just a nice-to-have in relation to its function! I hope that's a better description what I'msearching for!😇

[@karvannansa8045]

How to add custom column with sequence? Sir?

[@AS-ym2bp]

Can you make a video on how to read and write data from and to an SQL database through VBA?

[@Excelmacromastery]

The code for reading from an SQL database is almost the same as the code used here to read from an Access database. The only difference is the connection string. I'll be doing a video, on writing to a database from a worksheet, soon.

[@3Starsgamer34]

Is ADO still relevant post power query?

[@Excelmacromastery]

Of course. There are countless scenarios where PQ wouldn't be suitable. For example, an application that retrieves data to a UserForm based on real-time user selections.

[@TheDinga1977]

I want to use ADO to get data from an access database that is password protected and the database is stored on a network drive with a specific userid and password instead of the active windows userid. I have searched the web and tried many connection strings…can someone point me in the right direction?

[@TheDinga1977]

I also want the data to be put in an array to be used in the vba not put into a sheet.

[@alwilson6471]

Unable to download the code. The "get the code" option just hangs.....

[@Excelmacromastery]

Turn off any popup blockers and it should work.

[@alihamiad7849]

I never get the source code; Am I doing something wrong?

[@Excelmacromastery]

Send me your email address and I will check it. Paul@ExcelMacroMastery

[@briandennehy6380]

Thank god for Power Query I say 🤣🤣🤣

[@Excelmacromastery]

Hi Brian, do you think the code is too complex?

[@briandennehy6380]

@@Excelmacromastery Hi Paul, for me as someone who is learing VBA, it is a bit advanced. I just love the ease of Power Query to do this sort of thing but it is very well explained in the video

[@oliveroshea5765]

@@briandennehy6380 it’s no more complex than the M code in the advanced editor in Power Query

[@briandennehy6380]

@@oliveroshea5765 True but with Power Query you can use the GUI to connect to a database without the need to know VBA.

[@partymaschine92]

Again another great video of you, but I have to admit that I must totally aggree with Brian. Could you explain the advantage to crawl data via vba? In the video it seems to be faster, but I am not sure. I am comfortable with both kind of codes VBA and M and would love to give both versions a chance, however I love to use PQ in Excel. It is so much easier to pass on the file to colleagues who are not able to run macros due to companies restrictions.

[@mtotowamungu8259]

Thank you Paul

[@Excelmacromastery]

You're welcome