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.

Explanation

A 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!

Code

Public 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

RS1.Close
QD1.Close
DB1.Close

End Sub

Download excel file! database-connection-retrieve-data-from-database-querying-data-into-excel-using-VBA-DAO.xls

Comments (4)

Simple and easy. Thanks for the help!!

I use the described method on this page, DAO.

can you tell me query which u r using to retrieve data??

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

Add your comment

yvComment v.CommentVersion