MS Access 2013 Front End, SQL Server 2014 Developer backend.
I've been developing in Access for over a decade but now I'm working on my first Access app with a SQL Server backend. When I started the project I asked for suggestions on EE about using an Access FE and a SQL BE. I was under the impression that all data retrieval and storage should be done thru stored procedures.
Many EEer's disagreed with that and indicated that bound forms were perfectly acceptable and would perform well.. The forms are bound to SQL tables that are linked to the Access FE.
I developed the project using forms bound to an Access table, linked to SQL Server.
All my testing to this point is on a single user non-networked machine so the response should be as best as can be expected.
Initially when my data test set table sizes were in the hundreds of thousands of records range, the forms performed well. However, I recently converted the entire DB and now the backend tables have as many as 25 million records.
Once the full data set was in place the response time slowed down considerably. Some forms take 30 seconds to load a single record on a stand alone machine.
I began 'playing' with the forms to see if I could improve the performance and came up with a few items that helped reduce the load time of a form.
These comments only relate to the load time (the time it takes to present the one record requested on the screen) for a form that only works with a single record from the linked table at a time. Records may be added, revised or deleted. The load time only applies to records being edited or deleted.
1. Subforms can really slow down the loading of the form. Even if the recordset of the main form is a specific record in the bound table.
2. If a record ID has been passed in OpenArgs, Any bound forms should go directly to the record to be edited rather than loading the entire table. In all cases the ID passed in 'Openargs' is the primary key of the bound table.
In the Form load event
If IsNull(Me.OpenArgs) Then
Me.RecordSource = " Select Top 1 * From " & Me.RecordSource & " Where [MuniMastID] = " & Me.OpenArgs
3. The ‘Top1’ clause also helps when only one record should be returned.
Am I on the right track? Are there any other suggestions from experienced EEer's to help speed up Access forms bound to linked SQL tables?
As the database got bigger.