Pass table names to delete query (1 Viewer)

[foxtrot123]

I use the following code to delete from a table all records except those meeting the WHERE criteria:

DELETE tblABC.*, tblABC.SubjectID
FROM tblABC
WHERE (((tblABC.SubjectID)<>99 And (tblABC.SubjectID)<>432));

I'd like to run this exact same query, but on many other tables, all of which are stored in tblTablesToClean (TableID, TableName).

Does anyone know a good way to have Access loop through the list of tables in tblTablesToClean, each time passing the name of the table into the DELETE code and running the code, until all tables have been processed?

Thank you.

 

[Galaxiom]

Your goal can only be achieved by building dynamic queries in VBA.

However, your description strongly suggests your data structure is incorrect.

If I am not mistaken then all that data should be in the same table with another field to record whatever it was that made you decide to put them into different tables.

Once you make that change you will only need one query.

 

[foxtrot123]

Your goal can only be achieved by building dynamic queries in VBA.

However, your description strongly suggests your data structure is incorrect.

I realize the table structure departs from normal form, but these are temporary tables that are created through a bunch of routines designed to create separate tables for use in statistical software.

A dynamic query in VBA is exactly what I'm trying to figure out. Below is the very rusty code I came up with, but it's not passing the name of the table(s) to the query (it's just returning the actual string, "TableName," instead of the table name). To test this, I'm using a very simple SQL query (i.e., no where criteria). I suspect the problem is the & " " stuff, which always trips me up.

Suggestions?

Public Function CleanTables()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb()
Set rs = CurrentDb.OpenRecordset("tblTablesToClean")

DoCmd.SetWarnings False

Do Until rs.EOF

strSQL = "DELETE rs(TableName).*, rs(TableName).SubjectID " & _
    "FROM rs(TableName);"

DoCmd.RunSQL strSQL

Loop

DoCmd.SetWarnings True

Set rs = Nothing
Set db = Nothing
    
End Function

 

[vbaInet]

Here you are:

Public Function CleanTables()
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT * " & _
                              "FROM tblTablesToClean", _
                              dbOpenSnapshot, dbReadOnly)
    
    With rs
        Do Until .EOF
            db.Execute "DELETE * " & _
                       "FROM " & ![COLOR="Blue"]TableName [/COLOR]& ";"
            .MoveNext
        Loop
    End With
    
    Set rs = Nothing
    Set db = Nothing
    
End Function

 

[nanscombe]

strSQL = "DELETE rs(TableName).*, rs(TableName).SubjectID " & _
    "FROM rs(TableName);"

If you are deleting the whole record I don't know that you need the extra ".SubjectID" part.

It could be simplified as ...

strSQL = "DELETE * FROM [" & rs(TableName) & "];"

I have included "[" and "]" in case there are any naughty table names with spaces in them.

 

[MarkK]

A delete query works on rows, so you don't need any kind of field identifiers . . .

DELETE FROM tblTable;

. . . will work.