"Incorrect syntax in Update" Error, since upsizing for Access to SQL server.

Hello Post readers,

I have recently upsized from access to SQL server using the upsize wizard and i am now going through the code and trying to make it SQL Server friendly.
I have come accross and error that i cant figure out.

I first got an error

"You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column."

to which i used this work around (Yes i tried putting in the dbseechanges but did not work).

cmdUpdateProjects.CommandType = adCmdText
cmdUpdateProjects.CommandText = " UPDATE Projects Set Work = " & "'" & rsTasks.Fields ("sumofwork").Value & "'" & " Where ProjectId = " & "'" & rsTasks.Fields ("ProjectId").Value & "'"
cmdUpdateProjects.ActiveConnection = CurrentProject.Connection
cmdUpdateProjects.Execute


Now i get an error saying that "inccorect syntax in UPDATE....


Could anyone please check if my syntax is correct, provide me with an alternative and generally give me a heads up when doing an Access to SQL server Upsize.

Thanks in advanced.
All responses appreciated.
irfoAsked:
Who is Participating?
 
CetusMODCommented:
Closed, 250 points refunded.
CetusMOD
Community Support Moderator
0
 
Anthony PerkinsCommented:
See if this makes any difference.  Change:
cmdUpdateProjects.CommandText = " UPDATE Projects Set Work = " & "'" & rsTasks.Fields ("sumofwork").Value & "'" & " Where ProjectId = " & "'" & rsTasks.Fields ("ProjectId").Value & "'"

To:
cmdUpdateProjects.CommandText = "UPDATE Projects Set Work = '" & rsTasks.Fields("sumofwork").Value & "' Where ProjectId = '" & rsTasks.Fields ("ProjectId").Value & "'"

If you are still getting the same error than output the result of cmdUpdateProjects.CommandText prior to the Execute method and you should see the error immediatelly.  If you don't post the result here.
0
 
hiteshgupta1Commented:
try to print the query and check whether it is correct or not
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Anthony PerkinsCommented:
I think I just said that.
0
 
irfoAuthor Commented:
Hi guys,

Sorry same error.
0
 
Anthony PerkinsCommented:
>>Sorry same error.<<
Right, now can you "output the result of cmdUpdateProjects.CommandText prior to the Execute method and you should see the error immediatelly.  If you don't post the result here."
0
 
irfoAuthor Commented:

I guessing that you mean that you want me to check it in the imediate window.

(1)
cmdUpdateProjects.CommandText = " UPDATE Projects Set Work = " & rsTasks.Fields("sumofwork").Value & " WHERE ProjectId = " & rsTasks.Fields("ProjectId").Value
     
I get;

UPDATE Projects Set Work = 80 WHERE ProjectId = 4

(2)
 cmdUpdateProjects.CommandText = " UPDATE Projects Set Work = " & "'" & rsTasks.Fields("sumofwork").Value & "'" & " WHERE ProjectId = " & "'" & rsTasks.Fields("ProjectId").Value & "'"

I get;
     
 UPDATE Projects Set Work = '80' WHERE ProjectId = '4'

Both give the same error.
0
 
irfoAuthor Commented:
Another thing is that the original code before i changed it to the above was

CurrentDb.Execute "UPDATE Projects Set Work = " & rsTasks.Fields("sumofwork").Value & " WHERE ProjectId = " & rsTasks.Fields("ProjectId").Value
     

With this i would get the error

You must use the dbSeeChanges option with OpenRecordset when accessing
a SQL Server table that has an IDENTITY column.

If anyone has any recomendations on how i could resolve this option then please let me know.

Thanks.
0
 
Anthony PerkinsCommented:
>>I guessing that you mean that you want me to check it in the imediate window.<<
No, that is not what I mean.  I know it gives an error and I have told you it would, repeatedly. All we are trying to do is find out WHY it is giving you an error.  So once again, please do the following from the Immediate window PRIOR to the Execute method:

? cmdUpdateProjects.CommandText

And post the results here.
0
 
irfoAuthor Commented:
And that is what i have done. Please look closer, all i have done is tried two different version of the syntax.

The results of what you are asking for are after

"I Get;"

Thanks
0
 
irfoAuthor Commented:
Here is the output just to end any confusion.

? cmdUpdateProjects.CommandText
UPDATE Projects Set Work = 80 WHERE ProjectId = 4
0
 
Anthony PerkinsCommented:
There is nothing wrong with that syntax.  Please post all the relevant code that executes that UPDATE statement, starting with:

DIM cmdUpdateProjects As ADODB.Connection
Set cmdUpdateProjects = New ADODB.Connection

Also, post your connection string, the environment you are using (VB or MS Access), whether you are linking to the SQL tables or accessing them directly.
0
 
irfoAuthor Commented:
hello, all sorted now.

It was the 'work' column name. Because it is a reserved word it needs to be in [].

Thanks anyways.
0
 
Anthony PerkinsCommented:
Yep.  That is correct that is a reserved in JET but not in MS SQL Server, since you appear to be using JET or ODBC (as opposed to going through directly to MS SQL Server) you will have to enclose it in [].  

For a complete list of JET reserved keywords see here:

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/default.aspx?scid=kb;en-us;321266

Now please close the question.  See here from the EE Help:

I answered my question myself. What do I do?
http://www.experts-exchange.com/Programming/help.jsp#hi70
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.