Split Data By Comma Delimited

[Hamdard]

Hi Experts,

In Table_A I have the following fields.

ProjectName
Sectors
Tags

Data in the Tags field is delimited by commas (,) such as the following:
ProjectName Sectors Tags
Water Supply Health Clean Water, Hygiene, Human Rights, Access to Resources

Now, I want to split and then insert data in Table_B by eliminating the delimited values so that I have each tag value in a row as a single record such as the following:
ProjectName Sectors Tags
Water Supply Health Clean Water
Water Supply Health Hygiene
Water Supply Health Human Rights
Water Supply Health Access to Resources

To do this, I wrote the following VBA code. But it returns empty records for Tags. What do I do wrong? Your expert advises is highly appreciated.

Private Sub SplitTagsButton_Click()
DoCmd.SetWarnings Off
On Error GoTo Error_Resume
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Dim sqlStr, insertSQL, arrayVal As String
Dim TestArray() As String
Dim fieldName, fieldSector, fieldTags As String
Dim i As Integer

sqlStr = "SELECT [ProjectName], [Sectors], [Tags] FROM Table_A"
Set rs = db.OpenRecordset(sqlStr)
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
fieldName = rs.Fields(0)
fieldSector = rs.Fields(1)
fieldTags = rs.Fields(2)
TestArray() = Split(fieldTags, ", ")

For i = 0 To UBound(TestArray)
If TestArray(i) <> "" Then

arrayVal = TestArray(i)

insertSQL = "INSERT INTO Table_B([ProjectName], [Sectors], [Tags]) " _
& "VALUES(""" & fieldName & """, """ & fieldSector & """, """ & arrayVale & """)"
DoCmd.RunSQL (insertSQL)
Error_Resume:
Resume Next

End If
Next i
rs.MoveNext
Loop
End Sub

 

[pbaldy]

For starters, you set one variable and use another:

arrayVal
arrayVale

This may be of assistance:

Option Explicit

 

[bastanu]

And you seem to have a space in the Split:
TestArray() = Split(fieldTags, ", ")

 

[moke123]

You also do not set warnings true again.
Avoid setting warnings altogether and use db.execute insertSql, dbfailonerror since you've already instanced it.

 

[Pat Hartman]

But when you do set warnings, use a macro.

mSetWarningsOff = sets warnings off and hourglass on
mSetWarningsOn = sets warnings on and hourglass off

The hourglass is an annoying visual indicator that warnings are off. You can then run the on macro and fix your code.

These are the only two macros in most of my databases. It is really critical to not leave warnings off - hence using the hourglass as a visual wake up call. You will get burnt by this sooner or later when you close something you changed but didn't specifically save it before you closed the object and all your nice changes go bye-bye as they are silently discarded.