• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Merge n Number of Columns of a table to one column

Merge n Number of Columns of a table to one column

Merge n Number of Columns of a table to one column

API Declarations


'# replace the database path and name this code merges #'
'# the data in the columns to one column seperated by #'
'# "," and then DELETES THE OTHER COLUMN'S FROM WHERE #'
'# THE DATA IS TAKEN #'
'# Make a reference to Mcrosoft DAO 3.6 Object Library #'
'# in your Project #'
*********************************************************



Rate Merge n Number of Columns of a table to one column

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

'# You should not delete these lines and also copy the #'
'# Decleration Lines to your code                      #'
'# Author is not responsible for any misbehaviour of   #'
'# Code. I HAVE MANY OTHER APPLICATIONS AND CODES ANY  #'
'# ONE INTERESTED MAY CONTACT ME AT                    #'
'# [email protected]                        #'
'# As i have recived plenty of help from net would be  #'
'# glad to help also.                                  #'
*********************************************************

Dim dbworkspace As Workspace
Dim db1 As Database
Dim dbtable As Recordset, tmptable As Recordset
Dim str, SQL
Private Sub Form_Load()
    Me.Hide
    Set dbworkspace = DBEngine.Workspaces(0)
    Set db1 = dbworkspace.OpenDatabase(App.Path & "\mam.mdb", False, False, ";pwd=1234")
    SQL = "select * from master"
    Set dbtable = db1.OpenRecordset(SQL, dbOpenDynaset)
    dbtable.MoveLast
    dbtable.MoveFirst
    While Not (dbtable.EOF)
        ChkEmpty ("Artist1")
        ChkEmpty ("Artist2")
        ChkEmpty ("Artist3")
        ChkEmpty ("Artist4")
        ChkEmpty ("Artist5")
        ChkEmpty ("Artist6")
        ChkEmpty ("Artist7")
        ChkEmpty ("Artist8")
        ChkEmpty ("Artist9")
        ChkEmpty ("Artist10")
        If str <> "" Then str = Mid(str, 1, Len(str) - 1)
        SQL = "update master set Artist1='" & str & "' ,Artist2=''"
        SQL = SQL & " ,Artist3='' ,Artist4='' ,Artist5='' ,Artist6='' ,Artist7=''"
        SQL = SQL & " ,Artist8='' ,Artist9='' ,Artist10='' where Name='" & dbtable("Name") & "'"
        db1.Execute SQL
        dbtable.MoveNext
        str = ""
    Wend
    db1.Close
    Set dbworkspace = DBEngine.Workspaces(0)
    Set db1 = dbworkspace.OpenDatabase(App.Path & "\mam.mdb", False, False, ";pwd=1234")
    SQL = "ALTER Table master DROP COLUMN Artist2;"
    db1.Execute SQL
    SQL = "ALTER Table master DROP COLUMN Artist3;"
    db1.Execute SQL
    SQL = "ALTER Table master DROP COLUMN Artist4;"
    db1.Execute SQL
    SQL = "ALTER Table master DROP COLUMN Artist5;"
    db1.Execute SQL
    SQL = "ALTER Table master DROP COLUMN Artist6;"
    db1.Execute SQL
    SQL = "ALTER Table master DROP COLUMN Artist7;"
    db1.Execute SQL
    SQL = "ALTER Table master DROP COLUMN Artist8;"
    db1.Execute SQL
    SQL = "ALTER Table master DROP COLUMN Artist9;"
    db1.Execute SQL
    SQL = "ALTER Table master DROP COLUMN Artist10;"
    db1.Execute SQL
    db1.Close
    End
End Sub

Private Sub ChkEmpty(fld As String)
    If dbtable(fld) <> "" And Not IsEmpty(dbtable(fld)) Then
        str = str & dbtable(fld) & ","
    End If
End Sub