Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Visual Basic
Expertise: Intermediate
Aug 13, 1999



Building the Right Environment to Support AI, Machine Learning and Deep Learning

A Generic Routine to Fill Unbound Lists

A common need in database processing is retrieving a list of the values of a particular column field for every record in a table. This function takes arguments for a database name, table name, field name, and optional SQL criteria string, and it returns a collection that contains the list of all row values for the specified column field:

Public Function GetColumnData(ByVal DbName As String, _
	ByVal TableName As String, ByVal DataFieldName As _
	String, Optional WhereCriteria As String) As Collection

	Dim WS As Workspace
	Dim DB As Database
	Dim RS As Recordset
	Dim SQLQuery As String
	Dim Results As Collection
	Dim FieldValue As String
	Dim Count As Integer

	Set WS = CreateWorkspace("", "admin", "", dbUseJet)
	Set DB = WS.OpenDatabase(DbName)
	SQLQuery = "SELECT " & TableName & _
		"." & DataFieldName & " FROM " & TableName
	If WhereCriteria <> "" Then _
		SQLQuery = SQLQuery & " WHERE " & WhereCriteria
	Set Results = New Collection
	Set RS = DB.OpenRecordset(SQLQuery, dbOpenForwardOnly)
	If Not RS Is Nothing Then
		Count = 0
		'this count will be a unique key
		'in the collection
		Do While Not RS.EOF
			FieldValue = RS.Fields(DataFieldName)
			Results.Add FieldValue, CStr(Count)
			Count = Count + 1
		Set RS = Nothing
End If
	Set DB = Nothing
	Set WS = Nothing
	Set GetColumnData = Results
	Set Results = Nothing

End Function

This procedure is great for filling unbound lists and combo boxes or for driving other database processing based on the returned list. Here's a simple example:

' get a list of Social Security numbers 
' for all employees over age 65
Dim lst As Collection
	Dim i As Integer

	Set lst = GetColumnData("employee.mdb", _
		"tblEmployees", "SSNum", "Age>65")

	If Not lst Is Nothing Then
		For i = 1 To lst.Count
			'do something with lst(i)
		Next i
		Set lst = Nothing
	End If

In this code, efficiency is traded for ease of use. The procedure opens a connection to the database each time it's called, which is an expensive operation, especially if used inside a loop. As an alternative, you could pass an optional database object. Another efficiency enhancement would be to declare the GetColumnData function as Recordset. After the recordset is open, simply Set GetColumnData = RS. By doing this, you can dispense with the collection altogether. It would also save an iteration through the recordset/collection within the GetColumnData function to assign it to the collection.

Also, note that duplicate values are allowed in the returned collection. I left out error checking intentionally to keep the code as short as possible.

Allen Broadman
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date