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.
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments