Need Access VBA code to drop empty fields in a table

[JFKJr]

Hello everyone, I have imported an excel file into an Access table, but the table contains some empty fields, does anyone know how to delete those empty fields using Access VBA?

Thanks in Advance.

[ADezii]

This one was a little challenging, but here it goes. The following code will loop through all Fields in a Recordset, in this case one based on an Employees Table, and will list to the Immediate Window 'ALL' Fields which have 'NO' values in 'ALL' the Records in the Recordset. To subsequently DELETE those Fields would be even trickier yet since you cannot use the Delete Method on a member of the Fields Collection, and you cannot Delete a Field using SQL, DAO, ADO, etc. if it is contained in an active Recordset. Should you really need this functionality, I'm sure that I can accommodate you, just let me know.

Dim MyDB As Database
Dim MyRS As DAO.Recordset
Dim intFieldCounter As Integer
Dim intNumOfFields As Integer
Dim intNumOfRecords As Integer
Dim intNumOfEmptyFields As Integer
 
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("Employees", dbOpenDynaset)
 
MyRS.MoveLast: MyRS.MoveFirst
 
intNumOfFields = MyRS.Fields.Count
intNumOfRecords = MyRS.RecordCount
 
For intFieldCounter = 0 To intNumOfFields - 1
  Do While Not MyRS.EOF
    If IsNull(MyRS.Fields(intFieldCounter).Value) Then
      intNumOfEmptyFields = intNumOfEmptyFields + 1
    End If
    MyRS.MoveNext
  Loop
    If intNumOfEmptyFields = intNumOfRecords Then   'ALL' Fields Empty
      Debug.Print MyRS.Fields(intFieldCounter).Name
    End If
    intNumOfEmptyFields = 0     'Reset
    MyRS.MoveFirst
Next
 
MyRS.Close
Set MyRS = Nothing

[JFKJr]

This one was a little challenging, but here it goes. The following code will loop through all Fields in a Recordset, in this case one based on an Employees Table, and will list to the Immediate Window 'ALL' Fields which have 'NO' values in 'ALL' the Records in the Recordset. To subsequently DELETE those Fields would be even trickier yet since you cannot use the Delete Method on a member of the Fields Collection, and you cannot Delete a Field using SQL, DAO, ADO, etc. if it is contained in an active Recordset. Should you really need this functionality, I'm sure that I can accommodate you, just let me know.

Dim MyDB As Database
Dim MyRS As DAO.Recordset
Dim intFieldCounter As Integer
Dim intNumOfFields As Integer
Dim intNumOfRecords As Integer
Dim intNumOfEmptyFields As Integer
 
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("Employees", dbOpenDynaset)
 
MyRS.MoveLast: MyRS.MoveFirst
 
intNumOfFields = MyRS.Fields.Count
intNumOfRecords = MyRS.RecordCount
 
For intFieldCounter = 0 To intNumOfFields - 1
  Do While Not MyRS.EOF
    If IsNull(MyRS.Fields(intFieldCounter).Value) Then
      intNumOfEmptyFields = intNumOfEmptyFields + 1
    End If
    MyRS.MoveNext
  Loop
    If intNumOfEmptyFields = intNumOfRecords Then   'ALL' Fields Empty
      Debug.Print MyRS.Fields(intFieldCounter).Name
    End If
    intNumOfEmptyFields = 0     'Reset
    MyRS.MoveFirst
Next
 
MyRS.Close
Set MyRS = Nothing

Thanks a lot for the reply, the above code successfully displaying the empty fields in my table (ImportedData). Now I would like to delete these empty fields from the table. It would really make my day if I can get this code.

I am tired of searching this code on internet.

Thanks.

[ADezii]

Thanks a lot for the reply, the above code successfully displaying the empty fields in my table (ImportedData). Now I would like to delete these empty fields from the table. It would really make my day if I can get this code.

I am tired of searching this code on internet.

Thanks.

