How to connect to Access Database – ADO Connection String

.

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

Adsense

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.

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

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
How to Declare a Public Variable in VBA

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

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

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 (=).

You May Also Like…

17 Comments

  1. Parminder

    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

      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

        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

        Hi Visha,
        Please help on this “interact with website and get data and put it in Excel”

        Reply
  2. Kannan

    Pls suggest any good book or online literature on SQL / Access programming using excel vba?

    Reply
  3. Beer Singh

    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
  4. Kartik

    Hi Vishwa

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

    Regards

    Iyer Kartik

    Reply
  5. li

    Hello
    How to connect to Access Database with ADO Connection String
    and Add,Edit,Delete …Record
    tank you

    Reply
  6. ali

    Hello Please help me
    How to connect to Access Database with ADO Connection String
    And Add,Delete,Edit … Record
    Tank you

    Reply
  7. mouhamad

    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

      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
  8. Samuel

    Hello, how do I pass the connection(connection object) to a form or a button.

    Reply
  9. naboco

    How to connect to Access 2013/2019 ?

    Reply
  10. Amit

    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
  11. Hasim Ajmi

    “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
  12. Peter

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

    Thanks,

    Reply

Trackbacks/Pingbacks

  1. Learn Excel Macro Oracle Connection String in VBA - [...] How to Connect to Access Database using Excel Macros [...]

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest