Forms slow with SQL back end

R

Rod

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?
 
P

Pieter Wijnen

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

Pieter
 
R

Rod

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)
 
H

HubbardUK

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
 
R

Rod

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

Pieter Wijnen

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
 
Top