reateDataLB

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cn.ConnectionString = "Provider=MSDASQL.1; _
Persist Security Info=False; _
Data Source=DSG_Input"

cn.Open

'*SQL Statement to extract all customers from the database


sql = "Select First_Name, Cust_ID from Customer _ Order by First_Name"
Set rs = cn.Execute(sql)

'*Populates the listbox**


With List1
Do While Not rs.EOF
.AddItem rs("First_Last")
rs.MoveNext
Loop
End With

'You now have a listbox containing the records from your

'database You will create an array that is dynamic to your

'recordset.This will keep track of the primary key as a

'boundColumn would in a datalist box. This is for the

'purpose of relational databases. You will create the array

'the same size as the listIndex count (number of records in

'listbox).


rs.movefirst

ReDim array1(List1.ListCount) As String

'This will now populate the array whichis a

'mirror image as the listbox, but with the primary key.


For i = 0 To List1.ListCount - 1
array1(i) = rs("Cust_ID")
rs.MoveNext
Next i

'We have now completed the listbox. Youcan use this listbox

'the same way as you would a datalist box. The following code

'will explain how. To access the primary key relating to

'each record in the list, put the following code in the

'listbox "Click()" event. This explains how to access the

'primary key stored in the array.


'list1.listIndex explains with record in the list was

'clicked on. You use this to find where in the array the

'primary key is stored.


Private Sub List1_Click()
Dim Primary_1 as String
Primary_1 = array1(list1.listIndex)
MsgBox Primary_1
End Sub

'Conclusion*

'Although this isn't as convenient as setting up

'a bound datalist control, you will find it

'will speed up things when using a large database file.



Put the following code in any function.
This code opens database connection/recordset.
I have connected via datasource