Automate database queries using VBA in Excel

Automating database queries using VBA in Excel can save time and reduce manual errors. The goal here is to query a database using SQL, execute the query, and retrieve the results into an Excel sheet.

Below is a detailed VBA code example that connects to a SQL database (like SQL Server, MySQL, or Access), queries it, and retrieves the data into an Excel sheet. We will use ADO (ActiveX Data Objects) for the database connection.

Example Steps

1. Connect to the database.
2. Execute an SQL query.
3. Retrieve the results into an Excel sheet.
4. Handle errors.

Detailed VBA Code

Sub QueryDatabase()
    ' Declare variables
    Dim Conn As Object ' Connection object to the database
    Dim Rs As Object ' Recordset object to store the query results
    Dim StrSQL As String ' SQL query to execute
    Dim Row As Long ' Variable to determine which row in Excel to paste the result
    Dim ConnString As String ' Connection string for the database
    ' Connection string (for SQL Server)
    ConnString = "Provider=SQLOLEDB;Data Source=ServerName;Initial Catalog=DatabaseName;User ID=Username;Password=Password"
    ' Create ADO Connection and Recordset objects
    Set Conn = CreateObject("ADODB.Connection")
    Set Rs = CreateObject("ADODB.Recordset")
    ' Attempt to connect to the database
    On Error GoTo ConnectionError
    Conn.Open ConnString
    On Error GoTo 0 ' Reset error handling
    ' Define the SQL query
    StrSQL = "SELECT * FROM MyTable" ' Replace with your own SQL query
    ' Execute the query
    Rs.Open StrSQL, Conn
    ' Check if the recordset contains any results
    If Not Rs.EOF Then
        ' Start pasting results from the second row (assuming row 1 contains headers)
        Row = 2
        ' Loop through the recordset and paste the results into Excel
        Do While Not Rs.EOF
            ' Fill each cell in the row
            For i = 0 To Rs.Fields.Count - 1
                Sheets("Sheet1").Cells(Row, i + 1).Value = Rs.Fields(i).Value
            Next i
            Row = Row + 1 ' Move to the next row
            Rs.MoveNext ' Move to the next record
        Loop
    Else
        MsgBox "No results found", vbInformation
    End If
    ' Close the Recordset and the Connection
    Rs.Close
    Conn.Close
    ' Release the objects
    Set Rs = Nothing
    Set Conn = Nothing
    MsgBox "Data retrieved successfully!", vbInformation
    Exit Sub
ConnectionError:
    MsgBox "Database connection error: " & Err.Description, vbCritical
    Set Rs = Nothing
    Set Conn = Nothing
End Sub

Explanation of the Code

1. Declared Variables:
   • Conn: The object representing the connection to the database.
   • Rs: The Recordset object that stores the query results.
   • StrSQL: The SQL query to execute.
   • Row: The row number in Excel where the results will be pasted.
   • ConnString: The connection string containing the information needed to connect to the database (server, database name, user credentials).
2. Connecting to the Database:
   • We use CreateObject(« ADODB.Connection ») to create a connection object.
   • The connection is established using Conn.Open ConnString, with the connection string containing the necessary details like server name, database name, username, and password.
3. Executing the SQL Query:
   • The SQL query is defined in the StrSQL variable. You can modify this query to fit your specific needs, such as filtering or selecting specific columns.
   • Rs.Open StrSQL, Conn executes the SQL query and stores the results in the Recordset (Rs).
4. Processing Results in Excel:
   • If results are returned (Recordset is not empty), the code loops through each record and pastes the values into Excel starting from row 2 (assuming row 1 contains headers).
   • The columns in the Recordset are iterated, and their values are pasted into the corresponding cells in the Excel sheet.
5. Closing the Connection:
   • After the data is retrieved, the Recordset and connection are closed using Rs.Close and Conn.Close.
   • The objects are released by setting them to Nothing to avoid memory leaks.
6. Error Handling:
   • A simple error handler (On Error GoTo ConnectionError) is used to capture any connection errors and display an error message if the connection fails.

Customization

• Connection String: Modify the connection string (ConnString) to match your database type. For example, for MySQL, you might use a MySQL OLE DB provider.
• SQL Query: Change the StrSQL variable to match the SQL query that you want to run. You can filter data, select specific columns, or join tables.
• Sheet Name: Ensure the sheet name (Sheet1) matches the sheet where you want to paste the results, or adjust it as needed.

Conclusion

This VBA code serves as a solid foundation to automate querying a database and importing the results into Excel. It can be customized further for advanced use cases, such as exporting data, scheduling queries, or applying additional filters to the query.