Cant update Recordset

[saintedmunds]

Hi

I have the following function in an access project.

But it wont update does any one now why please.

Dim cmd As New adodb.Command
Dim param1 As adodb.Parameter
Dim param2 As adodb.Parameter
Dim cnn As adodb.Connection
Dim RS As adodb.Recordset
Set cnn = CurrentProject.Connection
Set RS = New adodb.Recordset

Dim intRank As String
Dim intCount As Integer
Dim intOldScore As Integer

With cmd
.ActiveConnection = cnn
With RS
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.CursorLocation = adUseServer
End With

Set param1 = .CreateParameter("@TermID", adInteger, adParamInput)
Set param2 = .CreateParameter("@FormsExamsID", adInteger, adParamInput)
.Parameters.Append param1
.Parameters.Append param2
param1.Value = fnTermID()
param2.Value = fnFormsExamsID()

.CommandType = adCmdStoredProc
.CommandText = strQueryName
Set RS = .Execute
End With

RS.Sort = strScoreFldName & " DESC"
intRank = 1

Do While Not RS.EOF
If RS.Fields(strScoreFldName) = 0 Then
Exit Do
End If
intCount = intCount + 1
If intOldScore = RS.Fields(strScoreFldName) Then
RS.MovePrevious
If Not Right(RS.Fields(strRankFldName), 1) = "=" Then
RS.Fields(strRankFldName) = RS.Fields(strRankFldName) & "="
RS.Update
End If
RS.MoveNext
End If

If Not intOldScore = RS.Fields(strScoreFldName) Then
intRank = intCount
Else
intRank = intRank & "="
End If

RS.Fields(strRankFldName) = intRank "ERROR HERE"

If Right(intRank, 1) = "=" Then
intRank = Left(intRank, Len(intRank) - 1)
End If
RS.Update
intOldScore = RS.Fields(strScoreFldName)
RS.MoveNext
Loop
RS.Close
Set RS = Nothing
Set cnn = Nothing
Me.Requery
End Function

Cheers

 

[cmmrfrds]

For starters change these to be.

With RS
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.CursorLocation = adUseClient

 

[saintedmunds]

HI There

THank you for you reply but it hasnt made any difference.
Is there any way of setting the uniquetable in a recordset maybe that is what causing the problem?

Cheers

 

[JimFive]

Your CommandType is a StoredProcedure, so your recordset is not updatable. You need to have a Table or Updatable view to update data this way.

JimFive.

 

[LTusing]

I am having the same problem. I have an Access (.adp) project and all forms are unbound. My tables are located on SQL Server 2000 and communicate to Access Forms via ODBC/ADO. I have changed the property setting 'uniquetable' to the appropriate recordset for each form. Fields of tables on the 1 side of a 1:M relationship are updatable, however forms based on the many side of relationship are not updateable, they are read-only. The SQL Server Developers Guide notes several options of which I am not sure of, 1) adding a sql statement at Resync Command 2)binding form to the recordset 3) stored procedure transact sql and cmd statement calling the stored procedure with parameters; or using cursors, Views, and temp tables. I am looking for recommendations. Thank you!

 

[saintedmunds]

HI

This is how i got around the problem.

A Stored Procedure is updateable it was when i was using cmd.execute that was causing the problem but i need to use this to pass the parameters. The way i am now doing it is below:

Dim SQLString As String
SQLString = "SELECT tStudents.StudentSurname, LEFT(tStudents.StudentForename, 1) AS Initial, tExamResults.English1Score, tExamResults.English1Position, "
SQLString = SQLString & "tExamResults.Maths1Score, tExamResults.Maths1Position, tExamResults.ScienceScore, tExamResults.SciencePosition, tExamResults.HistoryScore, "
SQLString = SQLString & "tExamResults.HistoryPosition, tExamResults.GeographyScore, tExamResults.GeographyPosition, tExamResults.FrenchScore, "
SQLString = SQLString & "tExamResults.FrenchPosition, tExamResults.RSScore, tExamResults.RSPosition, tExamResults.AV, tExamResults.POS, tExamResults.CoreAV, "
SQLString = SQLString & "tExamResults.RA , tExamResults.PIPS, tExamResults.TermID, tExamResults.StudentID, tStudents.FormsExamsID, tStudents.HasLeft "
SQLString = SQLString & "FROM tExamResults INNER JOIN "
SQLString = SQLString & "tStudents ON tExamResults.StudentID = tStudents.StudentID "
SQLString = SQLString & "WHERE (tExamResults.TermID =" & fnTermID() & ") AND (tStudents.FormsExamsID = " & fnFormsExamsID() & ") AND (tStudents.HasLeft = 0) "
SQLString = SQLString & "ORDER BY tStudents.StudentSurname, Initial"

With RS
.ActiveConnection = cnn
.Source = SQLString
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.CursorLocation = adUseServer
.Open
End With

I do the select as a string so my Params are in there.

Not ideal but works for what I need.

Thank you all for your input