You could use instead of triggers.

from the books online

INSTEAD OF INSERT Triggers

INSTEAD OF INSERT triggers can be defined on a view or table to replace the standard action of...

Are you trying to reapply a servicepack since you are already running

Sql server 2000 + sql sp4?

From a certain mdac the odbc-driver for Access was dropped.

Since then you have to use the jet-driver.

What version of Access are you using?

Are you using ado, dao,...?

*You could add some debugging code in your stored procedure.

Like adding a new row containing some state info.

*How is your server built up? OS, sql server and patch level,...

You have a few options:

SET PARSEONLY (connection setting)

SET NOEXEC (connection setting)

SET SHOWPLAN_TEXT (connection setting)

SET SHOWPLAN_ALL (connection setting)

What they do is described in the books online (BOL)

Can you provide us some background info.

Does it happen only on your pc?

What connectiontechnology are you using?

What version of sqlserver + patch level, on what OS and hardware does it...

The command you are looking for is SELECT INTO (see the books online)

Example

SELECT Shippers.*, Link.Address, Link.City,               ...

Because sysindexes.rows,rowcnt  does not contain real-time data.

I presume it is updated with the statistics.

What technology you use to get your data ADO,DAO,.NET?

Is this in VB6,Access,.NET?

One of the 3 parameters of  DLookup is probably the value you wish to look up.

*have a look at...

1) are the objects in the extended stored procedure properly released?

2) Sql server has a memtoleave area for extended procedures, perhaps it gets exhausted.

3) Is the cursor forward-only,read-only to minimise...

assuming access97

dim rs as dao.recordset

dim sqlstring as string

sqlstring="SELECT clinicianid from qryPatientCheck where ClinicianID= " & Me.ClinicianID.Value

set rs=currentdb.openrecordset (sqlstring)

if rs.recordcount>0 then

'found

else 'not found

end if

rs.close

set rs=nothing

Just a note

It assumes that FirstTreatment is always later than AssesmentDate.

Datediff is indeed more appropriate.

When you install sql server 2000 on a windows 2003 machine

ignore the warning that servicepack 3 is required.

Install sql server 2000, install servicepack 3 or higher. (otherwise 2003 only allows connections...

what about

v=year([FirstTreatment])

w=year([AssesmentDate])

TimeOnWaitingList: ([x]-[y])+((v-w)*12)