Updated Access VBA code to delete empty records
January 13, 2010 • JP • No Comments • Rate This Article
• Links to this article
I recently had to use the code I posted in Clean and Repair Your Access Database to delete blank records from a database. But it failed because one of the tables has spaces in the name. I didn't create the table, I swear!
So here is the revised version that works when you have tables with OR without spaces in the name.
Sub DeleteBlankRecords()
' loop through each table and delete blank records
On Error GoTo ErrorHandler
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim currentTable As String
Dim currentField() As String
Dim fieldCount As Long
Dim i As Long
Dim strSQL As String
Set db = GetCurrentDB
For Each tbl In db.TableDefs
If (tbl.Attributes = 0) Then ' not a system table, OK to proceed
currentTable = tbl.Name
' build string consisting of all fields in specified table
fieldCount = tbl.Fields.Count
ReDim currentField(1 To fieldCount)
For i = 0 To fieldCount - 1
currentField(i + 1) = tbl.Fields(i).Name
Next i
strSQL = "DELETE [" & currentTable & "].*"
For i = 1 To fieldCount
strSQL = strSQL & ",[" & currentTable & "].[" & currentField(i) & "]"
Next i
strSQL = strSQL & " FROM [" & currentTable & "] WHERE ("
For i = 1 To fieldCount
If i = 1 Then
strSQL = strSQL & "(([" & currentTable & "].[" & currentField(i) & "]) Is Null)"
Else
strSQL = strSQL & " AND (([" & currentTable & "].[" & currentField(i) & "]) Is Null)"
End If
Next i
strSQL = strSQL & ");"
Debug.Print strSQL
' execute SQL delete query
Call ExecSQL(strSQL)
End If
Next tbl
ProgramExit:
Exit Sub
ErrorHandler:
MsgBox Err.Number & " - " & Err.Description
Resume ProgramExit
End Sub
' loop through each table and delete blank records
On Error GoTo ErrorHandler
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim currentTable As String
Dim currentField() As String
Dim fieldCount As Long
Dim i As Long
Dim strSQL As String
Set db = GetCurrentDB
For Each tbl In db.TableDefs
If (tbl.Attributes = 0) Then ' not a system table, OK to proceed
currentTable = tbl.Name
' build string consisting of all fields in specified table
fieldCount = tbl.Fields.Count
ReDim currentField(1 To fieldCount)
For i = 0 To fieldCount - 1
currentField(i + 1) = tbl.Fields(i).Name
Next i
strSQL = "DELETE [" & currentTable & "].*"
For i = 1 To fieldCount
strSQL = strSQL & ",[" & currentTable & "].[" & currentField(i) & "]"
Next i
strSQL = strSQL & " FROM [" & currentTable & "] WHERE ("
For i = 1 To fieldCount
If i = 1 Then
strSQL = strSQL & "(([" & currentTable & "].[" & currentField(i) & "]) Is Null)"
Else
strSQL = strSQL & " AND (([" & currentTable & "].[" & currentField(i) & "]) Is Null)"
End If
Next i
strSQL = strSQL & ");"
Debug.Print strSQL
' execute SQL delete query
Call ExecSQL(strSQL)
End If
Next tbl
ProgramExit:
Exit Sub
ErrorHandler:
MsgBox Err.Number & " - " & Err.Description
Resume ProgramExit
End Sub
Access ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Are classic Office 2003 menus already making a comeback?
Next Post: Another take on Data Validation
Speak Your Mind
Tell us what you're thinking...Certain comments (including first-time comments) are subject to moderation and will not appear immediately. Please view the Comment Policy for more information. To post VBA code in your comment, use tags like this: [cc lang='vb']Code goes here[/cc].