Well, using Bob's original as a base, I have modified the function and called it SplitCodesTwo. I created a new table (TableForInsertNameHere) to accept your companyid and the text field alt_img TableForInsertNameHere.
I have assumed your records in OneBigTable are companyId and alt_Img where alt_img contains the text you showed earlier.
The new function is
Code:
'---------------------------------------------------------------------------------------
' Procedure : SplitCodesTwo
' Author : Jack
' Date : 24/02/2012
' Purpose : FROM: https://www.accessforums.net/showthread.php/14370-Access-2003-Split-Data-into-an-unknown-number-of-fields
'original from Bob Larson
'---------------------------------------------------------------------------------------
'
Function SplitCodesTwo()
Dim varSplit() As String 'array to hold result of the Split function
Dim strSQL As String
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim lngCount As Long
On Error GoTo SplitCodesTwo_Error
Set db = CurrentDb
'adjusted to deal with your table and field names
Set rst = db.OpenRecordset("Select [CompanyID], [alt_img] FROM OneBigTable")
With rst
Do Until .EOF
varSplit = Split(![alt_img], "; ") 'Split at the "; "
For lngCount = 0 To UBound(varSplit)
'this new table was defined separately before running this function
' each array element becomes a new record in the new table
'the companyid is from the original table
strSQL = "INSERT INTO TableForInsertNameHere ( CompanyID, Alt_img) " & _
"VALUES (" & !CompanyID & ",'" & varSplit(lngCount) & "');"
Debug.Print strSQL 'Print the rendered sql before executing
CurrentDb.Execute strSQL, dbFailOnError
Next
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
On Error GoTo 0
Exit Function
SplitCodesTwo_Error:
MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure SplitCodesTwo "
End Function
This was the only record in OneBigTable
companyid |
alt_img |
1 |
AAG70260G05_2_1.JPG; AAG70260G05_3_1.JPG; AAG70260G05_5_1.JPG; AAG70260G05_6_1.JPG; AAG70260G05_7_1.JPG; AAG70260G05_8_1.JPG; AAG70260G05_4_1.JPG |
This is the output into the new table
companyid |
alt_img |
1 |
AAG70260G05_2_1.JPG |
1 |
AAG70260G05_3_1.JPG |
1 |
AAG70260G05_5_1.JPG |
1 |
AAG70260G05_6_1.JPG |
1 |
AAG70260G05_7_1.JPG |
1 |
AAG70260G05_8_1.JPG |
1 |
AAG70260G05_4_1.JPG |