Update Syntax error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

OK - I am going brain dead - I cannot spot what is wrong with the follow
statement

DoCmd.RunSQL "UPDATE [License] SET Desc= """ & Buildnum & """ " & _
"WHERE Id= 1 WITH OWNERACCESS OPTION"

Desc is a memo field, Id is a number field and Buildnum is a String variable
- have tried several different iterations of "" but with no success!

Any help would be GREATLY appreciated - battled with this all day!!

Jerry
 
I copied and paste your code and it worked great, what is the problem?
Do you get error message, or no value is updated?

Can you post the complate code?
 
I believe that Desc is a reserved word, so probably isn't a great choice for
a field name. If you can't rename it, try putting square brackets around it:

DoCmd.RunSQL "UPDATE [License] SET [Desc]= """ & Buildnum & """ " & _
"WHERE Id= 1 WITH OWNERACCESS OPTION"

Another possible problem: does Buildnum contain any double quotes in it? If
so, try

DoCmd.RunSQL "UPDATE [License] SET [Desc]= """ & _
Replace(Buildnum, Chr$(34), Chr$(34) & Chr$(34)) & """ " & _
"WHERE Id= 1 WITH OWNERACCESS OPTION"
 
First, you are missing the semicolon.
DoCmd.RunSQL "UPDATE [License] SET Desc= """ & Buildnum & """ " & _
"WHERE Id= 1 WITH OWNERACCESS OPTION;"
Next, I suggest you change your DoCmd.RunSQL to:

Currentdb.Execute("UPDATE [License] SET Desc= """ & Buildnum & """ " & _
"WHERE Id= 1 WITH OWNERACCESS OPTION;")

It is so much faster it is hard to believe.
 
AFAIK, the semicolon is never mandatory.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Klatuu said:
First, you are missing the semicolon.
DoCmd.RunSQL "UPDATE [License] SET Desc= """ & Buildnum & """ " & _
"WHERE Id= 1 WITH OWNERACCESS OPTION;"
Next, I suggest you change your DoCmd.RunSQL to:

Currentdb.Execute("UPDATE [License] SET Desc= """ & Buildnum & """ " & _
"WHERE Id= 1 WITH OWNERACCESS OPTION;")

It is so much faster it is hard to believe.

JWS315 said:
OK - I am going brain dead - I cannot spot what is wrong with the follow
statement

DoCmd.RunSQL "UPDATE [License] SET Desc= """ & Buildnum & """ " &
_
"WHERE Id= 1 WITH OWNERACCESS OPTION"

Desc is a memo field, Id is a number field and Buildnum is a String
variable
- have tried several different iterations of "" but with no success!

Any help would be GREATLY appreciated - battled with this all day!!

Jerry
Click to expand...
Click to expand...
 
I realized that after I posted, but since I never use the RunSQL, I was
thinking in terms of a standard SQL statement where I believe it is.

Douglas J. Steele said:
AFAIK, the semicolon is never mandatory.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Klatuu said:
First, you are missing the semicolon.
DoCmd.RunSQL "UPDATE [License] SET Desc= """ & Buildnum & """ " & _
"WHERE Id= 1 WITH OWNERACCESS OPTION;"
Next, I suggest you change your DoCmd.RunSQL to:

Currentdb.Execute("UPDATE [License] SET Desc= """ & Buildnum & """ " & _
"WHERE Id= 1 WITH OWNERACCESS OPTION;")

It is so much faster it is hard to believe.

JWS315 said:
OK - I am going brain dead - I cannot spot what is wrong with the follow
statement

DoCmd.RunSQL "UPDATE [License] SET Desc= """ & Buildnum & """ " &
_
"WHERE Id= 1 WITH OWNERACCESS OPTION"

Desc is a memo field, Id is a number field and Buildnum is a String
variable
- have tried several different iterations of "" but with no success!

Any help would be GREATLY appreciated - battled with this all day!!

Jerry
Click to expand...
Click to expand...
Click to expand...
 
The problem as Doug pointed out was the use of Desc for a field name - Put
brackets around it and everything worked perfect!!

Thanks for everyone's contibution - I went out to dinner and came back with
5 responses WOW!!

Jerry

Klatuu said:
I realized that after I posted, but since I never use the RunSQL, I was
thinking in terms of a standard SQL statement where I believe it is.

Douglas J. Steele said:
AFAIK, the semicolon is never mandatory.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Klatuu said:
First, you are missing the semicolon.
DoCmd.RunSQL "UPDATE [License] SET Desc= """ & Buildnum & """ " & _
"WHERE Id= 1 WITH OWNERACCESS OPTION;"
Next, I suggest you change your DoCmd.RunSQL to:

Currentdb.Execute("UPDATE [License] SET Desc= """ & Buildnum & """ " & _
"WHERE Id= 1 WITH OWNERACCESS OPTION;")

It is so much faster it is hard to believe.

:

OK - I am going brain dead - I cannot spot what is wrong with the follow
statement

DoCmd.RunSQL "UPDATE [License] SET Desc= """ & Buildnum & """ " &
_
"WHERE Id= 1 WITH OWNERACCESS OPTION"

Desc is a memo field, Id is a number field and Buildnum is a String
variable
- have tried several different iterations of "" but with no success!

Any help would be GREATLY appreciated - battled with this all day!!

Jerry
Click to expand...
Click to expand...
Click to expand...
 
I don't believe it's mandatory anywhere in Access.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Klatuu said:
I realized that after I posted, but since I never use the RunSQL, I was
thinking in terms of a standard SQL statement where I believe it is.

Douglas J. Steele said:
AFAIK, the semicolon is never mandatory.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Klatuu said:
First, you are missing the semicolon.
DoCmd.RunSQL "UPDATE [License] SET Desc= """ & Buildnum & """ " & _
"WHERE Id= 1 WITH OWNERACCESS OPTION;"
Next, I suggest you change your DoCmd.RunSQL to:

Currentdb.Execute("UPDATE [License] SET Desc= """ & Buildnum & """ " & _
"WHERE Id= 1 WITH OWNERACCESS OPTION;")

It is so much faster it is hard to believe.

:

OK - I am going brain dead - I cannot spot what is wrong with the follow
statement

DoCmd.RunSQL "UPDATE [License] SET Desc= """ & Buildnum & """ " &
_
"WHERE Id= 1 WITH OWNERACCESS OPTION"

Desc is a memo field, Id is a number field and Buildnum is a String
variable
- have tried several different iterations of "" but with no success!

Any help would be GREATLY appreciated - battled with this all day!!

Jerry
Click to expand...
Click to expand...
Click to expand...
 
DoCmd.RunSQL "UPDATE [License] SET Desc= """ & Buildnum & """
" & _ "WHERE Id= 1 WITH OWNERACCESS OPTION"
Click to expand...


I am glad that you have been updated about the fieldName and about using
DAO.Execute rather than RunSQL.

One thing about the RWOP (WITH OWNERACCESS OPTION): since this is newly-
created SQL code, its owner will be whoever is running the code so this
line is saying, WITH MYACCESS OPTION, which is what the db engine would
assume anyway.

I doubt that the RWOP clause is doing any harm, but please don't imagine
that it's contributing any security feature. If the user isn't allowed to
see the License table, then this query will not run either way.

Just a thought



Tim F
 
Back
Top