Forms slow with SQL back end

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rod - 07 Nov 2007 13:09 GMT

I moved all of my tables in access to our SQL server. Ever since then my
forms that have subforms hve been very slow. Should I move everything back
into access?

Reply to this Message

Pieter Wijnen - 07 Nov 2007 13:56 GMT

Start by checking indexes instead
Are the subdata tables indexed on the link field(s)?

Pieter

>I moved all of my tables in access to our SQL server. Ever since then my
> forms that have subforms hve been very slow. Should I move everything back
> into access?

Reply to this Message

Rod - 07 Nov 2007 15:59 GMT

The form is a vehicle form and the subform is the equipment on that vehicle.
The Subform is linked to the main form by the field "sub of" to the vehicle
number field. (this is how it has always been). On the linked table
"equipment" the field "sub of" is not marked as an inexxed field. Is this
what you mean?

If so I don't know how to mark it "indexed" in SQL. (I can't change the
properties of a linked table)

> Start by checking indexes instead
> Are the subdata tables indexed on the link field(s)?
[quoted text clipped - 4 lines]
> > forms that have subforms hve been very slow. Should I move everything back
> > into access?

Reply to this Message

HubbardUK - 07 Nov 2007 19:27 GMT

Have you unchecked "Track name autocorrect info"?
Makes a big difference to the speed forms load when you have a lot of
controls and subforms.
As a matter of course I always uncheck it when using either SQL back end or
Access.

Find from Tools-> Options -> General tab and righthand side in Name
Autocorrect.

Richard

> The form is a vehicle form and the subform is the equipment on that
> vehicle.
[quoted text clipped - 16 lines]
>> > back
>> > into access?

Reply to this Message

Rod - 07 Nov 2007 19:50 GMT

Thanks but it's unchecked by default in the 2007 version. I didn't specify my
version.

> Have you unchecked "Track name autocorrect info"?
> Makes a big difference to the speed forms load when you have a lot of
[quoted text clipped - 27 lines]
> >> > back
> >> > into access?

Reply to this Message

Pieter Wijnen - 08 Nov 2007 01:19 GMT

That is what I mean
You can Create an Index for the SQL table Using a *passthrough* query
(the connect property for the query can be extracted from the linked table's
property sheet)

The SQL of the passthrough query would be similar to

CREATE INDEX MyIndexName On MyTableName (MyFieldName1,..,MyFieldNameN)

Note:
If your fields include spaces etc, you must bracket them by using [], or
enclose them in quotes " & match the case of each letter.
ie [sub of] or "sub of"

HtH

Pieter

> The form is a vehicle form and the subform is the equipment on that
> vehicle.
[quoted text clipped - 16 lines]
>> > back
>> > into access?

Reply to this Message