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:
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:
My name is Vishwamitra Mishra. Friends Call me Vishwa. This blog is authored by me. I am an Excel Geek. Well, this blog talks a lot about my passion in Excels & Macros so I’ll not talk about it :) I am very much passionate about traveling & quite recently discovered that I am a good photographer too..:P
AND GET A FREE!
E-BOOK FOR EXCEL VBA BEGINNERS
DON'T MISS ANY NEW ARTICLE !
Get your FREE! E-Book & Have Every New Article Delivered Straight To Your Email-Box