The Names of the Fields to Delete would have to be stored in an Array, the Recordset would have to be closed, the Names would then have to be retrieved from the Array, and the Fields themselves deleted via DAO since we are already using it. There lies the logic, when I get a chance I will make an 'attempt' to implement it. BTW, don't beat yourself on this one, since this is rather specialized and not-mush-asked-for code. I don't think that you will find many references to this on the Internet. Check back this evening or at the latest tomorrow afternoon.

[JFKJr]

The Names of the Fields to Delete would have to be stored in an Array, the Recordset would have to be closed, the Names would then have to be retrieved from the Array, and the Fields themselves deleted via DAO since we are already using it. There lies the logic, when I get a chance I will make an 'attempt' to implement it. BTW, don't beat yourself on this one, since this is rather specialized and not-mush-asked-for code. I don't think that you will find many references to this on the Internet. Check back this evening or at the latest tomorrow afternoon.

Thanks for the response, I understand your logic, but it seems to be difficult to implement as I am a new beginner to Access VBA. It would be of great help if I can get help on this from you.

I will definitely keep in touch with the post. Thanks, Have a great day!

[ADezii]

'Completely' replace the previous code with this code block, it should do the trick. Plug your own Table name into the Constant conTABLE_NAME. Keep in touch and let me know how you make out.

P.S. - You cannot run this code from a Form whose Record Source is based on a Table/Query which consists of the Field(s) to be deleted!

Dim MyDB As Database
Dim MyRS As DAO.Recordset
Dim intFieldCounter As Integer
Dim intNumOfFields As Integer
Dim intNumOfRecords As Integer
Dim intNumOfEmptyFields As Integer
Dim astrFieldsToDelete() As String
Dim intCounter As Integer
Dim tdf As DAO.TableDef
Const conTABLE_NAME As String = "Employees"     'Replace
 
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(conTABLE_NAME, dbOpenDynaset)
 
MyRS.MoveLast: MyRS.MoveFirst
 
intNumOfFields = MyRS.Fields.Count
 
'Redimension Array to the Maximum Number of Fields to Delete,
'namely the Total Number of Fields
ReDim astrFieldsToDelete(1 To intNumOfFields)
 
intNumOfRecords = MyRS.RecordCount
 
For intFieldCounter = 0 To intNumOfFields - 1
  Do While Not MyRS.EOF
   'Debug.Print MyRS.Fields(intFieldCounter).Name & " ==> " & _
               'MyRS.Fields(intFieldCounter).Value
    If IsNull(MyRS.Fields(intFieldCounter).Value) Then
      intNumOfEmptyFields = intNumOfEmptyFields + 1
    End If
    MyRS.MoveNext
  Loop
    If intNumOfEmptyFields = intNumOfRecords Then   'ALL' Fields Empty
      'Debug.Print "Must DELETE: " & MyRS.Fields(intFieldCounter).Name
      astrFieldsToDelete(intFieldCounter) = MyRS.Fields(intFieldCounter).Name
    End If
    intNumOfEmptyFields = 0     'Reset
    MyRS.MoveFirst
Next
 
MyRS.Close
Set MyRS = Nothing
 
Set tdf = MyDB.TableDefs(conTABLE_NAME)
 
'Delete valid Fields whose names are in the Array astrFieldsToDelete()
For intCounter = LBound(astrFieldsToDelete) To UBound(astrFieldsToDelete)
  If Len(astrFieldsToDelete(intCounter)) > 0 Then
    'Debug.Print "In Array: " & astrFieldsToDelete(intCounter)
    tdf.Fields.Delete astrFieldsToDelete(intCounter)
  End If
Next

[JFKJr]

'Completely' replace the previous code with this code block, it should do the trick. Plug your own Table name into the Constant conTABLE_NAME. Keep in touch and let me know how you make out.

P.S. - You cannot run this code from a Form whose Record Source is based on a Table/Query which consists of the Field(s) to be deleted!

Dim MyDB As Database
Dim MyRS As DAO.Recordset
Dim intFieldCounter As Integer
Dim intNumOfFields As Integer
Dim intNumOfRecords As Integer
Dim intNumOfEmptyFields As Integer
Dim astrFieldsToDelete() As String
Dim intCounter As Integer
Dim tdf As DAO.TableDef
Const conTABLE_NAME As String = "Employees"     'Replace
 
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(conTABLE_NAME, dbOpenDynaset)
 
