Forms Loading Slowly with SQL Backend (1 Viewer)

JustinS

Member
Local time
Today, 07:34
Joined
Apr 11, 2020
Messages
58
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

AWF VIP
Local time
Today, 11:34
Joined
Jul 26, 2013
Messages
10,355
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

Member
Local time
Today, 07:34
Joined
Apr 11, 2020
Messages
58
@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

I’m here to help
Staff member
Local time
Today, 04:34
Joined
Oct 29, 2018
Messages
21,358
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

Member
Local time
Today, 07:34
Joined
Apr 11, 2020
Messages
58
@theDBguy

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

theDBguy

I’m here to help
Staff member
Local time
Today, 04:34
Joined
Oct 29, 2018
Messages
21,358
@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

AWF VIP
Local time
Today, 11:34
Joined
Jul 26, 2013
Messages
10,355
@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

Member
Local time
Today, 07:34
Joined
Apr 11, 2020
Messages
58
@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

Super Moderator
Staff member
Local time
Today, 11:34
Joined
Feb 19, 2013
Messages
16,553
take a look at this link - there may be some ideas there you can try and/or check you are already doing


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

Super Moderator
Staff member
Local time
Today, 07:34
Joined
Feb 19, 2002
Messages
42,981
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:)
 

Users who are viewing this thread

Top Bottom