Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Aug 2002
    Location
    UK
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting tables in an Access database

    I'm just curious, is it at all possible to delete multiple rows of data from an Access database using asp. I've deleted one before, but I have to delete 52 this time.

    I've successfully been able to create 52 records using a for i =1 to 52 loop, would then same principle apply for deleting?

    Cheers,

    ih.

  • #2
    Regular Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts
    if you wanted to delete all data from a table it would be:

    Code:
    Delete from TableName
    USA

  • #3
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    yes, you can delete multiple rows. You can in fact delete all the rows if you like with

    sql="delete * from table"

    if you add a few conditions (for instance: where date = '#13/08/2002#' ) you can delete a set of records that match your criteria.

    you can also do this with a loop, buth that wouln't be the most efficient way to do it (I can immagen that the extra 50 connections will slow things down a bit).

  • #4
    New Coder
    Join Date
    Aug 2002
    Location
    UK
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting tables in an Access database

    What I will have to do is create a session after I've deleted the username from the first table via the asp page then use this variable as the filter for the deletion of the 52 records on the second table.

    So I can just use DELETE * FROM tblWork WHERE tblWork.UserID = " & Session("Uid") ??

    Cheers lads,

    ih.

  • #5
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I don't fully understand your question.

    With that statement, you would delete all records that have that userID.

    As I understand it, you have a form where a userID is registered, and use an ASP page to delete the user + you have another ASP page to delete the records in another table, that were linked to that user (via UserID)

    If that's the case, it would be more efficient to delete the data in both tables with one query (since you can use an iner join between both tables)

    your statement would be something like

    DELETE users.username, tblWork.*
    FROM users INNER JOIN tblWork ON user.userID = tblWork.userID
    WHERE tblWork.UserID = " & Session("Uid") "


    (to generate the exacte sql, you can first create it in design view in access and then paste and modifie it in asp.)

  • #6
    New Coder
    Join Date
    Aug 2002
    Location
    UK
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting tables in an Access database

    Ok, here's the deal...

    I have a small site and on the admin bit one of the options is to delete a member of staff from the system.

    There are two tables on the database that would then need records deleted from it.

    The tblEmployees - coz that holds the users login name, password etc

    The tblWork - coz that holds 52 records each for a week in the year relating to that member of staff.

    Whenever the admin person deletes a member of staff, the site has to delete the one record from the first table and the relating 52 records from the second table.

    During the creation of these records, I had to use sessions to pass identifiers through the create the 52 records in the other table, that's where I was going in the previous post. I just thought I'd have to do the same that's all.

    I shall try what you've said though anyway coz that looks like the best option.


  •  

    Posting Permissions

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