Remove Empty Data Fields

[Craig]

I have a table with 40 fields and import data into it from another source.
Sometimes there could be 50000 records and at other times there is 500
records.

Is there a way to programatically remove fields from the table that do not
not have a data in it.

Like :

if myTable.Season1 is null then Delete myTable.Season1

is this possible in a Loop checking to see if any fields are null.

Thanks
Craig

 

[Jeff Boyce]

Craig

Why?! If you don't have data for a field, you don't put anything in there.
Why would you want to delete the field itself? And what will you do when
what you are importing has MORE fields ... add new ones?

That way lies (maintenance) madness! Every query, form, report, macro and
code snippet that depends on the table being structured one way will break
when you start deleting fields!

If you'll describe what you would be able to accomplish as a result of
deleting those fields, the newsgroup readers may be able to offer an
alternate approach..., one that doesn't impose such a severe maintenance
burden.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Try adding the following procedure to a standard module in the database. It
is fairly crude in that it makes the naïve assumption that any index on the
column in question will be a single column index of the same name as the
column. To cater for all possibilities as regards indexes as constraints
would require quite a lot more code:

Public Sub DropNullColumns(strTable As String)

Const NO_INDEX = 3372
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strSQL As String

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)

For Each fld In tdf.Fields
If DCount(fld.Name, strTable) = 0 Then

' drop index if exists
strSQL = "DROP INDEX " & _
fld.Name & " ON " & strTable
On Error Resume Next
dbs.Execute strSQL
If Err.Number = NO_INDEX Then
' ignore error
Else
' unknown error
If Err.Number <> 0 Then
MsgBox Err.Description, vbExclamation, "Error"
End If
End If

' restore default error handling
On Error Goto 0

' drop column
strSQL = "ALTER TABLE " & strTable & _
" DROP COLUMN " & fld.Name
dbs.Execute strSQL
End If
Next fld

End Sub

Call the procedure, passing the name of the table into it, like so:

DropNullColumns "YourTable"

Ken Sheridan
Stafford, England

 

[Craig]

I know this seems a little unusual, but there is a good reason.

I have to take data from a client system on a regular basis, clean up the
data and format it, then import it into another system.

If I can remove the empty fields either from the table or exporting the data
using a query where there fields are not null, it will save a heap of time
by not having to go through all of the field mapping on the import (which
the import preselects to the best match - badly).

Thanks
Craig

 

[Craig]

Brilliant !!!

This is just perfect. Thanks

Craig

 

[Jeff Boyce]

I think you might have come up with a very viable solution there. Use a
query, not a table.

When you don't have data in particular fields, don't include them in the
query (but leave all the fields in the table).

Regards

Jeff Boyce
Microsoft Office/Access MVP