MyRS.MoveLast: MyRS.MoveFirst
 
intNumOfFields = MyRS.Fields.Count
 
'Redimension Array to the Maximum Number of Fields to Delete,
'namely the Total Number of Fields
ReDim astrFieldsToDelete(1 To intNumOfFields)
 
intNumOfRecords = MyRS.RecordCount
 
For intFieldCounter = 0 To intNumOfFields - 1
  Do While Not MyRS.EOF
   'Debug.Print MyRS.Fields(intFieldCounter).Name & " ==> " & _
               'MyRS.Fields(intFieldCounter).Value
    If IsNull(MyRS.Fields(intFieldCounter).Value) Then
      intNumOfEmptyFields = intNumOfEmptyFields + 1
    End If
    MyRS.MoveNext
  Loop
    If intNumOfEmptyFields = intNumOfRecords Then   'ALL' Fields Empty
      'Debug.Print "Must DELETE: " & MyRS.Fields(intFieldCounter).Name
      astrFieldsToDelete(intFieldCounter) = MyRS.Fields(intFieldCounter).Name
    End If
    intNumOfEmptyFields = 0     'Reset
    MyRS.MoveFirst
Next
 
MyRS.Close
Set MyRS = Nothing
 
Set tdf = MyDB.TableDefs(conTABLE_NAME)
 
'Delete valid Fields whose names are in the Array astrFieldsToDelete()
For intCounter = LBound(astrFieldsToDelete) To UBound(astrFieldsToDelete)
  If Len(astrFieldsToDelete(intCounter)) > 0 Then
    'Debug.Print "In Array: " & astrFieldsToDelete(intCounter)
    tdf.Fields.Delete astrFieldsToDelete(intCounter)
  End If
Next

Hello ADezii, The above code is working perfectly fine. Thanks a lot for your help. Its been a week that I have been trying on the code to make it work. At last I finally got the code :), I don't find any words to express how much I appreciate your help. Thank you very much :)

[ADezii]

Hello ADezii, The above code is working perfectly fine. Thanks a lot for your help. Its been a week that I have been trying on the code to make it work. At last I finally got the code :), I don't find any words to express how much I appreciate your help. Thank you very much :)

You are quite welcome, that's why we are all here.

[johnsmith000]

ADezii, i just want to say how much i appreciate your work here. I've been trying to solve that problem for literally the whole of last week - and it's such a relief to have found the code to do it for me!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

thanks man i appreciate it so much :)
:)
:)
:)

hooorayyyyyyyyyyyyyy!

[ADezii]

@johnsmith000
That's not a problem, glad we were able to resolve the problem.

[chcorsa]

Dear ADezii,

I've been searching the internet to find a way to delete empty fields out of my databases and ran across your post and your code. It is simply beautiful and worked like a charm. YOU ARE MY HERO. Thank you so much for posting this code. I know it must have taken you some time to do it. I just want you to know how much appreciated you are -- I joined bytes.com just to be able to write you a THANK YOU. For all your time, just know how much you have helped others well beyond the individual who asked the original question. YOU ARE THE BEST!!

[NeoPa]

ADezii,

I've told you about this before. Stop creating new aliases to congratulate yourself with!!

(Sorry - couldn't resist it :D)

To the new members posting their thanks - Welcome to Bytes! It's nice to see people registering just to say thank you. I hope you will both continue to benefit from our site :)

PS. We think ADezii's a bit of a star too ;)

[ADezii]

@chcorsa
Thanks for taking the time and going out of your way to write this Post. We are all here for the same reason at Bytes, and I am grateful just to be a Member. Take care.

[ADezii]

@NeoPa
It's Posts like this that make it all worthwhile, hey NeoPa?

[NeoPa]

It's certainly nice when people sign up just to thank you ADezii. Two in one thread is a bonus. It's probably the power of good example :)

[Peter Grandia]

You saved me a lot of work! Thanks very much!