Stored Procedure Not Updateable? 1

[Notchback]

I have a stroed procedure in an Access 2000 project file as such:

SELECT TNo, AssignedTo, ResolutionInform, SignOffDate
FROM tblMain
WHERE (DATALENGTH(Resolution) < 150) AND (Aborted = 0) AND
(ResolutionInform = 0) AND (NOT (SignOffDate IS NULL))

When I access this through ADO and try to update the ResolutionInform boolean value I get the following message :

&quot;The query is not updateable because the from clause is not a single simple table name&quot;

This message is generally associated with a query that has more than one table. As you can see, my SP only has one table &quot;tblMain&quot;. The ADO code that fails is below :

Dim cnn As New ADODB.Connection
Dim rstSignOffNullCount As New ADODB.Recordset

cnn.ConnectionString = &quot;driver={SQL Server};server=sqltemp;uid=###;pwd=####;database=Helpdesk&quot;

cnn.ConnectionTimeout = 30
cnn.Open

rstMainResInform.CursorType = adOpenKeyset
rstMainResInform.LockType = adLockOptimistic
rstMainResInform.Open &quot;hdsp_MainResInform&quot;, cnn, , , adCmdStoredProc

rstMainResInform.MoveFirst

Do Until rstMainResInform.EOF
rstMainResInform(&quot;resolutioninform&quot = 1
rstMainResInform.Update
rstMainResInform.MoveNext
Loop

rstMainResInform.Close
cnn.Close
Set cnn = Nothing


There obviously is a bit more code within the loop that I have not included due to irrelevence.

Thanks for any advice

Notch. X-) [sig][/sig]

 

[foxdev]

According to Microsoft:

The following conditions cause this error to occur:
You have created a query that contains more than one table.

You are updating the records returned by this query and the update affects fields in more than one table.

Basically, if the query involves tables that have a one-to-many relationship, the query -as a whole- is not updateable.

For additional details, visit:

http://support.microsoft.com/support/kb/articles/Q174/6/40.ASP

[sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]