[RESOLVED] Insert Into Statement
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.
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.
Quote:
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:
Quote:
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:
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()
'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
Re: Insert Into Statement
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:
Code:
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)
Re: Insert Into Statement
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
Re: Insert Into Statement
Close, it would be like this:
Code:
VALUES ('" & RS.Fields(0).Value & "', "
Re: Insert Into Statement
Quote:
Originally Posted by
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:
Code:
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
Code:
& "'" & RS.Fields(0).Value & "'" &
And
Code:
& "'" & RS.Fields(1).Value & "'" &
G
Re: Insert Into Statement
What is the full code for that line, and what does the variable contain after it runs?
Re: Insert Into Statement
Quote:
Originally Posted by
si_the_geek
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:
Code:
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
Re: Insert Into Statement
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:
Code:
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
Re: Insert Into Statement
There is no reason to have two copies of the building, just change the Execute line to this:
Code:
db.Execute sqlInsert
As for the new issue, it is probably due to the positioning of these lines:
Code:
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)
Re: Insert Into Statement
Quote:
Originally Posted by
si_the_geek
There is no reason to have two copies of the building, just change the Execute line to this:
Code:
db.Execute sqlInsert
As for the new issue, it is probably due to the positioning of these lines:
Code:
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
Re: Insert Into Statement
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:
Is this enough or should I be doing anything else?
G
Re: Insert Into Statement
That's not quite enough, you should also be setting the object variables to nothing:
Code:
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.
Re: Insert Into Statement
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
Re: Insert Into Statement
I just used the variable NewTableName for now instead of mucking about with objects, that works ok for me.
G
Re: Insert Into Statement
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.
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
