Insert Into syntax error
Hi,
The following code is attempting to insert into a temp table the contents of a list box "List2" so that I can use this data in another part of a long line of code. It keeps telling me that there is an error in the INSERT INTO syntax.
The table doesn't exist. I'm hoping it will be created when the SQL runs.
Thanks
The following code is attempting to insert into a temp table the contents of a list box "List2" so that I can use this data in another part of a long line of code. It keeps telling me that there is an error in the INSERT INTO syntax.
The table doesn't exist. I'm hoping it will be created when the SQL runs.
Private Sub cmdFlowDown_Click()
'On Error GoTo ErrorHandler
Dim strTable As String
Dim i As Integer
strTable = "tblTempTest"
'Delete the table if it exists
DoCmd.DeleteObject acTable, strTable
For i = 0 To List2.ListCount - 1
CurrentDb.Execute ("INSERT INTO " & strTable & " VALUES ('" & List2.Column(0, i) & "'")
Next
'Insert more code here to do something with temp table
Exit Sub
ErrorHandler:
If Err.Number = 7874 Then
Resume Next 'Tried to delete a non-existing table, resume
End If
End SubThanks
JesterToo
I don't think Access supports temp tables. What you might do is create a real table and perform your work with that then drop the table when you are finished with it.
No, you cannot "INSERT INTO " a table that does not already exist, this is an Append query.
Create the table and then delete all the records from it before you start your insert operation
Create the table and then delete all the records from it before you start your insert operation
ASKER
Hi JesterToo
When I say temp table I mean a real table (sorry - should have explained that). My code, hopefully, creates the table when it runs and deletes it at the start if it already exists. Don't know what I'm doing wrong really.
Thanks
When I say temp table I mean a real table (sorry - should have explained that). My code, hopefully, creates the table when it runs and deletes it at the start if it already exists. Don't know what I'm doing wrong really.
Thanks
you need to specify the field
CurrentDb.Execute ("INSERT INTO " & strTable & " ([FieldName]) VALUES ('" & List2.Column(0, i) & "'")
CurrentDb.Execute ("INSERT INTO " & strTable & " ([FieldName]) VALUES ('" & List2.Column(0, i) & "'")
if tblTempTest already exists, then replace lines 6 and 7
'Delete the table if it exists
DoCmd.DeleteObject acTable, strTable
with
currentdb.execute "DELETE FROM tblTempTest", dbfailonerror
'Delete the table if it exists
DoCmd.DeleteObject acTable, strTable
with
currentdb.execute "DELETE FROM tblTempTest", dbfailonerror
ASKER
I'm having issues I'm afraid. It doesn't look like I'm getting anywhere with it.
It looks like I need to create the temp table first and then insert the data into it. Is there a method which will create the table on the fly and then run the INSERT INTO method? I still get a RunTime error 3134 - syntax error in INSERT INTO statement.
It looks like I need to create the temp table first and then insert the data into it. Is there a method which will create the table on the fly and then run the INSERT INTO method? I still get a RunTime error 3134 - syntax error in INSERT INTO statement.
just read you post again,
<The table doesn't exist. I'm hoping it will be created when the SQL runs.> NO it will not happen
<It looks like I need to create the temp table first and then insert the data into it. Is there a method which will create the table on the fly and then run the INSERT INTO method?> Yes there is, but why?
if you want you can just delete the content of the table
currentdb.execute "delete * from tableName"
'to create the table use this command
CurrentDb.Execute "Create Table myTable (FieldX Text)"
in the insert command you need to specify the field
CurrentDb.Execute ("INSERT INTO " & strTable & " ([FieldX]) VALUES ('" & List2.Column(0, i) & "'")
<The table doesn't exist. I'm hoping it will be created when the SQL runs.> NO it will not happen
<It looks like I need to create the temp table first and then insert the data into it. Is there a method which will create the table on the fly and then run the INSERT INTO method?> Yes there is, but why?
if you want you can just delete the content of the table
currentdb.execute "delete * from tableName"
'to create the table use this command
CurrentDb.Execute "Create Table myTable (FieldX Text)"
in the insert command you need to specify the field
CurrentDb.Execute ("INSERT INTO " & strTable & " ([FieldX]) VALUES ('" & List2.Column(0, i) & "'")
ASKER
Hi Rey,
I have run a test by creating the Temp Table with the necessary fields and then run the INSERT INTO. I still get the Syntax error. It seems that it is picking up the items in the ListBox with the loop, but just not inserting it into the table.
I have run a test by creating the Temp Table with the necessary fields and then run the INSERT INTO. I still get the Syntax error. It seems that it is picking up the items in the ListBox with the loop, but just not inserting it into the table.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,
It is a number.
CurrentDb.Execute "INSERT INTO " & strTable & " ([FieldX]) VALUES (" & List2.Column(0, i) & ")"
That did it - thank you. I thought I had tried all the different " and "' variations. Obviously not. Thank you
It is a number.
CurrentDb.Execute "INSERT INTO " & strTable & " ([FieldX]) VALUES (" & List2.Column(0, i) & ")"
That did it - thank you. I thought I had tried all the different " and "' variations. Obviously not. Thank you