MS-Access query to DELETE * FROM multiple tables

Question

In my Access database I have 15 tables. I want to perform [DELETE * FROM table4, table7, table8, table9, table10, table11]. That does not work, but when I run the query with one table it works, it deletes all the records in that table. I would like it to work for more than one table in a single query or in a single Visual Basic module.

Maybe I don't understand SQL and maybe this has to be done with Visual Basic?

Answer 1

MS Access' stored SQL queries only allow one DML or DDL statement at a time. For iterative actions, consider running action query in a loop using application layer code such as VBA or any language that can ODBC connect to .mdb/.accdb file. Also, DELETE does not need asterisk in MS Access SQL.

VBA (using built-in DAO CurrentDb object)

Dim var as Variant

For Each var In Array("table4", "table7", "table8", "table9", "table10", "table11")
   CurrentDb.Execute "DELETE FROM " & var, dbFailOnError
Next var

---

OPEN SOURCE ALTERNATIVES

Python (using pyodbc module)

import pyodbc

database = 'C:\\Path\\To\\Database\\File.accdb'
constr = "Driver={{Microsoft Access Driver (*.mdb, *.accdb)}};DBQ={0};".format(database)

db = pyodbc.connect(constr)
cur = db.cursor()

for i in ["table4", "table7", "table8", "table9", "table10", "table11"]:
    cur.execute("DELETE FROM {}".format(i))
    db.commit()

cur.close()
db.close()

PHP (ensure pdo_odbc is uncommented in .ini file)

$database="C:\Path\To\Database\File.accdb";

try {
  $dbh = new PDO("odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=$database;");

  foreach(array("table4", "table7", "table8", "table9", "table10", "table11") as $tbl){
       $sql = "DELETE FROM ".$tbl;    
       $STH = $dbh->query($sql);    
  }
}
catch(PDOException $e) {  
  echo $e->getMessage()."\n";
  exit;
}

$dbh = null;

R (using RDOBC package)

library(RODBC)

database <- "C:\\Path\\To\\Database\\File.accdb"
conn <- odbcDriverConnect(paste0('Driver={Microsoft Access Driver (*.mdb, *.accdb)};
                                  DBQ=', database))

lapply(c("table4", "table7", "table8", "table9", "table10", "table11"),
          function(t) sqlQuery(conn, paste0("DELETE FROM ", t)))

close(conn)