This snippet of code shows how to connect to a database using a Data Source Name (DSN), and a reference to Microsoft ActiveX Data Objects (ADO).
To make the reference to ADO, while in the IDE, click Project, References. In the References window, select the version of MS ADO that you wish to use. In the picture below, I’ve selected version 6.1.
Dim rs as ADODB.Recordset
Dim Con as ADODB.Connection
Dim ssql as String
Const strCon ="DSN=Contacts;Description=Contacts;SERVER=ServerName;UID=sa;Password=;"
Private Sub combo1_DropDown()
Set Con = New ADODB.Connection
Set rs = New ADODB.Recordset
Con.Open strCon
'sql statement to select items on the drop down list
ssql = "Select LastName From Contacts"
rs.Open ssql, Con
Do Until rs.EOF
combo1.AddItem rs("LastName") 'Adds lastnames to dropdown list
rs.MoveNext
Loop
'Close connection and the recordset
rs.Close
Set rs = Nothing
Con.Close
Set Con = Nothing
End Sub