Use of ADO to retrieve and update a table using SQL

This example demonstrates the use of ADO to split a field with data such as 'A B C D' counting the number of values (4) and inserting the 4 split values as 4 new rows into another table. This is particularly useful when a user has decided that the data should be presented in "long" format rather than "wide" format so that it is easier to carry out analysis. The data is originally held in bout and new rows inserted into bout_group. This shows the use of ADO which should be used in Access 2000, rather than DAO- the earlier method.

Public Sub split_group()
Dim member As String
'read row
Dim con As New ADODB.Connection
Dim partyset As New ADODB.Recordset
Dim boutset As New ADODB.Recordset
Dim stmember As String
Dim rest As String
Dim stdate As Date
Dim sttime As Date
Dim stfirst As String
Dim boutsql As String
Dim strsql As String
Dim group_no As Integer
boutsql = "select id,party_members,bout_date,bout_time from bout"

Set con = CurrentProject.Connection

boutset.Open "bout", con, adOpenDynamic, adLockOptimistic, adCmdTable
partyset.Open "bout_group", con, adOpenDynamic, adLockOptimistic, adCmdTable

With boutset
Do Until .EOF
    group_no = 1
    stmember = Left(!party_members, InStr(!party_members, " ") - 1)
    stfirst = !party_members
    rest = Mid(stfirst, InStr(stfirst, " ") + 1)
    stdate = !bout_date
    sttime = !bout_time
    Debug.Print !party_members
'note the use of ! to get a column value from the table    
    If InStr(rest, " ") > 1 And InStr(rest, " ") < Len(rest) Then
        group_no = group_no + 1
        stmember = Left(rest, InStr(rest, " ") - 1)
        rest = Mid(rest, InStr(rest, " ") + 1)
        strsql = "insert into bout_group(id,bout_date,bout_time) select '" & _
        stmember & "' & stdate & "#,#" & sttime & "#"
        docmd.setwarnings false
        docmd.runsql strsql
        docmd.setwarnings true
'switch off the warnings to avoid constant confirmation
'note stsql does not need a from clause and # around date data
    End If
    Do While InStr(rest, " ") > 1 And InStr(rest, " ") < Len(rest)
        group_no = group_no + 1
        stmember = Left(rest, InStr(rest, " ") - 1)
        rest = Mid(rest, InStr(rest, " ") + 1)
        strsql = "insert into bout_group(id,bout_date,bout_time) select '" & _
        stmember & "' & stdate & "#,#" & sttime & "#"
        docmd.setwarnings false
        docmd.runsql strsql
        docmd.setwarnings true
   Loop
    group_no = group_no + 1
    !group_no = group_no
    .Update
    Debug.Print "loopsql", group_no
    .MoveNext
'note that update updates the column value group_no
Loop
End With

boutset.Close
partyset.Close
con.Close
  
End Sub