Accress 2000 ADO Addnew Problem

[jayplus707]

Hi,

Has anyone had problems with using AddNew in Access 2000 with ADO? I am getting inconsistent results. Sometimes it adds data like it should and other times it doesn't. I put debug.print to make sure the .update was executed so I know it went through the code. However, sometimes data did not add to a local table. I am trying to generate report records before opening a report. The code I use follows.

Thanks,

LLC

Dim rsResults As ADODB.Recordset
Dim rsRptRcds As ADODB.Recordset
Dim intCounter As Integer

gCnn.Execute ("Delete from tblIndivResultsLocal&quot

Set rsRptRcds = New ADODB.Recordset
rsRptRcds.Open "tblIndivResultsLocal", gCnn, adOpenKeyset, adLockOptimistic


Set rsResults = New ADODB.Recordset
rsResults.Open "tblIndivTestResultsForAppLocal", gCnn

'Generate report records.
Do Until rsResults.EOF = True
If rsResults!Missed = 0 Then
rsRptRcds.AddNew
rsRptRcds!TestResultsID = rsResults!TestResultsID
rsRptRcds!Result = rsResults!Result
rsRptRcds.Update
Debug.Print "update missed 0"
Else
intCounter = 1
Do Until intCounter > rsResults!NoOfQuestions
If rsResults("M" & intCounter) = True Then
rsRptRcds.AddNew
rsRptRcds!TestResultsID = rsResults!TestResultsID
rsRptRcds!Result = rsResults!Result
rsRptRcds!DeficiencyID = rsResults("D" & intCounter)
rsRptRcds.Update
Debug.Print "update missed > 0"
End If
intCounter = intCounter + 1
Loop
End If
rsResults.MoveNext
Loop

rsRptRcds.Close
rsResults.Close
Set rsRptRcds = Nothing
Set rsResults = Nothing

 

[chrissie1]

try sql code works a lot faster

replace this

rsRptRcds.AddNew
rsRptRcds!TestResultsID = rsResults!TestResultsID
rsRptRcds!Result = rsResults!Result
rsRptRcds.Update
Debug.Print "update missed 0"

with
docmd.runsql "INSERT INTO tblIndivResultsLocal (testresultsid,result) VALUES (" & rsResults!TestResultsID & "," & rsResults!Result & &quot"


and do the same or similar with the other addnew
Christiaan Baes
Belgium
"What a wonderfull world" - Louis armstrong

 

[cmmrfrds]

ADO knows from the Open parms that the recordset is updatable. AddNew is either redundant or not needed.

'- Check these parms.
rsRptRcds.Open "tblIndivResultsLocal", gCnn, adOpenKeyset, adLockOptimistic

'- try making a client side cursor (which will force static)
'- the default cursor is server side.
'- leave off the addnew

rsRptRcds.CursorLocation = adUseClient
'- 3 is static cursor (is redundant but documents)
rsRptRcds.Open "tblIndivResultsLocal", gCnn, 3, adLockOptimistic