1

This query keeps telling me I'm missing a semicolon at end of SQL statement, but when I add it, it tells me that there's a character found at the end of the SQL statement, what is wrong here? I'm used to working with SQL Server so this is just plain confusing to me. I'm not sure why Access needs to use a ";" to close the query. I'm just not sure where to include it. 

strSQL = "Insert Into [tempCaseMgmt]Values([planID],[EdId],[OrID],[TheDate],  [TypeID],[UserID],[TimeStart],[TimeEnd],[Unitsled],[Unitsid],[ClientID],  [GenderID])" _
 & " Select * from [dbo_tempDetail] where [userid]= " & [Forms]![frmx]! [txtClientID] & ";"

I'm just trying to make this work. The values I'm selecting and inserting are identical.

HansUp
  • 95,961
  • 11
  • 77
  • 135
FatBoySlim7
  • 232
  • 1
  • 2
  • 13

2 Answers2

2

As suggested by @Martin in one of the comments to his answer, you are mixing up the two forms of INSERT INTO, specifically, 

INSERT INTO ... VALUES ...

and

INSERT INTO ... SELECT ...

In my own simplified example this fails with "Run-time error '3137': Missing semicolon (;) at end of SQL statement."

Dim strSQL As String
strSQL = "Insert Into [tempCaseMgmt]Values([planID],[UserID])" _
        & " Select * from [dbo_tempDetail] where [userid]=1" & ";"
Dim cdb As DAO.Database
Set cdb = CurrentDb
cdb.Execute strSQL, dbFailOnError

whereas this works

Dim strSQL As String
strSQL = "Insert Into [tempCaseMgmt] ([planID],[UserID])" _
        & " Select * from [dbo_tempDetail] where [userid]=1" & ";"
Dim cdb As DAO.Database
Set cdb = CurrentDb
cdb.Execute strSQL, dbFailOnError

Note that the Values keyword has been omitted.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
1

Without looking too deep into it, I would say, you are missing a white space directly in front of your select Statement.

Update:

You missed a second white space in front of the "Values" keyword. Did you copy pasted this query, or did you just wrote it in?

I would say, that you try to use a mixed up statement syntax for the Insert Into Statement. Values is used for single record appending. That means you should have an semicolon after the closing parenthesis. For the interpreter the Select is a completely new Statement. I goes that is not what you want.

Use the multi record syntax for insert into:

"Insert Into [tempCaseMgmt] \n
Select * from [dbo_tempDetail] where [userid]= " & [Forms]![frmx]![txtClientID] & ";"

In this case column naming should be identically best regards Martin

  • I'm missing a semicolon but doesnt matter where I put it, i keep getting an error – FatBoySlim7 Mar 14 '16 at 20:37
  • 1
    Does that helps: http://stackoverflow.com/questions/74162/how-to-do-insert-into-a-table-records-extracted-from-another-table –  Mar 14 '16 at 20:46
  • No it doesn't - different case. – FatBoySlim7 Mar 14 '16 at 20:51
  • 1
    It looks like you are mixing up Single and Multi record Syntax. https://msdn.microsoft.com/en-us/library/bb208861(v=office.12).aspx another question: What is the the missing whitespace infront of 'value'? –  Mar 14 '16 at 21:06
  • 1
    So for access the Statement would end After the value parenthesis and select would be a New Statement. –  Mar 14 '16 at 21:10