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 🙏🙏🙏
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 ;)
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!!
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?
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?
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
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
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.
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
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
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?
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.
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!
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 :)
me sorprende cuanto conocimiento sobre este lenguaje VBA, Saludos cordiales maestro, estoy aprendiendo mucho de usted, a pesar de la barrera del idioma...
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.
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.
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.
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 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!😇
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!
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.
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.
@@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
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.
![[Full Episode] The Restaurant War Thailand ศึกพ่อค้าซ่าแม่ค้าแซ่บ Episode 2 | 29 ก.ย. 67](http://i.ytimg.com/vi/r3lfLNH6Ke4/mqdefault.jpg)
Let me know what you think of this video in the comments below. Make sure to download the code examples from the description.
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 🙏🙏🙏
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 ;)
Great resource. I am your biggest fan Paul. Facing a bottleneck in my project. Details are in my post. Appreciate any guidance
There should be a heart ❤ button in addition to 👍 button.. thank you !!
Thanks for the video. You cleared many doubts I had.
Perfect! Thank you very much, Paul.
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.
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!!
Very Nice! Can you work on a video to work on doing API calls using VBA?
It's been on my list for a while now but I hope to do it soon.
Great Video!!! Congrats!
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?
Paul, awesome video. Thanks a lot for you tips.
But Could you do video of what is faster: ADO+VBA or PowerQuerry?
How to import to a template with discrete columns and rows or mapping the data to specific filed
Great job. Thanks.
You're welcome Carlos.
Many thanks
You are welcome
I already knew most of these stuff, but man, what a brilliant explanation!!!!!! Marvelous job!!
Thanks Daniel
Interesting, but next level...
Glad you like it.
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?
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
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
@@nealonions7654 hmmm. Thank you. Will look at that very closely soon
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.
Thanks for sharing, nice video, do you have an example of the conexion string for a oracle database??
I haven't yet used this for actual databases, only for Excel worksheet. Need to try with that. Good video :)
How to add custom column with sequence? Sir?
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
Thanks a lot, learned a very simple way to solve complex data, keep posting...
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
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?
I also want the data to be put in an array to be used in the vba not put into a sheet.
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.
Thank you sir ... how i can find last purchase price from table ( date - product - price - purchase or sales )
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.
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!
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 :)
Thanks, I'd like to know how to execute a procedure and how to close the connection.
me sorprende cuanto conocimiento sobre este lenguaje VBA, Saludos cordiales maestro, estoy aprendiendo mucho de usted, a pesar de la barrera del idioma...
good skill
Is ADO still relevant post power query?
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.
Hi Paul, could this be achieved by using Office Scripts? I like VBA, but was wondering if Office Scripts offered similar methods.
Cheers
Oliver
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.
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.
Thanks for the feeback. I haven't used the DataLink.
As always, Excellent video !
Спасибо
Unable to download the code. The "get the code" option just hangs.....
Turn off any popup blockers and it should work.
Neat n clean explanations 😀
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!😉
Error Handling is tricky when unwinding the code. Check out this video th-cam.com/video/lR5e8gyA69U/w-d-xo.html
@@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!😇
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!
Thanks Jim. Glad it was timely for you.
very usefull
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).
Write to array with rs.getrows
Excellent video, thank you very much for it
You're welcome.
This was awesome! Thanks!
I really don't understand how anyone could downvote content like this
Thanks Jillian
Can you make a video on how to read and write data from and to an SQL database through VBA?
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.
Mind blowing👍👍. Thanks Paul
Glad you enjoyed it
I never get the source code;
Am I doing something wrong?
Send me your email address and I will check it. Paul@ExcelMacroMastery
Thank you Paul, it has been a long time ⌛ clear explanation on the error handling 👍
Happy to help Frik.
Great video. Thank you Paul!!!
Glad you liked it Michal!
Brilliant explanation!
With late binding is posible to use ADO with Excel for Mac?
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.
I actually use late binding for excel.
@@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?
Oops I didn't read the part where he wrote Mac
Easy to understand thanks for sharing
Glad it was helpful!
Brilliant stuff! Thanks
Glad you enjoyed it John!
This is fantastic! Thank you!
You're very welcome Kurtis!
Thanks Paul.
You're welcome Vincent.
Thank you Paul
You're welcome
Thanks Paul!
You're welcome Oliver.
Thank god for Power Query I say 🤣🤣🤣
Hi Brian, do you think the code is too complex?
@@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
@@briandennehy6380 it’s no more complex than the M code in the advanced editor in Power Query
@@oliveroshea5765 True but with Power Query you can use the GUI to connect to a database without the need to know VBA.
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.