I am using this code to do it:
Note you will need to add a reference to
(Your versions may differ)
Microsoft ActiveX Data Objects 2.8 Library
Option Explicit
Private Sub Workbook_Open() 'is run when workbook opens
Dim rsRef As ADODB.Recordset
Dim cnRef As ADODB.Connection
Dim sqlRef As String
'Instantiate objects
Set rsRef = New ADODB.Recordset
Set cnRef = New ADODB.Connection
cnRef.ConnectionString = "DSN={LOCAL-DSN};Uid={USER};Pwd={PASSWORD}"
cnRef.Open
'sqlRef = "Select * [OR Field List] FROM {DATA-TABLE}"
sqlRef = "Select * FROM {DATA-TABLE}" 'returns all fields and all records
sqlRef = "Select Field1, Field2 FROM {DATA-TABLE}" 'returns only Field1
and Field2 but ALL records
'Other options available with SQL - SELECT statement
rsRef.Open sqlRef, cnRef, adOpenStatic, adLockReadOnly
If rsRef.RecordCount > 0 Then
frmInput.cboInput.Clear 'clears combo box
Load frmInput
rsRef.MoveFirst 'goes to first record
Do While Not rsRef.EOF 'do while records remain
' Next line adds items to combo box
frmInput.cboInput.AddItem rsRef.Fields("Field1").Value
rsRef.MoveNext 'moves to next record
Loop 'continues the loop
while records remain
frmInput.Show vbModal 'displays my form
End If
End Sub
--
Wendell A. Clark, BS
Nurses Unlimited, Inc.
432-550-1700 x126
-------------------------------------
CONFIDENTIALITY NOTICE: This e-mail communication and any attachments may
contain confidential and privileged information for the use of the
designated recipients named above. If you are not the intended recipient,
please notify us by reply e-mail. You are hereby notified that you have
received this communication in error and that any review, disclosure,
dissemination, distribution or copying of it or its contents is prohibited.
If you have received this communication in error, please destroy all copies
of this communication and any attachments. Contact the sender if it
continues.