我有多个从Excel电子表格导入到Access的格式相同的表。导入会带来我要删除的空记录。我可以使用从单个表中删除DELETE FROM HTS_01 WHERE TESTS Is Null;
但是,如果我尝试使用它编写它来照顾第二张桌子,
DELETE FROM HTS_01 WHERE TESTS Is Null;
DELETE FROM HTS_0203 WHERE TESTS Is Null;
然后出现错误“ SQL语句结束后找到字符”。
如果我从第一行中删除分号,则会出现语法错误“查询表达式TESTS为空,语法错误(缺少运算符),从HTS_030中删除,而TESTS为空;”
问题是我有19张桌子。我想我可以编写19个查询,然后编写一小段代码来逐个执行查询,但是我试图避免这种情况。
一位同事提出了以下建议,并且效果很好。谢谢你的帮助!
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
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句