Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    May 2005
    Posts
    126
    Provided Answers: 1

    Question Unanswered: Help required with code

    Hi, I'm a real noob when it comes to code , but I was hoping someone could point me in the right direction.

    Version of Access 2003

    What I'm after if its possible, the database I am developing gathers data from a sql server, and the data is temporally being held in quite a few tables, what I want to happen is that the end user clears out all temp data. At present I have created delete queries, and a macro that runs them all. Getting monotonous now

    But I would like to get away from creating multiple delete queries and have some code that when called deletes all data held within the required tables. E.G.

    Pri function ClearAllTempTables

    “Delete * from Tbl 1
    “Delete * from Tbl 2
    “Delete * from Tbl 3
    “Delete * from Tbl 4

    End function

    So if I need to create another temp table, I can add it to the code for the data to be deleted

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    AFAIK deleting from multiple tables isn't possible unless you have delete cascade on
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2005
    Posts
    126
    Provided Answers: 1
    Tnx Healdem

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You can just run VBA delete queries inside your VBA:

    Code:
    CurrentDB.Execute "DELETE * FROM TBL1;", dbFailOnError
    CurrentDB.Execute "DELETE * FROM TBL2;", dbFailOnError
    CurrentDB.Execute "DELETE * FROM TBL3;", dbFailOnError
    CurrentDB.Execute "DELETE * FROM TBL4;", dbFailOnError
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If it is getting tedious, presumably you are dynamically building tables. How come? Why not a fixed schema?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    May 2005
    Posts
    126
    Provided Answers: 1
    Both actions work guy's

    Thankyou very much

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're welcome
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    May 2005
    Posts
    126
    Provided Answers: 1
    Quote Originally Posted by pootle flump
    If it is getting tedious, presumably you are dynamically building tables. How come? Why not a fixed schema?
    Could you please explain "fixed Schema" pootle flump

    Thanks

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    fixed schema means no temp tables, draw down data as required from the DB using queries.
    no tables created on the fly (at run time)
    so no need to delete
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151

    One more suggestion....

    If you store the names of the tables that need deleting in a new table, you can then create a recordset of them and then loop thru it and use only one delete statement. Then the only maintenance needed would be to add or delete the table name from the new table.

    Just a thought.
    --If its free, take it for what its worth!

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just to be picky - you'd need one delete statement per table still, but you'd be using (icky) dynamic SQL to help out.
    George
    Home | Blog

  12. #12
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151

    Icky?

    Really? The only 'dynamic' would be the table name and I think that would be a whole lot easier than having a delete statement for each table. Is there an easier way? I'm always open to suggestions.

    Thanx, Stu
    --If its free, take it for what its worth!

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Nipping the problem in the bud would be removing the truncation and population of tables and replacing it with a lovely merge instead

    Obviously this requires a fair bit more thinking (deleting and re-adding stuff is easy-peasy) but the ability to define RI on the "import" tables would make it worthwhile in my head

    And the reason we don't like dynamic SQL is that it is prone to SQL Injection.

    As for an easier way which is not prone to dynamic SQL - replace the table with an array in VBA - a bit more secure but obviously requires a code change to maintain (maybe that's a good thing too, leaves the administration to us lot instead of them!). We certainly don't want someone entering a line into our table of "employees" do we?
    George
    Home | Blog

  14. #14
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151

    Hummm....

    Valid points. I shall consider. Do you have any more of those cookies you promised Star Trekker?
    --If its free, take it for what its worth!

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Cookies are still available; just send £5 in a stamped, self-addressed envelope to me and I'll get one out in the posat to you by the end of the week

    Alternatively, I'm offering up e-cookies (formerly known as the points system) for anything that takes my fancy.

    +1 e-cookie for putting the idea in my head
    George
    Home | Blog

Posting Permissions

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