CODEWITHSUNDEEP
sqlms-accessms-access-2010
Brent
Brent

Reputation: 3

Delete records from multiple Access tables

I have multiple identically formatted tables imported from Excel spreadsheets into Access. The imports bring over empty records that I want to delete. I can delete from a single table using DELETE FROM HTS_01 WHERE TESTS Is Null;

However, if I try to write it to take care of a second table using, 

  DELETE FROM HTS_01 WHERE TESTS Is Null;
  DELETE FROM HTS_0203 WHERE TESTS Is Null;

then I get the error "Characters found after end of SQL statement."

If I remove the semicolon from the first line, I get a syntax error "Syntax error (missing operator) in query expression TESTS Is Null DELETE FROM HTS_030 WHERE TESTS Is Null;"

The problem is that I have 19 tables. I suppose I could write 19 queries and then a short piece of code to execute the queries one by one, but I was trying to avoid that.

Upvotes: 0

Views: 93

Answers (1)

Brent
Brent

Reputation: 3

A co-worker came up with the following, and it worked very well. Thanks for your help!

Sub delete_empty_rows()
' **************************************************************************
' J.K. DeHart
' 3/8/16
' This script will loop through all active tables in the current database and
' remove rows there the defined colmn has 'NULL' data cells
' **************************************************************************

DoCmd.SetWarnings False ' Turn warnings 'Off' for DELETE function

Dim db As Database
Dim tbl As TableDef
Dim fieldName
Dim sqlString As String
Set db = CurrentDb

fieldName = "TESTS" ' Update this value for the driving field

For Each tbl In db.TableDefs
    If tbl.Attributes = 0 Then   'This tells it to ignore hidden tables
        sqlString = "DELETE * FROM " & tbl.Name & " WHERE '" & fieldName & "' Is Null"
        DoCmd.RunSQL (sqlString)
    End If
Next

' Clean up the script
Set tbl = Nothing
Set db = Nothing

DoCmd.SetWarnings True ' Turn warnings back 'On'
End Sub

Upvotes: 0

Related Questions