MS Access project with SQL Server

Author

SQL Syntax problem

AkAlan via AccessMonster.com

2006-03-06, 7:30 pm

I am getting a syntax error "Line 1 incorrect syntax near ' = ' " when I
run the following:

strSQL = "APPEND tblEarByStatus SET RedHours = " & sngRedTime _
& ", Status = " & intCurEc _
& ", ParcTag = '" & strCurEquip & "'"

With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = strSQL
.CommandType = adCmdText
.Execute
End With

Here is what is in strSql :

"APPEND tblEarByStatus SET RedHours = 126.5, Status = 10, ParcTag = '00176'"

This looks like it should work but it doesn't. The variable types are as
follows :

sngRedTime is single
intCurEc is integer
strCurEquip is string.
Is it obvious what I'm doing wrong?

I'm new to project and SQl Server and I get my XXX kicked by syntax errors
every time I try something different. Does anyone know where I can find a
good reference for how to correctly write SQL statements.
Thanks for any help.

--
Message posted via http://www.webservertalk.com

Douglas J. Steele

2006-03-06, 8:23 pm

There is no Append operator in SQL.

strSQL = "INSERT INTO tblEarByStatus " & _
"(RedHours, Status, ParcTag) " & _
"VALUES(" & sngRedTime & ", " & _
intCurEc & ", '" & strCurEquip & "'"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

"AkAlan via webservertalk.com" <u18147@uwe> wrote in message
news:5cdfee5a1e27a@u
we...
>I am getting a syntax error "Line 1 incorrect syntax near ' = ' " when I
> run the following:
>
> strSQL = "APPEND tblEarByStatus SET RedHours = " & sngRedTime _
> & ", Status = " & intCurEc _
> & ", ParcTag = '" & strCurEquip & "'"
>
> With cmd
> .ActiveConnection = CurrentProject.Connection
> .CommandText = strSQL
> .CommandType = adCmdText
> .Execute
> End With
>
> Here is what is in strSql :
>
> "APPEND tblEarByStatus SET RedHours = 126.5, Status = 10, ParcTag =
> '00176'"
>
> This looks like it should work but it doesn't. The variable types are as
> follows :
>
> sngRedTime is single
> intCurEc is integer
> strCurEquip is string.
> Is it obvious what I'm doing wrong?
>
> I'm new to project and SQl Server and I get my XXX kicked by syntax errors
> every time I try something different. Does anyone know where I can find a
> good reference for how to correctly write SQL statements.
> Thanks for any help.
>
> --
> Message posted via http://www.webservertalk.com

Sylvain Lafontaine

2006-03-06, 8:23 pm

I might be wrong but I don't think that there is an APPEND command in
SQL-Server; you should use the Insert command instead:

Insert tblEarByStatus (RedHours, Status, ParcTag)
Values (126.5, 10, '00176')

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

"AkAlan via webservertalk.com" <u18147@uwe> wrote in message
news:5cdfee5a1e27a@u
we...
>I am getting a syntax error "Line 1 incorrect syntax near ' = ' " when I
> run the following:
>
> strSQL = "APPEND tblEarByStatus SET RedHours = " & sngRedTime _
> & ", Status = " & intCurEc _
> & ", ParcTag = '" & strCurEquip & "'"
>
> With cmd
> .ActiveConnection = CurrentProject.Connection
> .CommandText = strSQL
> .CommandType = adCmdText
> .Execute
> End With
>
> Here is what is in strSql :
>
> "APPEND tblEarByStatus SET RedHours = 126.5, Status = 10, ParcTag =
> '00176'"
>
> This looks like it should work but it doesn't. The variable types are as
> follows :
>
> sngRedTime is single
> intCurEc is integer
> strCurEquip is string.
> Is it obvious what I'm doing wrong?
>
> I'm new to project and SQl Server and I get my XXX kicked by syntax errors
> every time I try something different. Does anyone know where I can find a
> good reference for how to correctly write SQL statements.
> Thanks for any help.
>
> --
> Message posted via http://www.webservertalk.com

AkAlan via AccessMonster.com

2006-03-06, 8:23 pm

Thanks Douglas, I wasn't even thinking I had the wrong command. I got it to
work after adding the closing parenthesis.

Douglas J. Steele wrote:[color=darkred
]
>There is no Append operator in SQL.
>
>strSQL = "INSERT INTO tblEarByStatus " & _
> "(RedHours, Status, ParcTag) " & _
> "VALUES(" & sngRedTime & ", " & _
> intCurEc & ", '" & strCurEquip & "'"
>
>[quoted text clipped - 27 lines]

--
Message posted via http://www.webservertalk.com

Malcolm Cook

2006-03-07, 8:25 pm

There is no APPEND command in SQL as implemented by MS SQL.
You probably want to user the INSERT command.
Look it up in Books On Line

Cheers,

--
Malcolm Cook
Stowers Institute for Medical Research - Kansas City, MO USA

"AkAlan via webservertalk.com" <u18147@uwe> wrote in message news:5cdfee5a1e27a@u
we...
>I am getting a syntax error "Line 1 incorrect syntax near ' = ' " when I
> run the following:
>
> strSQL = "APPEND tblEarByStatus SET RedHours = " & sngRedTime _
> & ", Status = " & intCurEc _
> & ", ParcTag = '" & strCurEquip & "'"
>
> With cmd
> .ActiveConnection = CurrentProject.Connection
> .CommandText = strSQL
> .CommandType = adCmdText
> .Execute
> End With
>
> Here is what is in strSql :
>
> "APPEND tblEarByStatus SET RedHours = 126.5, Status = 10, ParcTag = '00176'"
>
> This looks like it should work but it doesn't. The variable types are as
> follows :
>
> sngRedTime is single
> intCurEc is integer
> strCurEquip is string.
> Is it obvious what I'm doing wrong?
>
> I'm new to project and SQl Server and I get my XXX kicked by syntax errors
> every time I try something different. Does anyone know where I can find a
> good reference for how to correctly write SQL statements.
> Thanks for any help.
>
> --
> Message posted via http://www.webservertalk.com

Sponsored Links