Deleting tables in an Access database

[ihitman]

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.

[landon11]

if you wanted to delete all data from a table it would be:

Delete from TableName

[raf]

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).

[ihitman]

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.

[raf]

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.)

[ihitman]

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.