Timeout on query

[jdam]

Hi guys

I have a Query in access 2000 front end connected via odbc to SQL server database backend. The following query runs fine in design mode but timesout at runtime. Any suggestion?

UPDATE _CEInter LEFT JOIN FFTWeb ON ([_CEInter].VENDOR = FFTWeb.Vendor) AND ([_CEInter].ACCTDATE = FFTWeb.AM) SET FFTWeb.InvoiceDate = [_CEInter].InvoiceDate, FFTWeb.Invoice = [_CEInter].InvoiceNumber
WHERE (((FFTWeb.Invoice) Is Null) AND (([_CEInter].ACCTDATE)=Get_AMth()) AND ((FFTWeb.TrxID)=99) AND ((Left(fftweb.Batch,2))='CE'))

 

[PHV]

Why a LEFT join ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jdam]

I updated the syntax to run on SQL server to be:

UPDATE [FFTWeb]
SET FFTWeb.InvoiceDate = [_CEInter].InvoiceDate, FFTWeb.Invoice = [_CEInter].InvoiceNumber
from [_CEInter] INNER JOIN [FFTWeb]
ON ([_CEInter].ACCTDATE = FFTWeb.AM) AND ([_CEInter].VENDOR = FFTWeb.Vendor)
WHERE (((FFTWeb.Invoice) Is Null) AND (([_CEInter].ACCTDATE)= '2005/04' ) AND ((FFTWeb.TrxID)=99)

but it still times out when run via Access Code or previously as an Access Query.

This query updates about 800 records in a table of about 50000 records. ANY suggestions would be helpful.