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?