DataWright Information Services

Consulting and Resources for Excel and Access

Use a validation list to import data from an Access query

This is Part 5 of 7 in a tutorial on using code to integrate Excel and Access.

In the sample file, Region has a validation list in cell K1. Selecting a value from the list triggers some event code, which in turn drives the code to download data for all countries from that region.

Note:

  1. Because all of the code for this tutorial exists in a single module, I have placed a constant at the top of the module that defines the name of the database. This removes the need to keep redefining it for each procedure, and means that you only need to change one place in the code to refer to a different database.
  2. To use this code in your own file you will need to set the appropriate references. In the code environment go to Tools > References and select Microsoft ActiveX Data Objects 2.x Library (the highest version available on your system).

The constant is shown below. It needs to go before the first Sub procedure in the module:

Const TARGET_DB = "DB_test1.mdb"

To view the event code, right-click the worksheet tab and View Code. The code looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Cells.Count > 1 Then Exit Sub
   Application.EnableEvents = False
   If Target = Range("K1") Then Call DownloadRegion
   Application.EnableEvents = True
End Sub

Let's dissect the code. 

Private Sub Worksheet_Change(ByVal Target As Range)

When you make a change on a worksheet, Excel can respond to this by triggering other actions. It does that by using code in the Worksheet_Change procedure, and referencing the cell(s) that changed. The changing cells are referred to as the Target. 

   If Target.Cells.Count > 1 Then Exit Sub
   Application.EnableEvents = False
   If Target = Range("K1") Then Call DownloadRegion
   Application.EnableEvents = True

When you use event code on a worksheet you stand a chance of slowing your application to a crawl, because every change on the worksheet will trigger the event and you may find yourself stuck in a loop. This code does three things to avoid the problem.

  1. First, if more than one cell is selected when the change happens, the first line causes the code to terminate.
  2. Second, we only want the code to run if K1 is changed so the third line attaches the code to that cell only.
  3. Third, because the code will change multiple rows of data, we don't want the code firing for every cell that is updated. We wrap the call to DownloadRegion in two lines of code; the first temporarily turns off events in the application, and the second switches them back on once DownloadRegion has finished running.

Note: The code will still run properly without those two lines. It will just be slower, because of all the additional Worksheet_Change events that are triggered. You can test this out by commenting out the two Application.EnableEvents lines and comparing the difference.

The second part of this example is the code that downloads the data. It goes in a standard module but is called from the Worksheet_Change event. The code for DownloadRegion is shown below:

Sub DownloadRegion()
   Dim cnn As ADODB.Connection
   Dim rst As ADODB.Recordset
   Dim fld As ADODB.Field
   Dim MyConn
   Dim i As Long
   Dim ShDest As Worksheet
   Dim sSQL As String

   Set ShDest = Sheets("Region")

   sSQL = "SELECT * FROM tblPopulation WHERE Region ='" & ShDest.Range("K1").Value & "'"

   Set cnn = New ADODB.Connection
   MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB

   With cnn
     .Provider = "Microsoft.Jet.OLEDB.4.0"
     .Open MyConn
   End With

   Set rst = New ADODB.Recordset
   rst.CursorLocation = adUseServer
   rst.Open Source:=sSQL, _
            ActiveConnection:=cnn, _
            CursorType:=adOpenForwardOnly, _
            LockType:=adLockOptimistic, _
            Options:=adCmdText

   'clear existing data on the sheet
   ShDest.Activate
   Range("A1").CurrentRegion.Offset(1, 0).ClearContents

   'create field headers
   i = 0
   With Range("A1")
   For Each fld In rst.Fields
     .Offset(0, i).Value = fld.Name
     i = i+ 1
   Next fld
   End With

   'transfer data to Excel
   Range("A2").CopyFromRecordset rst

   ' Close the connection
   rst.Close
   cnn.Close
   Set rst = Nothing
   Set cnn = Nothing
End Sub

If you compare this code to the sample in part 4 of this tutorial, you will see that they are very similar. The only difference is in how the recordset is defined. Click here to see a summary of some crucial recordset differences.

<<Previous: Part 4 Next: Part 6>>