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?

Issue one delete per table. Or, you might even want to just drop the tables instead. — Gordon Linoff
– Gordon Linoff, Commented May 30, 2017 at 20:59
truncate is the fastest but will reset your auto incremented fields... but have to be per table — RoMEoMusTDiE
– RoMEoMusTDiE, Commented May 30, 2017 at 21:04
if you want to only use one sql command then create a stored procedure that contains truncation/deletion of data per table... call the stored proc in vb to execute — RoMEoMusTDiE
– RoMEoMusTDiE, Commented May 30, 2017 at 21:05

Parfait · Accepted Answer · 2017-05-30 21:36:02Z

6

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)

answered May 30, 2017 at 21:36

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

STLDev Over a year ago

What? No Entity Framework? One more thing - shouldn't these deletes be done within a transaction?

Parfait Over a year ago

Transactions can be employed. Depends on OP's needs. Open source versions can be ignored. Future readers may find it helpful.

Parfait Over a year ago

Great! Please accept solution (tick mark to side) to confirm resolution. Alternatives here is to dispel the myth that MS Access only works with VBA.

Greencolor Over a year ago

@Parfait Could you please provide or give a link where it shows the python code which imports the files from folder to access db ( similar to DoCmd.TransferText in VBA)

Parfait Over a year ago

Actually, @Greencolor. DoCmd.TransferText is part of MS Access object library (not VBA). In fact, Python like VBA can interface to such methods via win32com which is not described in this solution. See my multiple answers on the process.

|

Collectives™ on Stack Overflow

MS-Access query to DELETE * FROM multiple tables

1 Answer 1

OPEN SOURCE ALTERNATIVES

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

OPEN SOURCE ALTERNATIVES

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related