Combo box row source in a split database

Posted on 2005-04-15
Last Modified: 2010-05-18
I have a form that has a combo box which is used to go to specific records.  As you type the company name, the combo box contents advances with each letter.  When the company is selected, pressing enter will display that company in the form.  The form's record source is a query that is created using QueryDef when the user logs in prior to opening the Comapny form.  On the comppany form there are several buttons that will change the record source of the form ( All Companies, My Companies, Calls Due).  All queries are created at login.  Each time a button is clicked to change the record source the underlying query updates to reflect the records in the query.  The row source is based on the current record source of the query.

The combo box works perfectly well.  The problem is that when I split the database, there is a huge delay as the user types in each letter to the combo box.  Then, the impatient users will backspace and compound the problem.  I assume that with each letter typed, the row source query is executing on the table that is located on the server.  Is there a way to take a snapshot of the query and have it located on the user's local database.  In other words, when a button is clicked and the underlying query is executed, can a copy of that query be a table on the user's local database and that table would then be the row source of the combo box?

Also, if there is nothing in the combo box that matches the typed letters and the combo box is empty, a message appears saying that something must be entered.  Is there a way around this?

Question by:beyondt
    LVL 17

    Expert Comment

    "a message appears saying that something must be entered.  Is there a way around this/"

    Check the table design and make sure the field that you have bound to the combo is set to:

    Allow Zero = Yes
    Required = No

    As far as your combo box performance there is not much you can do other that maybe creating a persistent connection to the database upon startup.  Are you using SQL server or is it a Jet back end?

    In one of your modules create a Public Recordset
    Dim Public PersistentRS as recordset, PersistentDb As Database, PersistentWS As Workspace

    Then On your start code try something like this:

    Set PersistentDb = DBEngine.Workspaces(0).Databases(0)
    Set PersistentWS = DBEngine.Workspaces(0)
    Set PersistentDb = PersistentWS.OpenDatabase("F:\YourDatabase")  ' Open database.
    Set PersistentRS = PersistentDb.OpenRecordset("YourComboTable"   'Open Recordset.

    On Close of DB:

    Private Sub Form_Close()


    LVL 17

    Expert Comment

    Oops! left out a ) in a set statement
    Should be
    Set PersistentRS = PersistentDb.OpenRecordset("YourComboTable")   'Open Recordset.


    Author Comment

    The tables are on a network haard drive.  Does that make any difference?  Should the Set Persistent codes go in the login form?

    What exactly does the set persistant code doe and how would it speed it up if it does?

    Thanks.  I'll let you know something today or tomorrow.
    LVL 17

    Expert Comment

    Yes, the database path can be on a network drive. And yes, you could put this in your login form.  A persistent connection just keeps the database open and active and should help performance. However, it doesn't always help.  Sometimes you have to increase the memory in your workstation and even processor speed can affect combobox performance. Also, don't forget to close the connection when you close the database.
    LVL 74

    Assisted Solution

    by:Jeffrey Coachman

    You might also look into creating a temporary table. Yes, they me a pain to maintain, and you might need to refresh to get new data. But it might be worth considering.

    just a thought

    Author Comment

    Setting up a temporary table was what I was thinking of.  I just don't know how to go about doing it.

    Could I setup a Dynset table that would reside in the local DB?  If so, can you give me an idea as to how that would be set up?  From what I am reading, a dynaset table is updateable.  How would I go about setting the table up?  It only needs a few fields based on the query that is the current record set of the form.  How would I update the temporary dynaset table?

    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    Here is what I have done in the past.
    Give this a try, at least to see if it speeds up your combobox

    Back up your database first, and try this on a (Spilt) copy
    (Rename "Combo18" to whatever your combobox is named)

    Open the Form in Design View
    RightClick the combobox
    Choose "Properties"
    Click the "All" Tab
    Go to the "Row Source" Property
    Click the (...) button
    File -->Save As: "qrymtTempCustLookup"
    Close, when asked to Save: SAY NO!
    Close the Property Box
    Save and Close the Form

    Open the new Query in design View
    Query--> Make Table
    Table Name: "tblTempCustLookUp"
    Run the query
    Save and Close
    Inspect New Table
    Make this temp table the rowsource for the Combobox.

    Create a public Function in a module called "RefreshCustInfo"

    Public Function RefreshCustInfo()
        DoCmd.SetWarnings False 'disable warnings
        DoCmd.OpenQuery "qrymtTempCustLookup"
        DoCmd.SetWarnings True 'enable warnings
    End Function

    On the Main form insert a button
    Name "cmdRefreshCustInfo"

    On the Click event:

    Private Sub cmdRefreshCustInfo_Click()
        Me.Combo18.RowSource = ""
        Me.Combo18.RowSource = "tblTempCustLookUp"
    End Sub

    Save and Close

    Create an Autoexec Macro
    Command: "RunCode"
    Function Name: "RefreshCustInfo ()"

    Name "AutoExec"

    Let me kown if this is clearly written
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    Here are some things you should note

    First and foremost, the way I have shown above is by no means the only way to accomplish this.

    (Arji's solution looks like it is very well thought out)

    1. Will each user have their own Temp Table?
    2. If you delete a Customer in the Main Table, they could still show up in the combobox.
    3. Will users know when to Refresh?

    As you can see, (and has been stated here before) temporary tables can be tricky to maintain.

    But at least you can test/refine my code to; at least see if it helps with speeding up the combobox.

    Good luck,

    Let us know how this turns out

    Author Comment

    I have not forgotten about this issue.  I just have been swamped with working out some of the bugs.  I have come to find out that the speed issue is mainly caused by having multiple users accessign the dataabse at the same time.  There are only seven users but I think because the back-end is on a network hard drive, bandwidth and prcessing becomes the issue.  That being said, are there any ideas as to address the speed issue in this particular situation?  I assume that if I went with MySQL it would probably fix the problem.  But that can't happen overnight, so am  stuck with this performance or is there something I can do in the meantime?

    Thanks for you help.
    LVL 17

    Accepted Solution

    Hopefully, you are on a network running at a least 100mb/s.  Beyond that, seven users can be a bit of a load for Jet but the network bandwidth and is probably the most critical issue in database speed(other than the DB engine itself).  Other network issues could be whether you are using a switch as a opposed to a hub, server memory size, server drive performance(SATA or high speed SCSI is recommended) and server processor clock speed.  Ideally your server would have at least:

    4 GBs of memory(more is better)
    2.6 Ghz Xeon processor(2 is better)
    SATA or SCSI 320 drives
    100 mb/s Switch(best if it has a 1 gb/s uplink to your server

    I have a client running Access 2k3 with the above server specs.  They can have up to 12 people on the database simultaneously by using Windows Terminal Services and running the client app on the server.  This works very well.  Good luck!


    Author Comment

    Thanks for all of the input on this!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now