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.
'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
15 9871
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
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.
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.
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!
'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
'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 :)
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.
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!
@johnsmith000
That's not a problem, glad we were able to resolve the problem.
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 32,556
Expert Mod 16PB
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 ;)
@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.
@NeoPa
It's Posts like this that make it all worthwhile, hey NeoPa?
NeoPa 32,556
Expert Mod 16PB
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 :)
You saved me a lot of work! Thanks very much!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: yvan |
last post by:
Approximately once a month, a client of ours sends us a bunch of
comma-delimited text files which I have to clean up and then import
into their MS SQL database. All last week, I was using a Cold...
|
by: qsweetbee |
last post by:
I have a form(fAddUsers) in my database. It is continue form for data
entry. Some fields are required fields. Some are optional fields.
There is 1 particular filed(TokenExpirationDate)on the form...
|
by: B Blue |
last post by:
here's the situation:
a produce dept at a grocery store needs to generate a weekly sheet for
ordering. This is how it looks:
5 columns across with the following headings:
Product Description,...
|
by: Krechting |
last post by:
Hi ALl,
I have a code that checks if the documents in a hyperlink field are still
where they should be. I use fileexist().
First I want to filter out all the hyperlink fields that are empty.
I...
|
by: google |
last post by:
I have a database with four table. In one of the tables, I use about
five lookup fields to get populate their dropdown list. I have read
that lookup fields are really bad and may cause problems...
|
by: my-wings |
last post by:
I think I've painted myself into a corner, and I'm hoping someone can help
me out.
I have a table of books (tblBooks), which includes a field (strPubName) for
Publisher Name and another field...
|
by: troy.forster |
last post by:
I am sure this must be about the simplest thing to do. I can whip
something off in 10 different languages and environments with no
problem. But VisualStudio 2003 and ASP.NET is sooooo...
|
by: lesperancer |
last post by:
I've got an access97 reporting mdb that pulls data (77,000 rows) from a
sql server table into a local table to run reports
if the local table is part of the reporting MDB, the insert statement...
|
by: ibeehbk |
last post by:
Hi. I have a form made in xhtml. I test via vbscript to make sure none of the fields are empty and properly formatted (ie email). All the regular fields work. However, I have two drop down menus...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |