Find unused fields?

S

SusanV

I have quite a few fields which are no longer used and need to clean them
up. Is there a way via VBA that I can check whether a table has a field in
which all records are null? I know I can use
recordset.fields.delete(fieldname) once I identify them, but this needs to
be done "on the fly."


Thank is advance,

Susan
 
S

SusanV

Hi Allen,

Thanks for your suggestion - that's what i wound up doing with the below
function. However, not every null field is deleted, unless I run it several
times - it seems to get only 2 each time out of perhaps a dozen. Running
this function multiple times eventually gets rid of all the unused fields.
Unfortunately this isn't a one-time cleanup but part of a larger process,
and will have to be done quite often by users who won't want to deal with
checking and re-checking...

Any idea of why it isn't catching all of them? Perhaps it's running too fast
and a timer might help? (Jut a guess there). Code pasted below.

Thanks again,

Susan

''''''''''' code start
Function DelNullFields()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim f As String
Dim sql As String
Dim c As String
Dim t As String

Set db = CurrentDb()
Set tbl = db.TableDefs("ConcatFans")
For Each fld In tbl.Fields
f = fld.Name
sql = "SELECT ConcatFans." & f & " FROM ConcatFans WHERE " _
& f & " Is Not Null"
Set rs = db.OpenRecordset(sql, dbOpenDynaset)
c = rs.RecordCount
Debug.Print c
If c = 0 Then
MsgBox f & " has no records"
Set rs = Nothing
tbl.Fields.Delete (f)
End If
Next fld
End Function
''''''''''' code end
 
A

Allen Browne

Hmm. If you are deleting fields within the loop, the For Each construct may
not work as expected.

Try looping backwards through the fields:
For i = tbf.Fields.Count - 1 to 0 Step -1
f = Fields(i).Name
...
 
S

SusanV

That did it - thanks again!!

Susan

Allen Browne said:
Hmm. If you are deleting fields within the loop, the For Each construct
may not work as expected.

Try looping backwards through the fields:
For i = tbf.Fields.Count - 1 to 0 Step -1
f = Fields(i).Name
...
Click to expand...
 
Top