Access 2000 form is slow to load over a network

Question

I have an Access 2000 database that sits on a peer to peer network. (All computers are connected via ethernet 100 to a simple 4 port switch) The database is designed with a back-end and a front-end. There are 2 users that connect to the back-end via the network. One user connects to the back-end directly as the back-end sits on her computer. All the computers are fairly low spec, with either 500 meg or 1 gig of ram

I have one form, frmInvoice, which is based on the table "tblInvoice" There are about 60,000 records in the table. There is also the table "tblInvoiceProducts" which is linked to "tblInvoice". There are about 150,000 records in the table "tblInvoiceProducts".

When I open form "frmInvoice" on the computer which hosts the back-end, the form opens very quickly. However if I open the form on one of the other computers it is very slow to open. However, once opened, record navigation is fast.

I think the problem is that when the form is opened, Access opens the entire table and all the records are accessible. Am I correct in this assumption?

I need to rectify this problem and welcome all suggestions.

I am going to install a D-Link DNS-323 2-Bay Network Storage Enclosure and store the back-end database on this as this will improve security / data protection.

Thanks in advance

Answer 1

"I think the problem is that when the form is opened, Access opens the entire table and all the records are accessible. Am I correct in this assumption?"

My bet is you are absolutely correct.

If you open the property sheet for the form, choose the Data tab, and look at the value for Record Source, do you see the name of one of your tables?

Or a query without a WHERE clause, perhaps like "SELECT * FROM tblInvoice;"?

If your answer is yes to either of those questions, Access will have to pull every single record from the table across the wire.

So, don't do that! :-) The key to decent performance is to limit the form's Record Source to a reasonable subset of rows.

Choose some criterion which makes sense in your situation, perhaps invoice date, and build a query which incorporates that criterion into its WHERE clause. Base that criterion on an indexed field --- add an index if your table doesn't already have one on that field. You can experiment by creating a new query in the query designer ... maybe the SQL View would look like this:

SELECT Invoice_ID, Customer_ID, invoice_date
FROM tblInvoice
WHERE invoice_date = Date();

That would retrieve only rows where invoice_date matches today's date.

(If you've already loaded the form, pulling down all the rows into local cache, you won't get a true indication of the speed of that query. It would be better to test the query from a new Access session without first loading the form.)

So then give the user a method to select a different invoice_date. For example, a text box control named txtSelectDate. And in the After Update event of that control, you can write an updated SELECT statement which you apply as the form's Record Source.

Private Sub txtSelectDate_AfterUpdate()
    Dim strSql As String
    If Len(Me.txtSelectDate & "") > 0 Then
        strSql = "SELECT Invoice_ID, Customer_ID, invoice_date" & vbCrLf & _
            "FROM tblInvoice" & vbCrLf & _
            "WHERE invoice_date = " & Format(Me.txtSelectDate, "\#yyyy-m-d\#")
        Debug.Print strSql
        Me.RecordSource = strSql
    End If
End Sub

The Debug.Print line will print the SELECT statement in the Immediate Window so if anything goes wrong you can view the completed statement, and copy & paste it into SQL View of a new query for testing. Changing the Record Source property automatically causes Access to requery the data source. And you can validate the user's entry in the text box's Before Update event to make sure it's a valid date.

Another possibility is to have the form first load with a single non-editable dummy record(1). Then display records only after the user chooses an invoice_date. To do that, you could use something like this as the form's Record Source:

SELECT TOP 1 Null AS Invoice_ID, Null AS Customer_ID, Null AS invoice_date
FROM tblInvoice;

Answer 2

In addition to the excellent suggestions made by HansUp, I would add the following:

• Detailed forms should never take more than a couple of seconds to load. If they do, you're either doing something wrong or you need to optimize your binding/code.
  Datasheet and reports can take longer depending on their complexity and how often people need them. Basically, if it's something they need a lot, it need to be fast. On the other hand, if it's a monthly report, waiting 30s is acceptable.

• You don't say if your front end is split from the back-end or if you're using the same .mdb for your data and UI. If you do, then stop and split your database, even for the person on the same computer as the data.
  Your back-end mdb file should only contain your data tables, and nothing else. The front-end mdb must be installed on the user's individual machines.

• Make sure that your tables have the right indexes for your filter and sorting criteria. If you're binding your form to your tblInvoice and set the property to order by invoice reference and your table doesn't have an index for this, Jet will need to load all invoice references from the table to check their order.

• If all your indexes are OK, Access will not load all the data to display a single record in a detailed form. It will only load lots of data if you're using a datasheet or a continuous form.
  So the issue is probably with something else: maybe you're binding to a complex query from within your form, like a combo box or a datasheet, that it's that particular thing that's dragging your down.

• Try to bind/unbind controls one by one to see which one has the biggest impact, then concentrate on optimizing that particular area.

• You can also bind the controls as needed from code. For instance you can keep your form or the expensive controls unbound and wait for the user to select the record they need before you bind it to the form/control.

• If you have lots of information on the form that loads related data from other queries and tables, you could re-design the form to group data in a Tab Control, then have a sub-form in each tab-control to display the data and load this only the user selects the tab.

• Make sure that if you display lots of data in continuous forms/datasheets, that they are bound as snapshot to make them read-only. If they are read/write and someone else is trying to load a detailed form, Access has to do extensive lock check to make sure that the data is not being edited in multiple places. The more you make your data read-only, the better.

• A final tip for for better performance: create a dummy table with a DUMMY field in your backend, link to it from the front-end. Bind this table to a form that has a single control for the DUMMY field. Make this form invisible and open it automatically when you start your application.
  This form will keep the dummy table open and will ensure that your front-end doesn't have to open/close the connection to the backend every time it needs to query it.
  Locking the database is time-expensive, especially over the network. If this is done very often, it can really decrease performance.
  Maybe it won't change anything for your particular case, but I've found it to be good practice as the impact can be tremendous.