The image above clearly explains what my macro here will do. I’ll first fill a combo box with few records on button click. Next, when I select a value from the combo box, it will show more details in the following rows.
Create a Table in SQL Server
You’ll need SQL Server (any version) installed in your computer to work with the examples that I am sharing here.
Next, create a table named dbo.Birds and add few rows in it.
The Macro
I’ll add a few ActiveX controls like a button and combo box on my sheet. It is like a form.
Add a Reference of ActiveX Data Object
Before you write any code, you will need to add a Reference of ActiveX Data Object or ADO. This will provide the necessary classes, properties and methods to connect database objects.
To add reference to your project, click the Tools menu on the top and choose References… option. In the references box, find Microsoft ActiveX Data Objects 6.1 Library (or any current version). Click OK.
Add a Module
From the Project Explorer, right click the project and insert a module.
The Module has a procedure to connect to the database using ADO classes and properties. It also has few Public variables.
Option Explicit Public myConn As New ADODB.Connection Public rs As New ADODB.Recordset Public sQuery As String ' Set the connection. Sub setConn() If myConn.State = adStateOpen Then myConn.Close End If ' Define the connection string by provider driver and database details. Dim sConnString As String sConnString = "Driver={SQL Server};Server=arun;Database=your_database_name;Uid=sa;pwd=any_password;Connect Timeout=500;" myConn.ConnectionString = sConnString myConn.Open ' Now, open the connection. End Sub
See the connection string, where I have defined the Driver and other SQL Server properties like the user name and password.
Procedures to Extract or Pull data from SQL Server
Now, from the Project Explorer, double click the Sheet1 and write these codes.
Option Explicit Private Sub cmdFillCombo_Click() setConn ' Set the connection first. Sheet1.cmbBirdType.Clear ' Clear the combo box. sQuery = "SELECT DISTINCT TypeOfBird from dbo.Birds" If rs.State = adStateOpen Then rs.Close End If ' Execute query using recordset object. rs.CursorLocation = adUseClient rs.Open sQuery, myConn, adOpenKeyset, adLockOptimistic ' Fill combo box with records. If rs.RecordCount > 0 Then Do While Not rs.EOF Sheet1.cmbBirdType.AddItem rs.Fields(0).Value rs.MoveNext Loop Else MsgBox "No data found.", vbCritical + vbOKOnly Exit Sub End If End Sub ' Show other details on combo box change event. Private Sub cmbBirdType_Change() If Trim(cmbBirdType.Text) <> "" Then setConn ' Set connection to the database. ' SQL query to fetch details about Birds for the selected Bird type. sQuery = "SELECT * FROM dbo.Birds WHERE " & _ "TypeOfBird = '" & cmbBirdType.Text & "' " & _ "ORDER BY BirdName" If rs.State = adStateOpen Then rs.Close End If ' Execute query using recordset object. rs.CursorLocation = adUseClient rs.Open sQuery, myConn, adOpenKeyset, adLockOptimistic Dim iCnt As Integer iCnt = 11 Sheet1.Range("A11:B20").Clear ' OR, you can use.. ' Worksheets("sheet1").Range("A11:B20").Clear ' Finally, show the details. If rs.RecordCount > 0 Then Do While Not rs.EOF Cells(iCnt, 1) = rs.Fields("BirdName").Value Cells(iCnt, 2) = rs.Fields("ScientificName").Value rs.MoveNext iCnt = iCnt + 1 Loop End If End If End Sub
The first procedure or event is the click event of an ActiveX button control. It will fill the combo box with distinct type of Birds, extracted from the Birds table.
The second event is the combo box change event Private Sub cmbBirdType_Change(). When you select a value from the combo box, it will trigger the change event, which will again connect SQL Server to fetch related data from the table and show the data in a tabular format.
The macro above just shows how you can connect to an SQL Server database and pull or fetch data from a table. Once the connection is made properly, you can execute other queries, like insert and update to manipulate data in the table.
Thank for reading :-).