[dba-SQLServer]Deleting records SQL2000 AXP ADP

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