The correct way to delete records from a Microsoft Access database. Man was I wrong!



  • So I have this client and she asks if I can review her Access database because she seems to be expierencing some "issues". This is what I see after looking at the code behind the first form that I open. Now mind you, she paid a supposidly Microsoft Access Guru Consultant a considerable amount of money for this. And all this time I thought that I only needed to do execute "DELETE * FROM TABLE;", man was I wrong. I hope I don't have to give refunds! (note: I've replaced the table name with x's)

    'This function will clear the table.

        Public Function ClearRecords() As String

        Dim db As Database
        Dim rec As Recordset
        Dim CurrentTable As String
        Set db = CurrentDb()
        Set rec = db.OpenRecordset("tbl_xxxxxxxxxxxxxx", dbOpenDynaset)

    On Error GoTo ErrorHandling_Resume

     With rec
    .MoveFirst
        Do While Not .EOF
            rec.Delete
        rec.MoveNext
        Loop
    End With

    MsgBox "Records have been successfully deleted!", _
    vbOKOnly + vbExclamation, "Success!"

    ErrorHandling_Resume:
        Resume Next

    End Function



  • SQL is all just one big communist plot to undermine Capitalism. ADO is the one true way to Freedom!



  • I've seen this more than once in my career. Usually from Access gurus who never heard of SQL or dared to learn it. The only thing they knew about queries was Access query designer.

     And to above poster, it's not ADO, it's DAO. If we stick to political comparisons, it's not socialism, it's North Korean communism. ;-)

    Tvrdy 

     



  • <font face="tahoma,arial,helvetica,sans-serif">At least he didn't use On Error Resume Next... Oh wait...</font>

    <font face="tahoma,arial,helvetica,sans-serif">On Error GoTo ErrorHandling_Resume
    ...
    ErrorHandling_Resume:
        Resume Next</font>

    <font face="tahoma,arial,helvetica,sans-serif">Never mind...

    Personally, when I first learned VB and Access at school, I used a similar kind of approach because that's what was taught...</font>

    <font face="tahoma,arial,helvetica,sans-serif">rec.AddNew()
    rec.Fields("col1").Value = txtCol1.Txt
    rec.Update()</font>

    <font face="tahoma,arial,helvetica,sans-serif">But when I learned how to execute SQL Statements besides select, I started using that approach (maybe because I have to type in less code this way than the previous one and I feel "mess-ier" using that approach anyway)...

    This guy probably believed teachers knows best and knew everything (not that I have anything against good teachers), and live by it all his life...



    </font>



  • Funny huh? What about the function as string that returns nothing? Or the CurrentTable variable that is never used?

    I was a bit concerned that he didn't exit the procedure before hitting the error handler. But I suppose, what's the worst that can happen by running on error resume next every time you exit a procedure? Oh, wait a minute....



  • [quote user="hellboy"]

    Funny huh? What about the function as string that returns nothing? Or the CurrentTable variable that is never used?

    I was a bit concerned that he didn't exit the procedure before hitting the error handler. But I suppose, what's the worst that can happen by running on error resume next every time you exit a procedure? Oh, wait a minute....

    [/quote]
    <font face="tahoma,arial,helvetica,sans-serif">While I agree that having a function not returning anything useful as well as having unused variables is bad and usually indicates lousy programming habits, what bothers me most is that his error handling is somewhat the same as On Error Resume Next which I believe is really bad by itself...</font>





  • I'd imagine it was a first-time programmer touting himself as a guru.

    • it's a Function that doesn't return anything. we call that a Sub.
    • using a DAO loop rather than one nice simple SQL statement
    • handling errors by ignoring them (with a bizarre "Goto" / "Resume Next" combo)
    • blindly reporting successful execution
    • then allowing the function to hit the "Goto" portion of the code after it has finished

    Wrong wrong wrong. I'm also confused about why you would want a function that automatically deletes all the records out of a particular table? Don't people like to keep records?

    It might be nitpicky because overall none of it is necessarily terrible, just sloppy. The fact that someone paid for this makes it a WTF in my mind though.

     



  • [quote user="Manni"]

    I'm also confused about why you would want a function that automatically deletes all the records out of a particular table? Don't people like to keep records?

    [/quote]

     

    I'd guess a work table.  Access seems to be big on multiple "populate this table with a variation of what's in the previous table" steps.

     

    Access also has this retarded idea that "DELETE * FROM TABLE" means you should delete the table itself, not just the data within it.  I'm sure there's some simple way to delete just the data, but I intentionally keep my Access knowledge as limited as I can get away with...

     

    (On another note, why is the message editor being all retarded today about spacing and stuff?  I think it went from bah-ro-ken to only-slightly-broken when they upgraded from CS1 to CS2, now it's feeling like it's back in bah-ro-ken territory again.  Honestly, you'd think a Firefox-compatible rich-text editor would be a solved problem by now.)

     



  • [quote user="emurphy"]Access also has this retarded idea that "DELETE * FROM TABLE" means you should delete the table itself, not just the data within it.[/quote]

    I think that's 'The Real WTF' right there ... 



  • [quote user="emurphy"]

    Access also has this retarded idea that "DELETE * FROM TABLE" means you should delete the table itself, not just the data within it.  I'm sure there's some simple way to delete just the data, but I intentionally keep my Access knowledge as limited as I can get away with..

    [/quote]

    I've been forced to work with Access several times in my career.  I really, really doubt that you're correct.  Are you saying that the following statement will drop the table?  I'm sure I've ran this before without losing the table.

    DoCmd.RunSQL  "DELETE FROM foo"

    Also, I'm pretty sure that "Delete *" is illegal syntax, but I'll assume it was kind of a typo.



  • [quote user="emurphy"][quote user="Manni"]

    I'm also confused about why you would want a function that automatically deletes all the records out of a particular table? Don't people like to keep records?

    [/quote]

    I'd guess a work table.  Access seems to be big on multiple "populate this table with a variation of what's in the previous table" steps.

    Access also has this retarded idea that "DELETE * FROM TABLE" means you should delete the table itself, not just the data within it.  I'm sure there's some simple way to delete just the data, but I intentionally keep my Access knowledge as limited as I can get away with...

    [/quote]

    I love when people criticize things they have no clue about.   Why don't you try running "DELETE * FROM Table" in MS Access and let us all know what actually happens*.   Then, you can offer your opinion as to whether or not it is "retarded". 

    As for "populate this table with a variation of what's in the previous table" -- that's up to the programmer.  Nothing about Access forces or even encourages you to do this.  You can write one query after another to maniuplate a recordset and never create a single row in a single table (i.e., you can use queries as derived tables).


    *Hint:  It deletes all rows and not the table.



  • zip -- Access allows for this syntax (DELETE * FROM tbl_x) and it works (but as expected, it does not remove the table, just all rows)

    Jeff is completely right on.



  • [quote user="emurphy"]

    I'd guess a work table.  Access seems to be big on multiple "populate this table with a variation of what's in the previous table" steps.

    [/quote]

    I've been using ASP and VB code to hit up Access and SQL databases for six years now, I've NEVER had to make a work table for temporary data. It's not so much Access that perpetuates the idea that you need temporary tables...it's lazy or uneducated programmers.

    [quote user="emurphy"]

    Access also has this retarded idea that "DELETE * FROM TABLE" means you should delete the table itself, not just the data within it.

    [/quote]

    Just tried it myself, and just as expected with standard SQL syntax, it deleted only the records. If you're talking about a bug in Access v3.0 or something, I can't confirm or deny that. Just that anything including and since Access2000 should interpret the SQL just fine.



  • [quote user="Manni"][quote user="emurphy"]

    I'd guess a work table.  Access seems to be big on multiple "populate this table with a variation of what's in the previous table" steps.

    [/quote]

    I've been using ASP and VB code to hit up Access and SQL databases for six years now, I've NEVER had to make a work table for temporary data. It's not so much Access that perpetuates the idea that you need temporary tables...it's lazy or uneducated programmers.

    [quote user="emurphy"]

    Access also has this retarded idea that "DELETE * FROM TABLE" means you should delete the table itself, not just the data within it.

    [/quote]

    Just tried it myself, and just as expected with standard SQL syntax, it deleted only the records. If you're talking about a bug in Access v3.0 or something, I can't confirm or deny that. Just that anything including and since Access2000 should interpret the SQL just fine.

    [/quote]

     

    100% agreement about the anti-pattern not being inherent to Access, but rather a habit of uneducated programmers.

     

    As for 'delete * from table', I just tested it locally on Access 2000 and it left the table alone - but I was definitely seeing table drops at a client earlier this year, under both 2000 and 2002.  This was part of a macro, and immediately followed by 'select (stuff) into table from (other tables)', so maybe it was really the SELECT INTO that was implicitly dropping and re-creating the table.  (In which case that is a WTF IMO, because an error in that step leaves you with a missing table, which causes a different error on the next attempt.  We went through that drill a number of times.)

     



  • [quote user="emurphy"] 

    100% agreement about the anti-pattern not being inherent to Access, but rather a habit of uneducated programmers.

     
    As for 'delete * from table', I just tested it locally on Access 2000 and it left the table alone - but I was definitely seeing table drops at a client earlier this year, under both 2000 and 2002.  This was part of a macro, and immediately followed by 'select (stuff) into table from (other tables)', so maybe it was really the SELECT INTO that was implicitly dropping and re-creating the table.  (In which case that is a WTF IMO, because an error in that step leaves you with a missing table, which causes a different error on the next attempt.  We went through that drill a number of times.)

     [/quote]

    First off -- there is nothing wrong with using temporary tables if that helps you out. Often it can be more efficient to put things into a temp table, it all depends on what you need to do.  Especially if you are using linked tables -- sometimes you want to bring in some data locally first and then process it, or if you want to temporarly store aggregated data for processing, and so on.

    As for SELECT INTO dropping a table that already exists -- it will only if you explicitly turn off warnings.  Otherewise the statment either fails or displays a warning depending on the context in which it is executed (i.e., JET or the Access application, respectively).  If you want to do things right, you simply check for existance of the table first and then DROP the table before executing the SELECT INTO. 

    Lets consider blaming the programmer and not the tools.  If you don't know how to use the tools and then they work in ways that you didn't expect, that's your fault, right? 



  • [quote user="BrainSlugs83"]

    zip -- Access allows for this syntax (DELETE * FROM tbl_x) and it works (but as expected, it does not remove the table, just all rows)

    Jeff is completely right on.

    [/quote]

    "DELETE * FROM tbl_x" only works as expected if it results in an SQL syntax error.

     Does "DELETE column1, column2, column3 FROM tbl_x" work too?  What does it do?

     But hey, maybe "DELETE * FROM" is an Access extension to the SQL language.  All the other backends I have access to might just be more standard-compliant.  I know the only way anyone would remove my favorite SQL extensions would be over my cold, dead fingers (mmmm...table joins in DELETE queries...).  ;-)




  • [quote user="emurphy"]100% agreement about the anti-pattern not being inherent to Access, but rather a habit of uneducated programmers.[/quote]

    I think this entire site is a result of uneducated programmers.  If all programmers were educated there just wouldn't be any material here...

    A similar problem happens to people who start out with MySQL:

    • Lots of server-side logic in the application, including joins and constraint checks
    • Temporary tables instead of joins or subqueries (there's no views in most of MySQL's history (does it even have them now?), so can't blame 'em for not using them)
    • Concurrency is a bad thing that happens to other people, but not at the same time as it happens to you
    • Transactions?  What are they?  Do I need them?  or even better,  I don't need them!  I just have a "clean up the database" button in the application

    But then it happens to people who move between languages all the time.  C programmers who move to C++ use classes as a kind of struct with convenient syntax for function calls, and they never, ever use exceptions until someone beats them over the head with a stick...but they will initialize each and every one of their variables, always (sometimes in constructor bodies instead of using initializer syntax...but it compiles to the same code anyway so it's good).  Compare with Java programmers moving to C++, who consistently build nightmares of uninitialized objects (especially static member objects) because Java does all the initialization work for you, whether you want it to or not.  Neither of them know how to use a template to save their lives, and they'll all write SQL like an Access programmer until they get a year or two more education.



     



  • [quote user="emurphy"]100% agreement about the anti-pattern not being inherent to Access, but rather a habit of uneducated programmers.[/quote]

    I think this entire site is a result of uneducated programmers.  If all programmers were educated there just wouldn't be any material here...

    A similar problem happens to people who start out with MySQL:

    • Lots of server-side logic in the application, including joins and constraint checks
    • Temporary tables instead of joins or subqueries (there's no views in most of MySQL's history (does it even have them now?), so can't blame 'em for not using them)
    • Concurrency is a bad thing that happens to other people, but not at the same time as it happens to you
    • Transactions?  What are they?  Do I need them?  or even better,  I don't need them!  I just have a "clean up the database" button in the application

    But then it happens to people who move between languages all the time.  C programmers who move to C++ use classes as a kind of struct with convenient syntax for function calls, and they never, ever use exceptions until someone beats them over the head with a stick...but they will initialize each and every one of their variables, always (sometimes in constructor bodies instead of using initializer syntax...but it compiles to the same code anyway so it's good).  Compare with Java programmers moving to C++, who consistently build nightmares of uninitialized objects (especially static member objects) because Java does all the initialization work for you, whether you want it to or not.  Neither of them know how to use a template to save their lives, and they'll all write SQL like an Access programmer until they get a year or two more education.



     



  • There could be a reason for this pattern. The key to the explanation is "Resume Next".

    Let's say that this table contains 1000 records, all of which should be deleted; but for some reasons, like referential constraints, only 970 rows can actually be deleted. If you try "delete from tbl_xxxxxxxxxxxxxxx", the command will fail (for those 30 rows) and, assuming Access is ACID compliant, be completely rolled back.

    By doing this loop with "Resume Next", only those rows remain that cannot be deleted. Depending on the WTFness of the programmer, they will either remain till hell freezes over or someone will have to take care of them manually.



  • [quote user="ammoQ"]

    There could be a reason for this pattern. The key to the explanation is "Resume Next".

    Let's say that this table contains 1000 records, all of which should be deleted; but for some reasons, like referential constraints, only 970 rows can actually be deleted. If you try "delete from tbl_xxxxxxxxxxxxxxx", the command will fail (for those 30 rows) and, assuming Access is ACID compliant, be completely rolled back.

    By doing this loop with "Resume Next", only those rows remain that cannot be deleted. Depending on the WTFness of the programmer, they will either remain till hell freezes over or someone will have to take care of them manually.

    [/quote]

    As usual, depends on how you want to do things.  Access supports transactions if you want to use them.   Of course, it also supports cascading deletes (before SQL Server did) and it certainly is easy to write a DELETE statement that only deletes rows for which there will be no FK conflicts.

     



  • The initial reason for my involvement was to find out why there was consistant duplicate data in the table. I'd rather see an error and know that I have a problem to fix than to just ass-u-me that it's all good. In this case it's a bad assumption, they've been reporting on incorrect data now for several months. The idea here was to clear the whole table as the comment stated.



  • [quote user="Zygo"]A similar problem happens to people who start out with MySQL:

    • Lots of server-side logic in the application, including joins and constraint checks
    • Temporary
      tables instead of joins or subqueries (there's no views in most of
      MySQL's history (does it even have them now?), so can't blame 'em for
      not using them)
    • Concurrency is a bad thing that happens to other people, but not at the same time as it happens to you
    • Transactions? 
      What are they?  Do I need them?  or even better,  I
      don't need them!  I just have a "clean up the database" button in
      the application[/quote]

    Sure you can blame them.I'm sure
    they weren't forced to use MySQL. You don't blame someone for doing a
    bad job of a haircut with a lawnmower - that's to be expected - but you
    sure as heck blame them for using the lawnmower to start with.

     I don't know why it's as popular as it is. Sure it's a bit faster than the others at some things, but then it's easy to be fast when you're only handling 10% of the capabilities of your bigger brothers. Both the free and commercial worlds offer superior products. Heck, every one of the commercial big boys have their own freebie version too if you want a bit of both worlds.

    Before anyone chimes in with "but MySQL is better now than it was!" that's true. It's also true that it still falls short. 



  • [quote user="Zygo"]

    A similar problem happens to people who start out with MySQL:

    • Lots of server-side logic in the application, including joins and constraint checks
    • Temporary tables instead of joins or subqueries (there's no views in most of MySQL's history (does it even have them now?), so can't blame 'em for not using them)
    • Concurrency is a bad thing that happens to other people, but not at the same time as it happens to you
    • Transactions?  What are they?  Do I need them?  or even better,  I don't need them!  I just have a "clean up the database" button in the application

    [/quote]

    I use MySQL for a production database right now that can best be described as a data warehouse.  The primary selling point, of course, was that it costs less than $50,000 (which is what it would cost to put DB2 on all the machines it runs on - SQL Server isn't viable, because it's Windows-only, and Oracle would cost at least twice as much).  I don't think it's as capable as the older commercial products.  But it's certainly not the broken toy that some DB snobs seem to think it is.

    I happen to be on the side of the fence that thinks databases should store data, and that business logic belongs outside.  But that's debatable.

    Your second point, however, doesn't make a lot of sense.   Why on earth would you need temporary tables to overcome the lack of views?  Joins and subqueries work quite well without being abstracted by a view, which amounts to nothing more than saving a bit of typing once you discount security (and if you're relying on DB security for controlling access to data, you're a WTF waiting to happen).  Or worse.  Ever see a query that runs quitequickly when run directly on the tables, but turns into a dog when done on a view instead?

    Concurrency?  Not sure what to make of that vague statement.

    MySQL supports transactions, and has for over five years now.  Or did you miss all those DB benchmarks done by the IT rags?  You know, the ones where MySQL ends up consistently near the top in transactional performance.

    There is a huge market where using a commercial DBMS would double the budget (if not worse).  In that market, MySQL does a nice job, indeed.  For example, I can spend $6K on a server that handles 100 concurrent users without breaking a sweat.  I'd have to spend $10K to put a commercial DBMS on it.


  • 🔀

    @discobot add 46b12662-e348-4258-93cd-2e0c6753a59b 46b12662-e348-4258-93cd-2e0c6753a59b


  • Banned

    Goddamnit @error!


  • Considered Harmful

    It was quite intentional that I necro'd this thread and not at all because I confused a reply ID with a topic ID.


  • Notification Spam Recipient

    @emurphy said in The correct way to delete records from a Microsoft Access database. Man was I wrong!:

    (On another note, why is the message editor being all retarded today about spacing and stuff?  I think it went from bah-ro-ken to only-slightly-broken when they upgraded from CS1 to CS2, now it's feeling like it's back in bah-ro-ken territory again.  Honestly, you'd think a Firefox-compatible rich-text editor would be a solved problem by now.)

    (from the future) I have some extremely terrible news for you...


  • Notification Spam Recipient

    @emurphy said in The correct way to delete records from a Microsoft Access database. Man was I wrong!:

    As for 'delete * from table', I just tested it locally on Access 2000 and it left the table alone - but I was definitely seeing table drops at a client earlier this year, under both 2000 and 2002.  This was part of a macro, and immediately followed by 'select (stuff) into table from (other tables)', so maybe it was really the SELECT INTO that was implicitly dropping and re-creating the table.  (In which case that is a WTF IMO, because an error in that step leaves you with a missing table, which causes a different error on the next attempt.  We went through that drill a number of times.)

    Yeah, SELECT INTO should NOT drop tables at any time. The fact that it apparently did is a :wtf: without recourse.




  • Notification Spam Recipient




  • Java Dev

    Are we not yet out of 10-year-old topics to perform unholy rituals on?


  • Notification Spam Recipient

    @PleegWat said in The correct way to delete records from a Microsoft Access database. Man was I wrong!:

    Are we not yet out of 10-year-old topics to perform unholy rituals on?

    You don't understand how necro works, do you?


Log in to reply