Forms Loading Slowly with SQL Backend

[JustinS]

I recently split a database that I have been working on into a sql backend and an Access front end. The split process was handled by my company's IT department. Prior to the split all forms, reports, queries, and tables loaded very quickly. However, since I received the database back from IT the forms seem to lag quite a bit. The tables, queries, and reports all seem to load normally.

With this stated, do you guys have any ideas as to what might be causing this problem with my forms? It really is just the forms.

 

[Minty]

Where is the SQL server sited - on site / remote ?
By default do most of your forms load with all the records ?

Do you have lots of Combo's on the forms that could be dragging lots of data across?

Any VBA functions performing "magic" on the data you are displaying?

 

[JustinS]

@Minty
Sql Server is remote.

There's a mix, in that some forms load all data while others focus on a singular record.

There's a mix on the comboboxes as well. Some forms have comboboxes, but I'm also having trouble with displaying just the datasheet portion of a split form.

I have some vba, but most of that is just hiding or showing or filtering information based upon user selection. No real magic.

 

[theDBguy]

Hi. You could try doing a little experiment. Create a new form without code and only load a single record into it to see if it runs slow or not.

 

[JustinS]

@theDBguy

I made a quick form as you instructed. Seems to load a lot quicker than the existing forms.

 

[theDBguy]

@theDBguy

I made a quick form as you instructed. Seems to load a lot quicker than the existing forms.

That's good news. The next experiment would be to create a new form to duplicate one of the old forms and test it.

Or, another approach is to create a new database front end file and import all the objects from the front end file IT sent you.

 

[Minty]

@Minty
Sql Server is remote.

This is probably the biggest factor. I'm guessing that beforehand the Access DB BE was relatively local?

If so any forms that load all the underlying records are going to be slow, especially on larger datasets.

Combo's that do provide lookups, if the data I relatively fixed can be moved to a local FE lookup, and updated on DB Load with the latest records.

There are a lot of other considerations such as only loading the first 20 records or so on a continuous form, then reloading with a user-defined subset based on form search criteria.

 

[JustinS]

@theDBguy

I tried both approaches, the forms still load very slowly.

 

[theDBguy]

@theDBguy

I tried both approaches, the forms still load very slowly.

When you said both, for the first approach, did you actually start from scratch? If not, that's what I meant.

 

[JustinS]

@theDBguy

Ok, so I didn't start from scratch. I just did a copy and paste.

Went back and created a copy from scratch. Didn't put all my code in, but it loaded a bit faster than what I received from IT. Still a bit slower than before though.

What do you think might be going on?

 

[CJ_London]

take a look at this link - there may be some ideas there you can try and/or check you are already doing

addressing performance issues

As your db expands and there are more active users, it is quite possible you will experience performance issues and may be considering upsizing to SQL Server or MySQL. Before considering such a transition, you should review your current design: Overview From a performance perspective, Access...

www.access-programmers.co.uk

in particular, when your IT dept split the db, did they include indexing to the tables? Other than changing the links, did they do anything else, such as change your form code to use ADO rather than DAO?

 

[Pat Hartman]

People are always shocked when they convert a Jet/ACE BE to SQL Server and the app slows down. What the??? Well, Jet/ACE is pretty quick and is optimized to work with Access. When you move to a RDBMS, YOU have to think differently and you can't use the old Access methods of binding forms to tables and using filters to get to the records you want to work with.

In a client/server environment, forms should be based on queries and the queries need to have selection criteria to reduce the number of rows requested to only the one you want at that time. For simple criteria, I just use combo or text boxes in the form's header. The query then includes criteria to reference the unbound form field in the form's header.

Select ... From ... Where SomeField = Forms!myform!cboSomeField

The form will open empty since SomeField has no value. The user picks something from the combo or types something into a text box and the AfterUpdate event of the search control does a requery.

Me.Requery

The record you want is loaded.

Fix one of your forms to operate this way to see how it works.

Also, keep in mind that WAN speeds are at least 10 times slower than LAN speeds so connecting to a remote database via the internet is ALWAYS going to be sluggish no matter how fast your internet connection.

The best way to share an Access application over the internet is to use Citrix. Using Citrix, your remote users will sometimes get faster response times than your local LAN users