Hi there, Access users!
Please help me improve the performance of my database. Here are the problems:

I have a table "clients" related one-to-many to table "requests" which is related one-to-many to table "models".

"Clients" has 11000 records, "Requests" - 13000 records and "models" - 14000 records.

1st problem: I have a form used to introduce new clients and requests (Data Entry), which is based on a query on this 2 tables, and a subform for models, linked to the corresponding table.
Opening this form takes about 30 seconds because it runs the entire query to open the form.
If I pass the SQL code to the "Record Source" of the form, it opens instantly, but I get errors of locking and up-dating the data, when more users are entering data in the same time.
What can you recommend me?

2: A second form is used for editing records. It is related to table "clients" with a subform - "requests" and a sub-subform - "models". I open this form passing the name of the client (from another form) and it takes 1 minute to open it (shows all requests for one client and the models for the selected request).
How can I speed up the process?

Good Luck to all!
Mihai

Buy a faster computer... :)

If you can find a way to restrict the amount of data being accessed in the requests, your performance will improve.

(Access is not really a good choice for large databases...)

Hi,

   I had this very same problem and here is how I got around it (with a lot of help from Access Advisor Magazine - the best).

   You need to have a main form with a button to open this huge client form. Put a combo box on that main form to allow the user to select the Client they want (populate it with a look up to your clients table.)

     Then on the button to open the form from the main form, put this code on the On Click event:

'=====
    Dim stDocName As String
    Dim stClient As String
    Dim stLinkCriteria As String

    stClient = Me.cboClient

    stLinkCriteria = "[ClientID] = " & stClient
    stDocName = "frmHugeClientForm"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
'...
'=====

   This will open your huge form with just one record. That will be very fast.

   Then on the huge form, if you want your user to navigate to another record, put a look up combo box again looking up the same client records (in the table, not on the form).

   Then on that combo box's After Update event, put this code:

'=====
Me.RecordSource = "SELECT * from dbo_tblUserName WHERE UserID = " & Str(cboUserName)
'=====

   This will pass the client name to the recordsource of the form and open it again with just one record.

   This will ber very fast, and will prevent locking issues etc since the user will only ever have one record open from that table at a time. This will populate your subform(s) of course.

   I am not sure how to resolve your second issue. DO you have more information?

HTH





mmcdonal

Sorry, the second code snippet was word for word from my large Access database - that performs quite well thank you.

   It should be this for generic reference:

'=====
Me.RecordSource = "SELECT * from tblClient WHERE ClientID = " & Str(cboClientLookUp)
'=====

Thanks



mmcdonal

Sorry again, but given the number of transactions that you are doing here, it might be better to move to SQL as BrianWren suggests.



mmcdonal

Open all your forms when you open your database. Instead of opening them...switch the visibilities on and off.

Hope this helps,
Modest

Thank you all for the input!
The way the forms work is quite the same as Michael suggested. Opening the form with just one client is instant. But it takes a lot of time to find the data for the subforms.
So, this week I've been studying SQL server and starting from June we'll migrate the data to SQL Server. And of course we'll buy a faster server, as Brian Suggested.
Thanks again!

Mihai