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
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
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
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
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.
Hi Visha,
Please help on this “interact with website and get data and put it in Excel”
Hi Manohar, You can refer this article how to interact with HTML pages using Excel VBA to interact with Webpages
Pls suggest any good book or online literature on SQL / Access programming using excel vba?
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
Hi Vishwa
I want to VBA code, to write the data from excel sheet to SQL database
Regards
Iyer Kartik
Hello
How to connect to Access Database with ADO Connection String
and Add,Edit,Delete …Record
tank you
Hello Please help me
How to connect to Access Database with ADO Connection String
And Add,Delete,Edit … Record
Tank you
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
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.)
Hello, how do I pass the connection(connection object) to a form or a button.
How to connect to Access 2013/2019 ?
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.
“Dim conn As New Connection” is not working on my system.
Can any one suggest another option. Error showing “User-Defined Type Not Defined”
Please help me, I want to create sub connect use for all sub in workbook by VBA.
Thanks,