CODEWITHSUNDEEP
sqlvbams-accessselect
FatBoySlim7
FatBoySlim7

Reputation: 232

MS Access query assistance INSERT

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.

Upvotes: 1

Views: 116

Answers (2)

user6034077
user6034077

Reputation:

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

Upvotes: 1

Gord Thompson
Gord Thompson

Reputation: 123839

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.

Upvotes: 2

Related Questions