[RESOLVED] Insert Into Statement

[Grifter]

Hi I have the following insert into statement in my code and have created the RS table fields from a SELECT statement in my code.

"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 & ")"

I keep getting the following error at the insert into statement. The column that the data is failing on is the first field in the query and is a varchar field; when I create the table I create the column names, sizes and data types from that what is found in the query columns.

3075 syntax error (missing operator) in query expression 'FirstVarcharValue'

I have done a debug print for the INSERT INTO statement and it is as below:

INSERT INTO tablename(ColumnAliasName1, ColumnAliasName2, ColumnAliasName3, ColumnAliasName4) VALUES (FirstVarcharValue, Second Varchar Value, 3, 771804.16)

But from here I cannot see the problem. My full code is below for illustration purposes:

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()
'CreateTable
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)

Dim tbl As TableDef
If Not (TableExists(NewTableName)) Then
Set db = CurrentDb()
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 

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 & ")" ' this is just to allow debug printing
    Debug.Print sqlInsert 
     db.Execute ("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 & ")") <<< Error raised here on first row of data
     RS.MoveNext
    Loop
Else
  GoTo Exit_SomeName
End If
'tbl.Fields.Append fld
db.TableDefs.Append tbl
db.TableDefs.Refresh

Else
MsgBox "This table already exists."
End If

RS.Close
db.Close

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

From here I do not really understand what is missing unless there is something I do nto have that should be in the code to make the DML statement correct for code?

G

[si_the_geek]

Thread moved to the 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)


I wouldn't have expected that particular error, but I would have expected an error... because you have not put delimiters around the values, eg:

VALUES ('FirstVarcharValue', 'Second Varchar Value', 3, 771804.16)

For an explanation and examples of delimiters to use around values within SQL statements, see the article How do I use values (numbers, strings, dates) in SQL statements? from our Database Development FAQs/Tutorials (at the top of this forum)

[Grifter]

Hi

How do I attach delimiters round values in the RS object though?

RS.Fields(0).Value

Is it as simple as ' & RS.Fields(0).Value & '

G

[si_the_geek]

Close, it would be like this:

VALUES ('" & RS.Fields(0).Value & "', "

[Grifter]

Thread moved to the 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)


I wouldn't have expected that particular error, but I would have expected an error... because you have not put delimiters around the values, eg:

VALUES ('FirstVarcharValue', 'Second Varchar Value', 3, 771804.16)

For an explanation and examples of delimiters to use around values within SQL statements, see the article How do I use values (numbers, strings, dates) in SQL statements? from our Database Development FAQs/Tutorials (at the top of this forum)

I now have this with quotes and still same error?

I done it like this in my code

& "'" & RS.Fields(0).Value & "'" &

And

& "'" & RS.Fields(1).Value & "'" &

G

[si_the_geek]

What is the full code for that line, and what does the variable contain after it runs?

[Grifter]

What is the full code for that line, and what does the variable contain after it runs?

Ok it never worked because I only added the quotes to the code used in the debug print bit but realise I should have had this:

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 & ")"
    Debug.Print sqlInsert
     db.Execute ("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 & ")")
     RS.MoveNext
    Loop
Else
  GoTo Exit_SomeName
End If

The output does now show quotes, however from the post suggestion above I am not sure if my concatenation of string delimiters are syntactically correct but it does show the delimiters round the varchar columns.

Now it is failing at insert into and saying:

3192 cannot find output table 'Tablename'

So I think one error has been solved only to move onto the next.

G

[Grifter]

For this error MS says uses select into instead:

http://office.microsoft.com/en-gb/ac...080760272.aspx

So in the portion of code below for the new table:

Dim tbl As TableDef
If Not (TableExists(NewTableName)) Then
Set db = CurrentDb()
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
'Set RS.Fields(fldNo).Name = CheckColName(RS.Fields(fldNo).Name)
'if RS.Fields(fldNo).Name
tbl.Fields.Append tbl.CreateField(RS.Fields(fldNo).Name, RS.Fields(fldNo).Type, RS.Fields(fldNo).Size)
Next fldNo

I have created a new table definition but is there anything else I need to do before trying to insert the values into it, do I need to use select into in this instance as it is my understanding the table is not yet created but only defined?

G

[si_the_geek]

There is no reason to have two copies of the building, just change the Execute line to this:

db.Execute sqlInsert

As for the new issue, it is probably due to the positioning of these lines:

db.TableDefs.Append tbl
db.TableDefs.Refresh

...I'm fairly sure they should be moved to before you start doing inserts (probably just after If Not (RS.EOF) Then).


By the way, your poor indenting makes your code harder to read than it could be. For an explanation/example of how it should be, see the article What is indenting, and why should I do it? from our Classic VB FAQs (in the FAQ forum)

[Grifter]

There is no reason to have two copies of the building, just change the Execute line to this:

db.Execute sqlInsert

As for the new issue, it is probably due to the positioning of these lines:

db.TableDefs.Append tbl
db.TableDefs.Refresh

...I'm fairly sure they should be moved to before you start doing inserts (probably just after If Not (RS.EOF) Then).


By the way, your poor indenting makes your code harder to read than it could be. For an explanation/example of how it should be, see the article What is indenting, and why should I do it? from our Classic VB FAQs (in the FAQ forum)

Hi

Yes I realised the first bit about using the same string in a variable then using seperately - still in hard-code mode! - and I will try and fix my indenting - sorry if it wasted time.

G

[Grifter]

Ok thanks that now works. One final thing that I have always been unsure about when coding is when I finish what should I be clearing and closing? Currently I have:

RS.Close
db.Close

Is this enough or should I be doing anything else?

G

[si_the_geek]

That's not quite enough, you should also be setting the object variables to nothing:

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

On looking at your code again (particularly Set db = CurrentDb() ) I see that you are not using VB6, but VBA inside Access.

As such the line db.Close may be a bad thing, but I'm not sure (either way, you should set it to Nothing). If it seems to be OK, I'd recommend keeping it.

[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