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

  • Thread starter Thread starter axwack
  • Start date Start date
A

axwack

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?
 
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?
Click to expand...

What is your current code like?
Are you using DAO / ADO?
What is your datasource? ODBC?
 
What is your current code like?
Are you using DAO / ADO?
What is your datasource? ODBC?
Click to expand...

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 Worksheet
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
 
'Now open the connection.
cnPubs.Open strConn

' Create a recordset object.
Set rsPubs = New ADODB.Recordset
'Clear Worksheet
Click to expand...

I'm not looking closely at your code but I can see the gist of your
technique.

1. ADO and DAO are in general constant connection models. So if you
open and close the connection often, that means that the database
server will not be "locked" waiting for Excel - however, you suffer
some slowdown everytime the connection is open and closed. You have to
balance the pros and cons of keeping the connection open for a long
time or not.

2. If you want to hold the connection open, you can declare a global
module and wrap the connection variable in the global module - so you
don't open and close the connection every time. You can pass declare
that cnPubs variable as a global variable that does not lose scope
until you close the workbook. You can pass the cnPubs between
procedures.

3. You can establish a cache of data. If the data is not changing
every second, when you first retrieve the recordset, that recordset
contents may not change the next time you use it. You could cache that
recordset by using a disconnected ADO recordset. Or if the recordset
is small and static, you can manually cache the data by populating the
data onto a hidden worksheet

4. A very geeky way is not to use VBA and ADODB but .NET (whether
wrapped into a dll or not) and ADO.NET which is more connectionless.

Hope this Helps

Ananda
 
Back
Top