Re: Reusable Database connection for Combo Box, list box and other query

On Jul 22, 9:26 am, AnandaSim <Ananda...@xxxxxxxxx> wrote:
On Jul 22, 12:48 am, axwack <axw...@xxxxxxxxx> wrote:

I have an app that is database driven and there are many times where I
need to make queries out to the database to populate Combo Boxes and
List boxes.

In my current code, I have to explicitly set the code to populate the
combo box based on the result set. Is there a way where I can get a
handle to a result set, pass that to a function that parses the
resultset and populates the combo box? I know in Java and other OO
languages you have an abstraction to the database where you can pass
around objects for manipulation but in Excel VB I'm not sure how one
would do this. Any idea?

What is your current code like?
Are you using DAO / ADO?
What is your datasource? ODBC?

Thanks for responding. Here is code to populate one of my Combo boxes.
I have one more and I have to repeat my Connection code each time:

Private Sub Model_Account_CB_populate()

' Create a connection object.
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection
Dim rsPubs As ADODB.Recordset

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the Pubs database on the local server.
strConn = strConn & "DATA SOURCE=" &
Worksheets("Parameters").Range("c5") & ";DATABASE=" &
Worksheets("Parameters").Range("c6") & ";"
strConn = strConn & "UID=" & Worksheets("Parameters").Range("c7")
& ";PWD=" & Worksheets("Parameters").Range("c8") & ";"


'Now open the connection.
cnPubs.Open strConn

' Create a recordset object.
Set rsPubs = New ADODB.Recordset
'Clear Work***
Worksheets("Data").Cells.Clear
With rsPubs
' Assign the Connection object.
.ActiveConnection = cnPubs
' Extract the required records.
.Open "Select acct_name from cs_fund"
Do Until rsPubs.EOF
With Model_Account_CB
.AddItem rsPubs!acct_name
End With
.MoveNext
Loop
End With
cnPubs.Close
Set rsPubs = Nothing
End Sub

.