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

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?

0
axwack (5)
7/21/2007 2:48:53 PM
excel 39879 articles. 2 followers. Follow

3 Replies
515 Views

Similar Articles

[PageSpeed] 40

On Jul 22, 12:48 am, axwack <axw...@gmail.com> 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?

0
AnandaSim (8)
7/22/2007 1:26:28 PM
On Jul 22, 9:26 am, AnandaSim <Ananda...@gmail.com> wrote:
> On Jul 22, 12:48 am, axwack <axw...@gmail.com> 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 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

0
axwack (5)
7/23/2007 1:59:04 AM
On Jul 23, 11:59 am, axwack <axw...@gmail.com> wrote:

>     'Now open the connection.
>     cnPubs.Open strConn
>
>     ' Create a recordset object.
>     Set rsPubs = New ADODB.Recordset
>     'Clear Worksheet

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

0
AnandaSim (8)
7/23/2007 11:06:57 AM
Reply:

Similar Artilces:

Access 97 can't resize database window
My database window with the listings of forms tables etc was adjusted to a smaller width, but resizing it is completely disabled and renders Access 2007 utterly useless for me. Is there anyway to 'reset' the window? ...

Drop-down list #5
I created a drop-down list in a separate sheet in my workbook. I named it Vehicles. Now I want to add to the list, but I can't figure out how to do it. I know ig must be so easy, but I'm stumped. Please help. If it's a one-time occurrence, you can press Ctrl+F3, click on the name, and extend the formula. Otherwise, I would make the range dynamic. You can learn how to do that here: http://www.contextures.com/xlNames01.html#Dynamic HTH Jason Atlanta, GA >-----Original Message----- >I created a drop-down list in a separate sheet in my workbook. I named it >Vehicles...

exporting outlook 2000 pst files to a database
I need to export outlook 2000 to a database- attachments and all. Is there a simple way to do this so you can search and retreive messages and attachments in a database form? Thanks Jeff You can export individual folders to access or excel to have them available for searching. I have never tried to export my entire .pst file to access or excel however. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding no answer Jeff <laacid@yahoo.com> asked: | I need to export outlook 2000 to a database- ...

ignore list
I have importet some contact data into mscrm, When I want to add these contacts to a marketing list (add marketing list members / use advanced find/ add all selected members), the adding stops with an error. I have done a trace during the error (occurs everytime I want to add these contacts) which shows me the following error: [2009-08-24 11:15:36.778] Process:OUTLOOK |Thread:5884 |Category: Unmanaged.Platform |User: PlatformUser |Level: Error | Found crmId {319C876A-CC39-DC11-9F61-0030485C3892} in ignore list. Update notification will be ignored Function: CItemHelper<struct Outlook::_Co...

Queries and Charts
Does anyone know why the expressions in queries work fine for reports but not charts? ...

Outlook Express and LAN connection
I have Outlook Express 6.00.280 installed on Win NT 4.0 SP6 which is LAN connected to another PC that has Internet connection (Proxy server). Email account on my PC is POP3 for incoming mails and SMTP for outcoming. Now the problem is that I cannot receive emails but can only send them through this Proxy. Internet Explorer and all other applications that require Internet connection through proxy are working OK. The only problem I have is to receive emails. I setup the same account on another PC which has direct connection to the Internet through dialup and no problems for both sending and rece...

Pass parameter from FORM to QUERY
I have an append query that I trying to call from a cmd btn...but it prompts me for the parameter(ie QuoteID)... How do I tye it into my call: Dim stQueName As String stQueName = "Quote Query" DoCmd.OpenQuery stQueName, acNormal, acEdit On Apr 13, 12:56 pm, jlt...@hotmail.com wrote: > I have an append query that I trying to call from a cmd btn...but it > prompts me for the parameter(ie QuoteID)... > How do I tye it into my call: > > Dim stQueName As String > stQueName = "Quote Query" > DoCmd.OpenQuery stQueName, acNormal, acEd...

How do I copy the result of a check box into another check box?
I'm trying to copy the result of a "check box form field" into another check box. For example: if I check (or uncheck) one box in a form, another box later in the protected form will also be checked (or unchecked) , much like the ref + F9 command for the text form field. Is this possible? Thanks. This cannot be done without macros. If your project will allow macros - see http://word.mvps.org/faqs/tblsfldsfms/ExclusiveFmFldChbxs.htm -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> G...

