Thanks for all the help so far, I will take all the advice and apply to my code. I have one further question that I knew would crop up sooner or later and here it is.

For instance once my table is created with data and say I want to now create another record set by querying the table/data set I have created how do I reuse the values in that table object (tbl)? For instance if you look at the bold bit of my code by querying the results that are in the new table (I want to do more than just select but this keeps it simple for just now):

Code:
Public Function resultset() As String
    On Error GoTo Err_SomeName
    Dim db As Database
    Const NewTableName = "OpptHeaderProducts"
    Dim RS As DAO.Recordset
    Dim LSQL As String
    Dim sqlInsert As String
    Dim ConcatVars As String
    Dim recordCnt As Integer
    Dim i As Integer
    'Set db = CurrentDb()


    Dim tbl As TableDef
    If Not (TableExists(NewTableName)) Then
        Set db = CurrentDb()

        LSQL = "SELECT o.Column2 AS ColumnAliasName1, " & _
              " p.Column1 AS ColumnAliasName2," & _
              " COUNT(*) AS ColumnAliasName3, " & _
              " SUM(p.Column3) As ColumnAliasName4" & _
              " FROM  table1 o " & _
              " INNER JOIN table2 p ON o.column1 = p.column4 " & _
              " GROUP BY o.column2, p.column1 " & _
              " HAVING COUNT(*) > 2 " & _
              " AND SUM(p.Column3) > 10000 " & _
              " ORDER BY o.p.Column2, p.Column1 "
        Set RS = db.OpenRecordset(LSQL)

        Set tbl = db.CreateTableDef(NewTableName)
        'creates each column in new table relating to the name, type and size of the fields in the results set of the query
        For fldNo = 0 To RS.Fields.Count - 1
            tbl.Fields.Append tbl.CreateField(RS.Fields(fldNo).Name, RS.Fields(fldNo).Type, RS.Fields(fldNo).Size)
        Next fldNo

        'Move to first row of data in result set
        'Check if result set not at end of file
        'create a do loop until end of the result set is reached
        'Execute insert statement for new table using result set values from first to last row
        'once a row has been inserted then move to the next row
        'if end of file is reached then call exit procedure 


        db.TableDefs.Append tbl
        db.TableDefs.Refresh

        RS.MoveFirst
        If Not (RS.EOF) Then
            Do Until RS.EOF
                sqlInsert = "INSERT INTO " & tbl.Name & "(" & RS.Fields(0).Name & ", " & RS.Fields(1).Name & ", " & RS.Fields(2).Name & ", " & RS.Fields(3).Name & ") VALUES (" & "'" & RS.Fields(0).Value & "'" & ", " & "'" & RS.Fields(1).Value & "'" & ", " & RS.Fields(2).Value & ", " & RS.Fields(3).Value & ")"
                db.Execute (sqlInsert)
                RS.MoveNext
            Loop
        Else
            GoTo Exit_SomeName
        End If
    Else    ' process records in new table
        'Create new RS from new table of data
        Set tbl = <<<<<<<??? is the first part right, should I be using the object name this way?
        LSQL = "SELECT * FROM " & tbl.Name << Fails here (with object not set) if I try set tbl to createtabledef(tablename) - which I know is probably not right anyway.
        Set RS = db.OpenRecordset(LSQL)
    End If

    RS.Close
    db.Close

Exit_SomeName:
    Exit Function
Err_SomeName:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_SomeName
End Function
Thanks again for any input and I think answer to this would solve the overall picture of what I am trying to achieve here.

G