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:

cover3d_0-89071700_1484285537__1_

Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!