RE: Linked SQL query performance after upgrade 97 to 2003

That kbb article doesn't show the keeping connection open concept, but it
does discuss the use of the MSysConf Table. My guess is that A97 Jet would
return & dispaly a smaller # of records, giving the illusion of faster
performance. With the MSysConf Table, I think you can fine tune this feature
to behave the same.

See, if you can tell Access to return only a small number of records and
display them, then keep working in the background to get the rest of the
records, then the user is faked out by the smoke and mirrors.

Checkout Tony Taves performance FAQ
http://www.granite.ab.ca/Access/performancefaq.htm

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting



"SusieN" wrote:

Thanks Steve
A97 was also linked - all we did was upgrade the database to 2003 and
refresh the link tables. The point where performance is poor is on the
Access Find which is looking up on an indexed field. Only thing is that the
field is indexed in SQL, but does not show as index in Access - despite
refreshing the link. But field is also part of primary key (tho there is a
second field required for uniqueness, but only max of 2 records per
reference).
Will check the KB article for keeping the connection open.

Thanks

"S.Clark" wrote:

Was the A97 version linked tables to SQL also?
Post the SQL.

Other Idears:
-Remove any *'s,
-Ensure the criteria fields are indexed
-Configure all database front-end computers to maintain an open connection
to the back-end database
-http://support.microsoft.com/kb/208858/

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting

"SusieN" wrote:

Following upgrade of a database from Access 97 to 2003 we are experiencing
noticeably worse performance. We have a Find button (using the Access in
built Find box) on a linked SQL table of 30,000 records, using the
'reference' as the lookup. 'Reference' is one field of the primary key and
is also defined as an index on the SQL table - but scanning these records for
a valid reference is taking 30secs + where it used to be instant in Access
97. We have changed the SubData*** property (as per other posts along
these lines) but this has made no difference at all. Any suggestions,
please?!
.