473,325 Members | 2,712 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Why do I keep getting a syntax error when using Sql INSERT INTO statement

'Microsoft VB 6.3, Access 2002
'Syntax error in the INSERT INTO Statement when the query runs
'How can I make this work?

Private Sub Command52_Click()
On Error GoTo Err_Command52_Click

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim rsSQL As DAO.Recordset
Dim strSQL As String
Dim rst As Recordset
Dim strDocName As String, strLinkCriteria As String


Set dbs = CurrentDb
strDocName = "InfoTable"

'MyShow is a textbox on a form that has the name of the target table
'in its text property.
strSQL = "INSERT INTO" & Me!MyShow & _
"SELECT *. FROM [InfoTable];"


DoCmd.RunSQL strSQL

Exit_Command52_Click:
Exit Sub

Err_Command52_Click:
MsgBox Err.Description
Resume Exit_Command52_Click

End Sub
Jan 19 '10 #1
3 2177
ajalwaysus
266 Expert 100+
There appears to be a "." dot in your code that shouldn't be there i believe, and there needs to be a space.

Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT INTO" & Me!MyShow & _
  2. "SELECT *. FROM [InfoTable];"
Should be:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT INTO" & Me!MyShow & _
  2. " SELECT * FROM [InfoTable];"
Maybe that is it.
-AJ
Jan 19 '10 #2
missinglinq
3,532 Expert 2GB
Also, I don't believe you can use Me in a SQL statement, I think you have to use a full reference to the form instead of Me!MyShow.
Jan 19 '10 #3
nico5038
3,080 Expert 2GB
The stringing of the SQL looks OK, but there's one possible problem when the form holds a tablename with an embedded "-" or space. To be 100% sure add a "[" and "]" surrounding the tablename like:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT INTO [" & Me!MyShow & _
  2. "] SELECT * FROM [InfoTable];"
  3.  
Another problem might be the "missing space after the INTO, the concatenation will give e.g."
INSERT INTOtblX...

When running into trouble with this type of statements I normally place a break point and after the strSQL has been build I use the immediate window to print the contents. Next I copy/paste the text in a query to get the precise error message :-)

Nic;o)
Jan 19 '10 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

699
by: mike420 | last post by:
I think everyone who used Python will agree that its syntax is the best thing going for it. It is very readable and easy for everyone to learn. But, Python does not a have very good macro...
8
by: Rich Grise | last post by:
I think I've finally found a tutorial that can get me started: http://www.zib.de/Visual/people/mueller/Course/Tutorial/tutorial.html and I've been lurking for awhile as well. What happened is,...
4
by: Bob Stearns | last post by:
The statement: merge into nullid.animals_et_in t1 using is3.animals t2 on t1.sire_assoc=t2.assoc and t1.sire_prefix=t2.prefix and t1.sire_regnum=t2.regnum when matched then update set...
3
by: William | last post by:
i am using a dataset and a DataAdapter to update a table with the following schema: ResourceID ProjectID LastName FirstName Year Nov Dec Jan
7
by: kosta | last post by:
hello! one of my forms communicates with a database, and is supposed to add a row to a table using an Insert statement... however, I get a 'oledb - syntax error' exception... I have double...
3
by: Neil Zanella | last post by:
Hello, I am trying to execute ADO.NET INSERT statement where one of the fields is coming from a password HTML control. When I access the text with password.Value and print with Response.Write...
3
by: Nathan Sokalski | last post by:
When trying to submit data to an Access database using ASP.NET I recieve the following error: System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) +41...
1
by: Joe | last post by:
Hello All, I am trying to insert a record in the MS Access DB and for some reason I cannot get rid of error message, System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement. ...
3
by: MP | last post by:
Hi Posted this several hours ago to another ng but it never showed up thought i'd try here. using vb6, ado, .mdb, jet4.0, no access given table tblJob with field JobNumber text(10) 'The...
2
by: RSH | last post by:
I have a rather simple script that transfers data from a SQL server database to an Access database. The procedure is intended to be dynamic so I am basically adding a datarow and then passing the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.