Combo box row source in a split database

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?

Who is Participating?
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!

"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()


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

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

beyondtAuthor Commented:
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.
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.
Jeffrey CoachmanMIS LiasonCommented:

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
beyondtAuthor Commented:
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?

Jeffrey CoachmanMIS LiasonCommented:

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
Jeffrey CoachmanMIS LiasonCommented:
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
beyondtAuthor Commented:
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.
beyondtAuthor Commented:
Thanks for all of the input on this!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.