Database connection, retrieve data from database, querying data into excel using VBA DAO
The VBA code makes a database connection and retrieves data by calling and giving input to existing database query.
ExplanationA database connection is established through the VBA Macro and a query that is all ready created and stored in the database is executed. The query is also created to retrieve data of two different parameters. The parameters can be excluded in case of retrieving all data from a query without specific filters. This type of database connection can be established to all major business systems and can save time and money by eliminating time consuming manual data transfer. For example a days manual work can easily be done automatically using VBA macro automation. It is important to perform analyzes of your own work continuously in order to be efficient.
In order to make the VBA code work the following reference needs to be enabled “Microsoft DAO 3.6 Object Library”.
The entire VBA program can be downloaded in an excel file at the end of this web page or just copy and paste the code directly from the page!
CodePublic Sub database_connection_retrieve_data_from_database_querying_data_into_excel_using_VBA_DAO()
Dim Database_RetrieveData_VBA_Excel As String
Dim Query_RetrieveData_VBA_Excel As String
Dim Parameter1_RetrieveData_VBA_Excel As String
Dim Parameter2_RetrieveData_VBA_Excel As String
Dim DAO_Connection_RetrieveData_VBA_Excel As String
Database_RetrieveData_VBA_Excel = Range("G3").Value
Query_RetrieveData_VBA_Excel = Range("G4").Value
Parameter1_RetrieveData_VBA_Excel = Range("G5").Value
Parameter2_RetrieveData_VBA_Excel = Range("G6").Value
DAO_Connection_RetrieveData_VBA_Excel = 0
DB1 = DBEngine.OpenDatabase(Database_RetrieveData_VBA_Excel)
Set QD1 = DB1.QueryDefs(Query_RetrieveData_VBA_Excel)
QD1.Parameters("p1") = Parameter1_RetrieveData_VBA_Excel
QD1.Parameters("p2") = Parameter2_RetrieveData_VBA_Excel
Set RS1 = QD1.OpenRecordset(dbOpenSnapshot, dbReadOnly)
Range("b11").Offset(0, 0).CopyFromRecordset RS1
CommentsA good one. Both DAO and ADO can give a stable connection to the database. You said it's just a matter of taste and like. Me? I prefer the ADO. :) Thanks for visiting my blog.
Comment made by: Joel Protusada , 2009-06-21 14:33:10
can you tell me query which u r using to retrieve data??
Comment made by: Amit , 2009-08-28 05:25:26
I use the described method on this page, DAO.
Comment made by: Answer2 , 2009-09-13 06:21:14
Simple and easy. Thanks for the help!!
Comment made by: James MacDougall , 2010-03-26 14:07:31
I'm still confuse beause its file xls doesn't work. can you attach mdb file to?
Comment made by: ME , 2010-12-29 17:23:08
can u please give me MDB database file so that i can run it
Comment made by: resham , 2011-07-13 12:18:28
Please help with code How to retreive data from excel sheet to user form textbox,combobox. My email Id: ranjit1001Agmail.com
Comment made by: Ranjit Nayak , 2011-08-18 17:21:02
Can anyone post some sample mdb file that can run on the code. And can we run on the Excel 10 as well. Any examples excel codes and access db please.
Comment made by: tangy , 2011-11-25 02:22:13
Comment made by: Password , 2012-01-16 03:35:54
I am new to Macros, i would like to connect to Oracle DB and do a simple "Select * from a table" and then export it to the excel sheet. I would require the code to connect from Macro as well as the syntax to fetch the data
Comment made by: Saud , 2012-11-01 03:49:11
cn u please tell me how can i fetch data in spreadsheet through vba
Comment made by: nivi , 2013-02-08 09:54:20
Dear Sir, I use macros and I want to deactivate all button besides Run. VBA coding don't show for anyone, I only see that coding. please suggest me soon. I shall be highly obliged to you, thank you, My Email ID: ranjit_guru2010Ayahoo.in
Comment made by: ranjit singh , 2013-02-13 06:26:25
I want to develope a simple tol in vba excel using data from excel sheet only...pls help...email-dpdebanjanAgmail.com
Comment made by: debanjan , 2013-03-28 07:26:13
Comment made by: Johhni , 2013-12-02 11:27:25
Comment made by: Johhni , 2013-12-02 11:29:01
Comment made by: Johhni , 2013-12-02 11:29:59
Comment made by: Muhammad , 2014-03-17 11:26:01
Hi, Would you advise I make and delete the connection on every call, or just once on initiation and close it down when the user exit the form or application - assuming I make around 100 calls per session...
Comment made by: Rob Bane , 2014-07-05 02:32:04
how to connect database in vba
Comment made by: priya , 2014-10-30 10:33:40
Comment made by: altaf , 2014-11-20 10:42:33