time-out expired during a DSUM

[bzb]

When I linked tables to SQL database, I'll get a time-out
expired error during a DSUM formula on a 1 million records
table. No such error if I linked the table to ACCESS
database of same size. Also, the .ADDNEW seems to last
forever during the first added record of a big table; but
this doesn't happen in a ACCESS table. Can anyone help,
pleaseeee?

 

[Larry Linson]

I don't know the details of how DSUM works with a server database. I do know
that we avoided using it in the past because in many cases it was much
slower than other ways to accomplish the same thing. It was often said that
it "didn't take advantage of indexes", but that seems to be an urban legend.

I don't recall any particular "general" performance problem using DAO to
write to server databases; but, as an alternative, you might try using an
APPEND query. If that does not give adequate performance, you might try
using a passthrough query to block Jet completely out of the loop and force
everything to be done server-side.

Larry Linson
Microsoft Access MVP