[dba-SQLServer]Deleting records SQL2000 AXP ADP

Thanks Mark.  Works much better - simpler to!

David

At 14/05/2003, you wrote:
>David -
>I would use queries to delete the records.
>Try 'DELETE * FROM ttmpLoadAllocateImp WHERE (((Field5) Is Null)) OR
>(((Field5)="Last Date"))'.
>
>Mark Boyd
>Sr. Systems Analyst
>McBee Associates, Inc
>
>
>-----Original Message-----
>From: David Emerson [mailto:davide at dalyn.co.nz]
>Sent: Tuesday, May 13, 2003 8:33 PM
>To: dba-SQLServer at databaseadvisors.com
>Subject: [dba-SQLServer]Deleting records SQL2000 AXP ADP
>
>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
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com