can I snap wrap points to a text box
rather than having to add individual wrap points to the edge of a frame, which is never as accurate anyway, can they be set to 'snap' to a frame (eg the ellipse) so that they are perfectly inline, (and which would of course be a lot quicker)? Edit points will not snap. There are options for edit points, select a point, right click. If you hold down control, the cursor will turn into an x, you can delete a point with a click. Truly a good draw program would be preferable. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com &q...

Global distribution List Not Appearing in Outlook Global Selections list
I have created a global list in AD in Server 2003 ( using Exchange 2003) and assigned my users. For some reason the list is not showing in the Outlook client global address list. I have compared this list to others i have created in the past and cannot spot any differences. What am I missing? 1. Have you got it hidden from the GAL? 2. Using cached Exchange mode? Therefore requiring a rebuild of the OAB to see it? Oliver On Fri, 16 Feb 2007 13:24:05 -0500, "rlm" <rmorton@execpc.com> wrote: >I have created a global list in AD in Server 2003 ( using Exchange 2003...

Changing query execution sequence
Hi all, I got a spreadsheet which would execute a bunch of queries. It's noted that the queries are executing in the sequence of when it was added to the spreadsheet. Does anyone out there know of a way to switch the order without deleting and recreating them? Thanks! Wing ...

Backing Up Exchange Database
Hi I am running Exchange 2003 on a Windows 2000 server. Veritas Backup 10, is installed on the server. The Exchange Agent is also installed on this server. A full backup with the option to clear committed logs is used to perform the daily exchange backups. I noticed that in the MDBDATA folder the logs are continuously being added approximately 5MB in size. Is there a way to have these logs removed automatically as I thought that the backup would perform that function. Thanks On Thu, 5 Jan 2006 17:18:02 -0800, microsoft <microsoft@discussions.microsoft.com> wrote: >Hi >...

SQL Server Connection Failed
Hi everybody, this is my scenario: 1. Windows Server 2008 2. WSS 3.0 + MOSS 2007 3. SQL Server 2008. i'm having this problem: "Login failed for user 'sa'. Reason: An error occurred while evaluating the password. [CLIENT: 72.55.191.137]". in this momment i can't see any application from sharepoint. somebody knows how can i do? i don't change the sa password. i don't change anything in the sharepoint configuration. since two days ago i'm having this problem. pls, is very urgent, because i have to deliver this server the next monday to the...

Customize columns in 'Marketing List Members'
I can't figure out where one can customize the columns used within the "marketing list" entitry when you click the 'marketing list members' on the left side to show the members. I want to add some columns, like Email. Screenshot: http://i355.photobucket.com/albums/r469/canadaka_bucket/marketing_list_members.jpg Just read the Posting on the Microsoft CRM Team Blog. <canadaka@gmail.com> schrieb im Newsbeitrag news:306584c6-2043-4962-b12a-d0b9287684bb@b31g2000prb.googlegroups.com... > I can't figure out where one can customize the columns used within the >...

Backing up databases on remote SQL
Hi, I recently followed one of the MS articles on how to move GP 9 sql database to a different server. Everything went well except when trying to run the backup option from the GP9 server. I currently have a server with GP9 installed and another server with SQL and the GP databases. When I try to run the backup from the backup option on the GP server it says " You must be on the server machine to perform this task". presume this is because the SQL is no longer on that server. The SQL server has a backup routine of its own, but I would like our finance users to have the ability...

Refreshing list boxes
Hi, I have created a database so that mulpile users can add detailss to the table - tblDetails from the form frmDetails or amend details in form frmAmend. I have a list box on the main page frmMain which has a list box lstSearch which shows all the records in the database. When users add new record to the database other users cannot see the added records unless the move to another section in the datbase and the return to the main page thus refreshing the list box. Can anyone tell me if there is a way I can put a "Refresh" button on the main page that updated the list box if the use...

how to automatically update inventory list with sales
Please provide the help on how to update the inventory list when some items are sold. Do you want to check this out while you wait for an answer? http://office.microsoft.com/en-us/FX011429711033.aspx Epinn "lalani" <lalani@discussions.microsoft.com> wrote in message news:07A10BC7-97FB-4E93-A686-CAAE3AA2DE88@microsoft.com... > Please provide the help on how to update the inventory list when some items are > sold. The page that Epinn points out is probably as good as any. Excel is a rather poor tool for trying to track inventory. You'll notice that the actual...

multiple iterations of same query
Running Access 2003 on Vista. I have a query that chooses 15 records at random. This works fine. What I want to do is run the same query for approx 110 different users, each with their own randomly selected 15 records. Obviously, I want to avoid manually running the same query that many times. How can I get around this? Post your query SQL. -- Build a little, test a little. "Nathan" wrote: > Running Access 2003 on Vista. > > I have a query that chooses 15 records at random. This works fine. > > What I want to do is run the same query fo...

Excel List Sorting Problem (Descending)
Hi there, I'm having trouble sorting my list--my column contains *only* 4-digit numbers but when I click on "descending order", only about the first half of the rows are arranged this way, before it begins again to arrange the rest in descending order. Like this: 5120 5119 5118 4000 3050 5116 4112 etc. Has this problem happened for anybody else? I'd appreciate any help you can offer. Part of your list is text, although it looks like numbers. Format an empty cell as number. Enter the number 1. Copy. Select your "numbers". Edit>Paste Special, check Mul...

Data validation list from another worksheet?
Is it possible that the value list for data validation be populated fro another worksheet? Puneet Aror -- puneetarora_1 ----------------------------------------------------------------------- puneetarora_12's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1840 View this thread: http://www.excelforum.com/showthread.php?threadid=38572 Sure is! Use a named range as described here: http://www.officearticles.com/excel/drop-down_using_data_validation_in_microsoft_excel.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "punee...

Find Highest Score In List Formula
Hello all, I'm looking to return the highest score for a users with multiple scores in a list of other users with multiple scores. Thank you, Ron Say the data is like: frank 56 joe 9 frank 74 frank 101 jim 143 jim 146 joe 200 frank 164 joe 135 joe 127 joe 177 jim 10 jim 135 jim 53 frank 190 joe 109 jim 193 jim 29 jim 8 jim 107 joe 93 joe 9 jim 153 jim 186 joe 36 jim 174 jim 141 frank 55 jim 92 frank 141 joe 15 frank 5 frank 34 joe 161 jim 103 joe 88 and we want the max score for frank: =MAX(IF(A1:A36="frank",B1:B36,""...

Listing recurring appointments
I have tried in both Outlook 2002 and 2003 to print a list for my cleaning service that shows the dates in a list. The appointment is set up as a recurring every 4 week. I cannot find a way to list it outside of reading the dates, and writing them on a separate report. Thank you. Can you create a view that shows only those appointments? One way would be to assign a category to them, and then create a new view that filters based on the category field. Then you ought to be able to print just those items, in a table view. Tony Linguini wrote: > I have tried in both Outlook 2002 and 2003 to...

Can not open additional mailboxes in my mailbox after a exchange 2003 rebuild database
To Exchange 2003 Gurus I have a exchange server that serve a lot of users. 3 week ago I had to repair the database with great success. But now I noticed a problem that in the past I was able to acomplished I can not open additional mailboxes in my mailbox.(right click on my mailbox>properties>advanced>Advanced tab> and when I click on add I get THE NAME COULD NOT BE RESOLVE. THE ACTION COULD NOT BE COMPLETED. This message appear eventhough I did not type anything Here is more info -I am able to open additional mailboxes if I use a computer with my profile that I created before I r...

Result in message box
HI, this is second time iam posting this ,,kindly help me a form contains button which runs a query like select count(field1) from table t1 ... then normally we will get a spread sheet window containing result in a single cell, let say answer is 85. now when command button is clicked then a message box should appear displaying the result 85.. please give coding also if required..... thanks naveen prasad wrote: > HI, > this is second time iam posting this ,,kindly help me > What was wrong with the reply you've already received? Please reply in your origina...

How to show query parameters on an Excel page header or worksheet?
Is it possible to display query parameters on a page header or on a worksheet? I have a worksheet that uses a query to retrive data from an ODBC database. The Query prompts for the Start Date and End Date. I would like to be able to print the worksheet and display the Start Date and End Date the user typed in. Thank you for your assistance. ...