|
|
|
| Title | Make a lookup cache from a DAO database |
|---|
| Description | This example shows how to make a lookup cache from a DAO database in Visual Basic 6. |
|---|
| Keywords | DAO, cache, lookup, lookup table, table |
|---|
| Categories | Database |
|---|
|
|
|
The LookupCache class does all the work. It's LoadValues method takes as parameters an open DAO database connection, the name of the lookup table, the name of the field to use in looking up values, and the name of the field that contains the value to return. It selects all of thue records from the table and saves their values in a collection, using the lookup names as keys.
The class's GetValue method simply looks up a key in the collection and returns the value.
|
|
Private m_Values As Collection
' Load the values in the lookup table.
Public Sub LoadValues(ByVal db As DAO.Database, ByVal _
table_name As String, ByVal name_field As String, ByVal _
value_field As String)
Dim rs As DAO.Recordset
' Get the values.
Set rs = db.OpenRecordset( _
"SELECT " & name_field & ", " & value_field & _
" FROM " & table_name, dbOpenSnapshot)
Set m_Values = New Collection
Do Until rs.EOF
' Save this value using the name as its key.
m_Values.Add rs.Fields(1).Value, rs.Fields(0).Value
rs.MoveNext
Loop
rs.Close
End Sub
' Return the value for this name.
Public Function GetValue(ByVal name_value As String) As _
Variant
GetValue = m_Values(name_value)
End Function
|
|
|
The main program uses the LookupCache class like this:
|
|
Private Sub Form_Load()
Dim db_path As String
Dim db As DAO.Database
Dim name_to_abbrev As LookupCache
Dim abbrev_to_name As LookupCache
Dim txt As String
' Open the database.
db_path = App.Path
If Right$(db_path, 1) <> "\" Then db_path = db_path & _
"\"
db_path = db_path & "test.mdb"
Set db = DAO.OpenDatabase(db_path)
' Make the lookup caches.
Set name_to_abbrev = New LookupCache
name_to_abbrev.LoadValues db, "States", "StateName", _
"Abbrev"
Set abbrev_to_name = New LookupCache
abbrev_to_name.LoadValues db, "States", "Abbrev", _
"StateName"
' Close the database.
db.Close
txt = txt & "CA <--> " & abbrev_to_name.GetValue("CA") _
& vbCrLf
txt = txt & "CO <--> " & abbrev_to_name.GetValue("CO") _
& vbCrLf
txt = txt & "NV <--> " & abbrev_to_name.GetValue("NV") _
& vbCrLf
txt = txt & "MA <--> " & abbrev_to_name.GetValue("MA") _
& vbCrLf
txt = txt & "UT <--> " & abbrev_to_name.GetValue("UT") _
& vbCrLf
txt = txt & vbCrLf
txt = txt & "California <--> " & _
name_to_abbrev.GetValue("California") & vbCrLf
txt = txt & "Colorado <--> " & _
name_to_abbrev.GetValue("Colorado") & vbCrLf
txt = txt & "Nevada <--> " & _
name_to_abbrev.GetValue("Nevada") & vbCrLf
txt = txt & "Massachusetts <--> " & _
name_to_abbrev.GetValue("Massachusetts") & vbCrLf
txt = txt & "Utah <--> " & _
name_to_abbrev.GetValue("Utah") & vbCrLf
txtResults.Text = txt
End Sub
|
|
|
You could easily modify the GetValue method to use default values in case a specific key is missing in the collection.
|
| |
|
| |
|
|
