Use a validation list to import data from an Access query
-
Download the
.mdb sample file (68,765 bytes) -
Download the .accdb sample file (71,432 bytes) - If you can’t create the database,
download this one (8558
bytes)
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:
- 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. - 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.
- First, if more than one cell is selected when the change
happens, the first line causes the code to terminate. - Second, we only want the code to run if K1 is changed so the
third line attaches the code to that cell only. - 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.
