by Amit Khanchandani (1 Submission)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Fri 8th February 2002
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
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 #'
*********************************************************
VBcoders
'# 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
