Delete command on various tables

[mane_uk]

Hi all,

I have about 10 tables interlinked. Their name start by "tblActivity_..." and a common field is the "activityID".

I am trying to create a code to delete a specific activityID from all the tables.

I am using the following code where "rsActivity.Fields("activityID")" is the activityID I want to delete:

          For Each tdf In db.TableDefs
                ' If Connect property is non-empty, table will be checked.
                If Len(tdf.Connect) > 0 Then
                    'Will check if tbl is an Activity table
                    If Left$(tdf.name, 11) = "tblActivity" Then
                        ' Set Delete command
                        strDelete = "DELETE FROM " & tdf.name & " WHERE [activityID] = " & rsActivity.Fields("activityID")
                        'Change the setting to False so don't ask to confirm deleting line
                        DoCmd.SetWarnings False
                        'execute delete command
                        DoCmd.RunSQL strDelete
                        'back to normal
                        DoCmd.SetWarnings True
                    End If
                End If
            Next tdf

The first table it works OK but then, on the secod table, it returns the error 3167 Record is Deleted when running the strDelete="DELETE FROM...." line . Any ideas why?

Many thanks
Emanuel

 

[Galaxiom]

The recordset rsActivity is based on the table where you execute the first delete. Hence the "Record is Deleted" message when the code tries to read it again.

Copy the value from the recordset to a variable which will persist throughout the procedure. That is more efficient than repeatedly reading the recordset anyway.

Also note you can refer to a value field in a recordset as rsSomename!fieldname

I would also suggest you stop pointlessly commenting the plainly obvious. Comments are supposed to enhance the readability of the code and what you are doing is a distraction.

 

[JamesMcS]

Might be worth putting in "debug.print tbl.name" before your delete query is executed, make sure it's moving on to another table. Also I'm not an SQL genius but I think it should be

DELETE [" & tdf.name & "].* FROM [" & tdf.name & "] WHERE.....

 

[mane_uk]

The recordset rsActivity is based on the table where you execute the first delete. Hence the "Record is Deleted" message when the code tries to read it again.

Copy the value from the recordset to a variable which will persist throughout the procedure. That is more efficient than repeatedly reading the recordset anyway.

Also note you can refer to a value field in a recordset as rsSomename!fieldname

I would also suggest you stop pointlessly commenting the plainly obvious. Comments are supposed to enhance the readability of the code and what you are doing is a distraction.

Thanks Galaxiom... it worked perfectly when I changed the recordset to a variable. And nice tip about the recordset!fieldname, makes it a lot easier.
The comments, unfortunately I need to add as I am leaving soon and the person who will be taking over don't have much knowledge of VBA so I do need to get even the obvious commented on, which is a pain...
Thanks for your help!!

 

[Galaxiom]

James:

The tablenames for the field references in queries only need to be used if the fieldname is ambiguous because it could be from different tables.

Sure makes for tidier code when you can leave them out.

Of course Access adds them back in (along with its fetish for parentheses) if you go to design view and save.

 

[JamesMcS]

Ah I get ya, I thought the docmd.runsql method had to include them - it was more the "delete * from tablename" bit I was going for anyway, I thought that had to be specified as well. Obviously not if it worked the first time though... ignore me, I'm an eejit