Results 1 to 4 of 4
  1. #1
    kapil0712 is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2009
    Posts
    8

    Deleting empty columns from a table

    Guys,


    I have a weird problem. I have to generate a table with random number of columns. Out of those randomly generated columns (Column names are random as well..i mean they can vary depending on the request), some are blank with no data in them.
    I need to write a code in vba (actually a module) to sequentially browse through different columns in the table and check whether the columns are empty or not. If they are empty, delete the columns from the table...or if its easy, create another table with only the columns that have data in it (not blank columns....can have a single record also)..
    Please help...

    P.S. I am not a coding guy so any simplification or a ready made code would be greatly appreciated..


    Thanks
    Kapil0712

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    some of the code in this example will be useful to you: http://www.access-programmers.co.uk/...ht=information


    it shows you how to loop the fields, and also create fields if you need to. this type of code writing stuff is prone to syntax errors, so be careful!

  3. #3
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    This code below will delete all fields in a table that are blank.

    This is What the code does:
    1) Loops through all the Fields in the table.
    2) Checks Record Count in the Field using DCount.
    3) Creates SQL to Drop blank fields.

    Limitations of the Code:
    1) The code can handle only 100 blank Fields in a table. If you anticipate more than that please adjust the Dim strAltersql(100) As String Array.
    2) You may have problem deleting Fields that are Indexed.

    How to use this code:
    Attach it to the Onclick event of a command button on the form.

    Note: Please put the name of your table in place of "student"

    Private Sub Command0_Click()
    On Error GoTo Err_Command0_Click
    Dim intCounter As Integer
    Dim columnNumber As Integer
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strFieldName As String
    Dim intDcount As Integer
    Dim intCounter2 As Integer
    Dim intCounter3 As Integer
    Dim strAltersql(100) As String


    '////////////////////////////////////
    'Written By Siddthartha Majumdar 05/12/2010
    'Delete Field in a table when No of columns and name of the Fields are not known.
    '//////////////////////////////////



    'Calculating the total number of Columns in a Table

    columnNumber = CurrentDb.TableDefs("student").Fields.Count

    'Looping throug each Column to check whether it contains records

    For intCounter = 0 To columnNumber - 1
    'Generating the Filed name

    Set dbs = CurrentDb()
    Set tdf = dbs.TableDefs("student")
    strFieldName = tdf.Fields(intCounter).Name

    ' Using DCount to determine whether the Field is Blank

    intDcount = IIf(IsNull(DCount(strFieldName, "Student")), 0, DCount(strFieldName, "Student"))


    'Generating Alter Table Queries to drop blank Fields

    If intDcount = 0 Then
    intCounter2 = intCounter2 + 1

    'The Sql string is stored in a Local Array strAltersql

    strAltersql(intCounter2) = "ALTER TABLE student DROP Column " & strFieldName & ";"
    End If
    Next intCounter

    ' Running the alter queries in order

    If intCounter2 > 0 Then
    For intCounter3 = 1 To intCounter2
    CurrentDb.Execute strAltersql(intCounter3)
    Next intCounter3
    End If


    Exit_Command0_Click:
    Exit Sub

    Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click

    End Sub


    Check the code and let me know.

  4. #4
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    have you been able to solve your problem. did the solution that I gave you helped.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Table with look-up columns
    By Tallica in forum Database Design
    Replies: 3
    Last Post: 03-02-2010, 02:09 PM
  2. Replies: 3
    Last Post: 01-21-2010, 08:10 PM
  3. Replies: 1
    Last Post: 10-09-2009, 11:52 AM
  4. Replies: 3
    Last Post: 09-19-2008, 02:19 AM
  5. Delete query, deleting customers froma table.
    By keithsrobinson in forum Queries
    Replies: 2
    Last Post: 02-14-2006, 11:33 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums