Slow connecting between .fe and .be

[Guest]

I have a form loaded on the front end of a database that, when opened,
executes 3 queries, one append and two update. The idea is to update any new
records in the stored table from a linked file that gets downloaded each
night. In this way, new records are added to the table, old records no longer
in the text file get their record status updated to "No Longer On Pending
Report".

When I created the form/table/ and queries, they were contained in a
seperate database. The queries and form opened quickly and smoothly. However,
the moment I added the table to the back end of the database and added the
linked table to the front end, it takes almost two minutes for the append
query to complete, then the following two update queries execture as quickly
as when they did when the table existed in the front end.

My question is, is there something I might modify to the query or DB that
will allow the initial append query to run faster across the link to the
server where the back end resides?

Never ran into this problem before...other linked tables run as quick in
terms of update and append queries.

TIA

 

[Guest]

Double check that the tables are properly indexed in the BE. Fields in the
criteria of the queries should be indexed. It's also possible that there are
too many indexes which could slow down updates and appends. Access has a bad
habit of creating indexes on fields with the characters ID, key, code, or num
in them. If you have a primary key called something like PersonID, Access
will create an index for the PK and another one because it has ID in PersonID.

Sometimes with slow connections it helps to have a persistant connection.
Create a bogus table in the BE with a record or two in it. In the FE create a
form based on this table. Have this form open up when the FE first opens and
keep the form open. It can be hidden.

I'd also be interested in seeing the slow SQL statement. Sometimes a tweak
or two with the SQL can work wonders.

 

[Aaron Kempf]

WRONG

Access MDB is too slow for real world use

move to SQL Server
SERIOUSLY here kid