How to connect to Access Database – ADO Connection String



Database | Excel Macro | Excel Macro Tutorial

Using Excel Macros (VBA) you can connect to any Databases like SQL, Oracle or Access DB. In this Article you will learn, how you can do a connection with an Access Database. Access Database connection string is different for Access 2003 (*.mdb) and Access 2007/2010 (*.accdb) because Drivers are different for both the databases.

For Access 2003 Database the Provider is: Provider=Microsoft.Jet.OLEDB.4.0.
For Access 2007/2010 the Provider is Provider=Microsoft.ACE.OLEDB.12.0

Before running the below code, you need to Add reference for ADO DB Connection. Follow below steps to Add Reference:

How to add References in excel

1. Go to VB Editor Screen (Alt+F11)
2. Tools –> References…
3. From the List of Available References Select “Microsoft ActiveX Data Objects 2.0 Library” You can select 2.0 Version or any other higher version of this reference.
4. Click OK

Connection with Access 2003 Database


Sub ADO_Conn()

Dim conn As New Connection
Dim rs As New Recordset
   
    strcon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=E:\Student.accdb;" & _
    "User Id=admin;Password="
    
    conn.Open (strcon)
  
    qry = "SELECT * FROM students"
    rs.Open qry, conn, adOpenKeyset
    
    rs.Close
    conn.Close
    
End Sub

Connection with Access 2007/2010 Database


Sub ADO_Conn()

Dim conn As New Connection
Dim rs As New Recordset
   
    strcon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=E:\Student.accdb;" & _
    "User Id=admin;Password="
    
    conn.Open (strcon)
  
    qry = "SELECT * FROM students"
    rs.Open qry, conn, adOpenKeyset
    
    rs.Close
    conn.Close
    
End Sub

Read this Also:

Buy a coffee for the author

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Hurray..Lets Go

Convert PDF Tables to Excel with VBA | Step-by-Step Guide

Dec 4, 2024

Convert PDF Tables to Excel with VBA | Step-by-Step Guide How to Convert PDF Tables to Excel Using Excel VBA (Macros) Extracting data from PDF tables into Excel can be tedious, but with Excel VBA (macros), you can automate this process and save time. This guide will...

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

Feb 9, 2024

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA

Feb 3, 2024

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

Know more

Parminder on July 14, 2012 at 4:53 am

hi Vishwamitra Mishra could please tell me. i want to automate process of data we usualy copy data from website and pate in excel sheet then through phone call we write them down to do work .i want i should me automated with sms app it will reduce our work ..thanks
Reply
- Vishwamitra Mishra on July 14, 2012 at 11:41 am
  
  Hi,
  Thanks for writing to me.
  As of now i do not know how to interact with SMS app through Excel.
  
  If you want to know how to interact with website and get data and put it in Excel, I can help you on this.
  
  Thanks,
  Vish
  Reply
  - poorna on March 16, 2013 at 5:21 am
    
    Hi can u please mail me any macros related materials like connecting to db and retrieving back and put data in excel.Actually i need to use them in my project.
    Reply
  - Manohar on October 3, 2016 at 6:06 am
    
    Hi Visha,
    Please help on this “interact with website and get data and put it in Excel”
    Reply
    - Vishwamitra Mishra on October 12, 2016 at 4:57 am
      
      Hi Manohar, You can refer this article how to interact with HTML pages using Excel VBA to interact with Webpages
      Reply
Kannan on August 21, 2012 at 7:14 am

Pls suggest any good book or online literature on SQL / Access programming using excel vba?
Reply
Beer Singh on July 5, 2014 at 2:05 am

Hi Vishwamitra,
Lear Excel Macro is a beat website. It helps me alot & male my life easier. Thanks a lot for making this website..
I have few queries & queations.
I havn’t receive my E-book. Kindly help me to get that.
You make best excel example files which helps alot.
Few of files asking for Password. It will be great If I could get all macro example
with password.
I also download your VBA Code file, but dont know how to use.
Kindly help
Reply
Kartik on April 12, 2015 at 3:06 pm

Hi Vishwa

I want to VBA code, to write the data from excel sheet to SQL database

Regards

Iyer Kartik
Reply
li on December 1, 2015 at 11:03 am

Hello
How to connect to Access Database with ADO Connection String
and Add,Edit,Delete …Record
tank you
Reply
ali on December 2, 2015 at 4:34 pm

Hello Please help me
How to connect to Access Database with ADO Connection String
And Add,Delete,Edit … Record
Tank you
Reply
mouhamad on July 7, 2016 at 1:30 pm

i use vba and i connect that to Ms Access 2013 when i put password for Ms access i can’t acces to MS access from Vba thanks
Reply
- kartina on November 8, 2016 at 6:26 am
  
  hye…i’m struggling with the same issue.. Have you obtained any solution?
  
  My connection is fine without the password.. When I password protect the DB, i keep on getting error Error -2147217843 (Cannot start your application.The workgroup information file is missing or open exclusively by another User.)
  Reply
Samuel on November 27, 2018 at 6:40 am

Hello, how do I pass the connection(connection object) to a form or a button.
Reply
naboco on November 27, 2018 at 4:32 pm

How to connect to Access 2013/2019 ?
Reply
Amit on April 18, 2019 at 6:51 am

I am using access frontend forms for enduser and backend tables for storing data. My backend is password protected, how can i work in frontend with secure database.
Reply
Hasim Ajmi on October 3, 2019 at 10:23 am

“Dim conn As New Connection” is not working on my system.

Can any one suggest another option. Error showing “User-Defined Type Not Defined”
Reply
Peter on March 11, 2020 at 1:02 pm

Please help me, I want to create sub connect use for all sub in workbook by VBA.

Thanks,
Reply