[dba-SQLServer]Deleting records SQL2000 AXP ADP

David Emerson davide at dalyn.co.nz
Tue May 13 19:33:06 CDT 2003


I have some code which imports a spreadsheet into a table and then deletes 
some of the records which are summaries of the main records.  The records 
are identified by having a null in the sixth field (or text "Last 
Date").  There is not a fixed number of record lines to delete.  The code 
is as follows -

     DoCmd.RunSQL "DELETE FROM ttmpLoadAllocateImp"
     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, 
"ttmpLoadAllocateImp", Me!txtNGCAllocFile
     strSQL = "SELECT ttmpLoadAllocateImp.* FROM ttmpLoadAllocateImp"
     Set rstData = basRunDataObject(strSQL, adCmdText)

     'Delete summary fields
     Do Until rstData.EOF
         If Nz(rstData.Fields(5), "Null") = "Null" Or Nz(rstData.Fields(5), 
"Null") = "Last Date" Then
             rstData.Delete    <-----PROBLEM LINE
         End If
         rstData.MoveNext
     Loop

     rstData.Close
     Set rstData = Nothing

When this is run I get an error -2147467259.  "Key column information is 
insufficient or incorrect.  Too many rows were affected by update".  Can't 
find anything in Books On Line.

My first questions is - Is this the correct way to delete lines in SQL?

Second Question - What dooes the error mean?

Third question - How can I fix it?

Regards

David Emerson
DALYN Software Ltd
25b Cunliffe St, Johnsonville
Wellington, New Zealand
Ph/Fax (877) 456-1205 



More information about the dba-SQLServer mailing list