This article includes a Visual Basic for Applications sample macro that
fills a ListBox control with values from an external database using the DAO
3.5 Object Library reference.
Microsoft provides examples of Visual Basic for Applications procedures
for illustration only, without warranty either expressed or implied,
including, but not limited to the implied warranties of merchantability
and/or fitness for a particular purpose. The Visual Basic procedures in
this article are provided 'as is' and Microsoft does not guarantee that
they can be used in all situations. While Microsoft support professionals can
help explain the functionality of a particular macro, they will not modify
these examples to provide added functionality, nor will they help you
construct macros to meet your specific needs. If you have limited
programming experience, you may want to consult one of the Microsoft
Solution Providers. Solution Providers offer a wide range of fee-based
services, including creating custom macros. For more information about
Microsoft Solution Providers, call Microsoft Customer Information Service
at (800) 426-9400.
This Visual Basic for Applications example uses three routines for future
expansion and portability. To use the procedures, place them in the
UserForm module. These procedures are as follows:
- The UserForm Initialize procedure sets the database values to use, such
as the database path and name values, and sets the table and field
values in the selected table.
- The DBConnect procedure makes the actual connections to the database,
the table, and the fields that you want to use.
- The PopulateListBoxData procedure populates the list box.
Referencing the DAO 3.5 Object Library
For this example to work, you must add the DAO 3.5 Object Library
reference.
To add the DAO 3.5 Object Library reference, use the following steps:
- In the Visual Basic Editor, click References on the Tools menu to
display the References dialog box.
The References dialog box displays all object libraries and projects
that are registered with the operating system.
- Scroll through the list for the library you want to reference. If the
application is not listed, click Browse to search for object library
(.olb and .tlb) files or executable (.exe and .dll) files. References
with check boxes that are selected are used by the project; references
with check boxes that aren't selected are not used.
- Click to select the check box for the reference in the Available
References list and click OK.
Typing the Code in the UserForm
Type the following code in the General Declarations section of the
UserForm:
Public oDataBase As Database
Public oWorkSpace As Workspace
Public oRecordSet As Recordset
Public strDB As String
Public strDBTable As String
Public strDBField As String
Sample Macro
The following example macro uses the Microsoft Access 97 Northwind.mdb
database, which is located in the default Office folder in the Samples
folder:
Private Sub UserForm_Initialize()
' ********************************************
' Form initialization.
' Enter all values here for Database, Table
' and Field to use.
' ********************************************
' DataBase to Use.
strDB = Options.DefaultFilePath(wdProgramPath) & _
"\Samples\Northwind.mdb"
' Database Table to Use.
strDBTable = "Customers"
' Database Field to Use.
strDBField = "CompanyName"
DBConnect
PopulateListBoxData
End Sub
Sub DBConnect()
' ********************************************
' Initializes Jet workspace and opens a database
' ********************************************
' Establish database WorkSpace.
Set oWorkSpace = CreateWorkspace(Name:="JetWorkspace", _
UserName:="admin", Password:="", UseType:=dbUseJet)
' Open the database.
Set oDataBase = OpenDatabase(strDB)
' Set the record set to the specified table.
Set oRecordSet = oDataBase.OpenRecordset(strDBTable)
End Sub
Sub PopulateListBoxData()
' Fill list box with data from selected field in table.
' This example populates the list box with values from
' the CompanyName field of the Customers table.
oRecordSet.MoveFirst
Do Until oRecordSet.EOF
ListBox1.AddItem oRecordSet.Fields(strDBField)
oRecordSet.MoveNext ' Move to next record in table.
Loop
End Sub
NOTE: To use the example, change the values to the full database path and
name and change the values for the table and fields that you want to use.
For additional information, please see the following article in the
Microsoft Knowledge Base:
164815Â
(http://kbalertz.com/Feedback.aspx?kbNumber=164815/EN-US/
)
How To Create And Display A Custom Dialog Box