Making the database connection a function

[BitCounter]

I have several places in my application where I am connecting to the same database in order to do things like populate a combo-box with data from a particular table.&nbsp;&nbsp;Often this is on the same page/form.&nbsp;&nbsp;How can I create a public function that <b>all forms </b>can access with this connect code?&nbsp;&nbsp;I do have my database defined in the dataenvironment, but I am confused as how to exactly use this.&nbsp;&nbsp;On almost all of my forms I have an ADODC connection, but this connection always is to a different table than the one I need.&nbsp;&nbsp;That's why I am writing actual code in the form to make the other connections, I just don't want to make the connection 3 times when I could really only use it once, then execute a particular SQL to obtain the correct table.&nbsp;&nbsp;<br><br>I hope that this makes some sense!&nbsp;&nbsp;Any help would be greatly appreciated.

 

[Collin]

One way of many is to make a Sub/function that populates an array or a collection of a class defined as your recordset,at application start up, for example if you had a table that had all the states in it so that you could load a ComboBox control with them, You could make Subroutine like this.<br><br><b>Dim arryStates() as Variant<br><br>Public Sub PopComboBoxWithStates(vCnt as ComboBox, vRefresh as Boolean)&nbsp;&nbsp;<br>Dim Cmd as Command<br>Dim rs as Recordset<br>Dim itr as Integer<br>If vRefresh then ' Populate the arrays, <br>&nbsp;&nbsp;&nbsp;&nbsp;'Code to connect to you DB<br>&nbsp;&nbsp;&nbsp;&nbsp;'Code to create the Command<br>&nbsp;&nbsp;&nbsp;&nbsp;'Code to create the Connection object (Firehouse)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If Not rs.EOF<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;arryStates = rs.GetRows 'Returns a Variant array<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Set rs = Nothing 'Close the recordset<br>End If<br>' Otherwise just populate the ComboBox<br>vCnt.Clear<br>For itr = 0 to Ubound(arryStates)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vCnt.AddItem arryStates(itr)<br>Next<br><br>End Sub </b><br><br>I left out some needed error checking, Hope this helps<br>Collin&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>

 

[BitCounter]

Collin:<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;That's exactly what I'm doing now...my question was very confusing (I know that)..sorry.&nbsp;&nbsp;My question was this if I am populating 3-4 combo-boxes on the same form...I have the same database connection for each one (seems redundant to me).&nbsp;&nbsp;For the sake of simpleness, I'd instead of having the same code 3-4 times (example below):<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Dim Cmd as Command<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim rs as Recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim itr as Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;'Code to connect to you DB<br>&nbsp;&nbsp;&nbsp;&nbsp;'Code to create the Command<br>&nbsp;&nbsp;&nbsp;&nbsp;'Code to create the Connection object (Firehouse)<br><br>I'd like this code in 1 place (a public function) and simply to call it each time I needed to make the connection.&nbsp;&nbsp;This would save me steps especially when the location of the database might change (i.e., I'd have less places to change the actual database path).&nbsp;&nbsp;<br><br>Do you have any suggestions on how I can make the actual connection public?<br><br>Thanks<br><br>Renae<br>

 

[Collin]

Well..yes.That was the purpose of the blnRefresh parameter if it is true then the array is &quot;refreshed&quot; by opening up a connection to the Database and putting the results in the array, if it is false then the ComboBox is just populated from the array (Which is a Public Variable) without re-connecting to the Database. Therfore you would only have to connect to the database for the first ComboBox, all other ComboBoxes are just loaded from the array<br><br>For example<br><br><b>Form1_Load<br><br>PopComboBoxWithStates(Combo1, True) 'Connects to DB<br>PopComboBoxWithStates(Combo2, False) 'No DB<br>PopComboBoxWithStates(Combo3, False) 'No DB</b><br><br>If I had made the If statement like this<br><br>&nbsp;&nbsp;<b>If Not vRefresh then ....</b><br><br><b>Form1_Load<br>Dim itr as Integer<br><br>For itr = 0 to 2<br>&nbsp;&nbsp;&nbsp;PopComboBoxWithStates(Combo1(itr),itr)<br>next</b><br><br>Collin