dcsimg
Results 1 to 9 of 9

Thread: [RESOLVED] SQL Delete?

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Oct 2005
    Posts
    376

    Resolved [RESOLVED] SQL Delete?

    Hey guys... I'd like to delete all records from a table without stepping through the table.

    Using DAO there was an "execute" statement. So you could say Db.Execute "Delete * from MyTable"

    That doesn't seem to work with ADO. I can do the delete by stepping through the records with a While statement but is there a Delete * method I just can't figure out?

    This code words but is there an easier way?

    Code:
       Dim SQLcon As New ADODB.Connection
       Dim MyCustomersSQL As New ADODB.Recordset
       MyString$ = "Driver={SQL Server};Server=MyServer;Database=MyDB;User Id=MyID;Password=MyPW"
    
       SQLcon.ConnectionString = MyString$
       
       SQLcon.Open
       
       MyCustomersSQL.CursorLocation = adUseServer
       
       MyCustomersSQL.Open "Select TOP 500 * from dbo.Cust ", SQLcon, adOpenKeyset, adLockBatchOptimistic, adCmdText
    
       lblDelete.Visible = True
       While Not MyCustomersSQL.EOF
          MyCustomersSQL.Delete
          MyCustomersSQL.Update
          MyCustomersSQL.MoveNext
       Wend
    Thanks for any help in advance!

  2. #2
    Frenzied Member
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    1,581

    Re: SQL Delete?

    Don't use the "*", just try:

    Code:
    DELETE FROM MyTable

  3. #3
    PowerPoster
    Join Date
    Feb 2006
    Posts
    20,921

    Re: SQL Delete?

    Have you looked at the Connection.Execute method?

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Oct 2005
    Posts
    376

    Re: SQL Delete?

    Quote Originally Posted by jpbro View Post
    Don't use the "*", just try:

    Code:
    DELETE FROM MyTable
    Now that sounds promising! Could you show me in a super short code example?

  5. #5
    Frenzied Member
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    1,581

    Re: SQL Delete?

    Code:
    SQLcon.Execute "DELETE FROM dbo.Cust"
    Important note: This will delete ALL of the records from your table as you've asked, not just the top 500 like your SELECT statement shows - so please be careful!

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Oct 2005
    Posts
    376

    Re: SQL Delete?

    Quote Originally Posted by jpbro View Post
    Code:
    SQLcon.Execute "DELETE FROM dbo.Cust"
    Important note: This will delete ALL of the records from your table as you've asked, not just the top 500 like your SELECT statement shows - so please be careful!
    Excellent! It worked perfectly!!

    Now... how do I undelete them?






    Just Kidding!

  7. #7
    Frenzied Member
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    1,581

    Re: SQL Delete?

    Quote Originally Posted by Darkbob View Post
    Now... how do I undelete them?

    Just Kidding!
    You had me for a second

    Glad it's working as required

  8. #8
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,077

    Re: [RESOLVED] SQL Delete?

    If your table is a large one then you may want to consider using the truncate table method as it can be much faster than a delete statement.
    I use this on my large dbs where delete takes a few seconds truncate is pretty much instant.

    I haven't actually used it with VB6 but I would think it should work the same. I use it in vb.net with sql server but since it is just a sql statement I would expect it to work in an ado statement as well.

    Code:
    SQLcon.Execute "truncate table dbo.Cust"

  9. #9
    Frenzied Member wqweto's Avatar
    Join Date
    May 2011
    Posts
    1,798

    Re: [RESOLVED] SQL Delete?

    You cannot TRUNCATE a table if there are FKs referencing it (incl. self-references) and that's usually a larger percent of the tables in any sane DB. . .

    cheers,
    </wqw>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width