[RESOLVED] Insert Into Statement

[Grifter]

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):

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

[Grifter]

I just used the variable NewTableName for now instead of mucking about with objects, that works ok for me.

G

[Grifter]

For anyone with a similar problem then I have posted my final code below. The function is called from a query (Q1) in the Access user area. This code in MS Access creates a result set (RS) from tables already in the database, it then puts this data in a holding table for processing by the code. Once it is processed Q1 is run again and then another table is created - final table - to hold the processed data. The processing is to concatenate one column value where duplicate IDs exist and also add up a number value associated with this.

The columns in the tables created have their name, size and data type are set dynamically usign the values found in the original query. This is helpful as it stops any ambiguity between the values being read in and created.

Once this table is created the holding table is queried, processing is done and the final table is populated with the data. Any questions please ask for further explanation.

I have included the check table exists function as well for clarity, listed at end of the code.

Public Function resultset() As String
    On Error GoTo Err_SomeName
    Dim db As Database
    Const HoldingTableName = "HoldingTable"
    Const FinalTableName = "FinalTable"
    Dim RS As DAO.Recordset
    Dim LSQL As String
    Dim sqlInsert As String
    Set db = CurrentDb()

    Dim tbl As TableDef
    If Not (TableExists(HoldingTableName)) Then 

        LSQL = " SELECT AliasTable1.column1 AS MyAlias1, " & _
             " AliasTable2.column2 AS Myalias2," & _
             " SUM(AliasTable2.column3) As total " & _
             " FROM  table1 AliasTable1 " & _
             " INNER JOIN table2 AliasTable2 ON AliasTable1.id = AliasTable2.id " & _
             " GROUP BY AliasTable1.column1, AliasTable2.column2 " & _
             " HAVING COUNT(*) > 2 " & _
             " AND SUM(AliasTable2.column3) > 10000 " & _
             " ORDER BY AliasTable1.column1, AliasTable2.column2 "
        Set RS = db.OpenRecordset(LSQL)

        Set tbl = db.CreateTableDef(HoldingTableName)
        '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 (not yet tested for when end of file is reached)


        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 & ") VALUES (" & "'" & RS.Fields(0).Value & "'" & ", " & "'" & RS.Fields(1).Value & "'" & ", " & RS.Fields(2).Value & ")"
                db.Execute (sqlInsert)
                RS.MoveNext
            Loop
        Else
            GoTo Exit_SomeName
        End If
    Else
        ' process records in holding table
        'Create new RS from new table of data
        'Concatenate any business unit values where the opportunity ID is the same
        'For any opp IDs that are the same the business unit is concatenated and nothing is returned until
        'the next opp id is loaded that does not equal that stored in concat function
        'Filter out any empty concat returned values
        LSQL = "SELECT * FROM " & HoldingTableName
        Set RS = db.OpenRecordset(LSQL)
        RS.MoveFirst
        If Not (RS.EOF) And Not (TableExists(FinalTableName)) Then
            Static strLastRowId As String
            Static strBussUnits As String
            Static ForecastTotal As Single
            Dim tbl1 As TableDef
            Set tbl1 = db.CreateTableDef(FinalTableName)
            'Create same fields in new table as in holding table
            For fldNo = 0 To RS.Fields.Count - 1
                tbl1.Fields.Append tbl1.CreateField(RS.Fields(fldNo).Name, RS.Fields(fldNo).Type, RS.Fields(fldNo).Size)
            Next fldNo
            db.TableDefs.Append tbl1
            db.TableDefs.Refresh
            'Using RS of holding table process each row
            strLastRowId = RS.Fields(0).Value
            strBussUnits = RS.Fields(1).Value
            ForecastTotal = RS.Fields(2).Value    ' To allow loading of first value
            RS.MoveNext
            Do Until RS.EOF
                If RS.Fields(0).Value = strLastRowId Then
                    'concat business units here and add up forecast values
                    'Do not set any values

                    strBussUnits = strBussUnits & ", " & RS.Fields(1).Value
                    ForecastTotal = ForecastTotal + RS.Fields(2).Value

                Else
                    sqlInsert = "INSERT INTO " & tbl1.Name & "(" & RS.Fields(0).Name & ", " & RS.Fields(1).Name & ", " & RS.Fields(2).Name & ") VALUES (" & "'" & strLastRowId & "'" & ", " & "'" & strBussUnits & "'" & ", " & ForecastTotal & ")"
                    db.Execute (sqlInsert)
                    'set values for inclusion in a new table,
                    'this will show the opp id, and the
                    'concatenated business unit and total forecast value
                    'Create a new table and do insert into statement here

                    strLastRowId = RS.Fields(0).Value
                    strBussUnits = RS.Fields(1).Value
                    ForecastTotal = RS.Fields(2).Value

                End If
                RS.MoveNext
            Loop
        Else
            GoTo Exit_SomeName
        End If
    End If

    RS.Close
    Set RS = Nothing
    db.Close
    Set db = Nothing
    

Exit_SomeName:
    Exit Function
Err_SomeName:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_SomeName
End Function



Public Function TableExists(sTable As String) As Boolean
    Dim db As Database
    Dim tbl As TableDef
    Set db = CurrentDb()
    TableExists = False
    For Each tbl In db.TableDefs
        If tbl.Name = sTable Then TableExists = True
    Next tbl
End Function

Hope it helps anyone trying to learn reading and writing to DB from VB code. Note this is VBA code for MS Access.

G