Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

"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.
0
irfo
Asked:
irfo
1 Solution
 
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
 
Anthony PerkinsCommented:
I think I just said that.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
CetusMODCommented:
Closed, 250 points refunded.
CetusMOD
Community Support Moderator
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now