How to use ADO and VBA to Read from a Database

👉 Ready to master VBA?
(Note: If the download page doesn't work then make sure to turn off any popup blockers)

How to use ADO and VBA to Read from a Database

In this video I show you how to read from a database using ADO(ActiveX Data Objects) with VBA. We will look at how to retrieve the data to a RecordSet using and query and then how to write that data to a worksheet. We'll also take a look at how to close the connection correctly and late binding.


#VBAADO #VBADATABASE #ExcelVBAADO #ExcelVBADatabase

Useful VBA Shortcut Keys
========================

Debugging:
Compile the code: Alt + D + C OR Alt + D + Enter
Run the code from the current sub: F5
Step into the code line by line: F8
Add a breakpoint to pause the code: F9(or click left margin)

Windows:
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
View the Properties Window: F4
Switch between Excel and the VBA Editor: Alt + F11
View the Project Explorer Window: Ctrl + R

Writing Code:
Search keyword under cursor: Ctrl + F3
Search the word last searched for: F3
Auto complete word: Ctrl + Space
Get the definition of the item under the cursor: Shift + F2
Go to the last cursor position: Ctrl + Shift + F2
Get the current region on a worksheet: Ctrl + Shift + 8(or Ctrl + *)
To move lines of code to the right(Indent): Tab
To move lines of code to the left(Outdent): Shift + Tab
Delete a Line: Ctrl + Y(note: this clears the clipboard)

Table of Contents:

00:00 - Introduction
00:30 - What is ADO?
00:50 - How to read from a Database
01:20 - Writing the connection code
02:41 - How to add the Query
04:45 - How to return the field headers
05:35 - Alternative way to use Recordset Open
06:35 - How to close the connection correctly
08:59 - How to use Late Binding

[Excelmacromastery]

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

[danielmadariagavaldes]

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

[ascarrunz]

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

[javiermolinaperez]

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 :)

[SimpleExcelVBA]

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

[philipmcdonnell]

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?

[efraintrejogomez]

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

[officetricks]

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

[victorgarmin]

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

[tigranhayrapetyan]

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!

[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.

[musabashraf]

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

[lalitthapa]

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

[jillianonthehudson]

This was awesome! Thanks!

I really don't understand how anyone could downvote content like this

[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!

[kamalam]

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.

[bigE]

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!!

[kuaatrichard]

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.

[frikduplessis]

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

[MrKhaldonalali]

Excellent video, thank you very much for it