Another runtime error 3075

[PsyberFox]

I've got the following SQL statement.

SQL1 = "Update Prod_Assembly_Data_1 " & _
"Set Operator = E.[Name] " & _
"from Prod_Assembly_Data_1 D, X01_Employees E " & _
"where D.ClockNo = E.ClockNo " & _
"go "

DoCmd.RunSQL SQL1

But when running it still gives syntax error. I've even tried to put the
whole string in one line, but no luck... putting it into a message box
produces the (i think) right string to be run.

Can anyone please help?

 

[J_Goddard via AccessMonster.com]

Hi -

The update statement cannot update one record or set of records by selecting
from another. Here is the correct syntax, taken from the A2000 help:

Syntax
UPDATE table
SET newvalue
WHERE criteria;

Check the help file for more details.

John

I've got the following SQL statement.

SQL1 = "Update Prod_Assembly_Data_1 " & _
"Set Operator = E.[Name] " & _
"from Prod_Assembly_Data_1 D, X01_Employees E " & _
"where D.ClockNo = E.ClockNo " & _
"go "

DoCmd.RunSQL SQL1

But when running it still gives syntax error. I've even tried to put the
whole string in one line, but no luck... putting it into a message box
produces the (i think) right string to be run.

Can anyone please help?

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com

 

[PsyberFox]

So you're basically telling me that there's no other way of running this
statement from Access itself? This query obviously runs within SQL no
problem...
Thx
W

Hi -

The update statement cannot update one record or set of records by selecting
from another. Here is the correct syntax, taken from the A2000 help:

Syntax
UPDATE table
SET newvalue
WHERE criteria;

Check the help file for more details.

John

I've got the following SQL statement.

SQL1 = "Update Prod_Assembly_Data_1 " & _
"Set Operator = E.[Name] " & _
"from Prod_Assembly_Data_1 D, X01_Employees E " & _
"where D.ClockNo = E.ClockNo " & _
"go "

DoCmd.RunSQL SQL1

But when running it still gives syntax error. I've even tried to put the
whole string in one line, but no luck... putting it into a message box
produces the (i think) right string to be run.

Can anyone please help?

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com

 

[Douglas J. Steele]

For one thing, "go" isn't a valid instruction in Access.

In theory, you should be able to use the following SQL:

SQL1 = "Update Prod_Assembly_Data_1 INNER JOIN X01_Employees " & _
"ON Prod_Assembly_Data_1.ClockNo = X01_Employees.ClockNo " & _
"SET Operator = X01_Employees.[Name] "

but it would be wrong to do so. <g>

Why store the data redundantly? You can create a query that joins the two
tables together and use that query rather than the table whenever you need
to know the employee's name. What do you do if somehow the name gets updated
in one table but not the other? How will you know which table is correct?

And even though you've at least put square brackets around the field name
Name, I'd strongly advise renaming that field. You should never use reserved
words for your own purposes. For a good discussion on what names to avoid,
see what Allen Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

 

[J_Goddard via AccessMonster.com]

Hi -

So you're basically telling me that there's no other way of running this
statement from Access itself?

I am not saying that at all.

This query obviously runs within SQL no problem...

I am not sure what you mean by "...within SQL...". SQL is a "language" used
to interface with RDMS databases, and virtually all systems use some flavour
of SQL, including of course MS Access. However, since it is a "language", it
does have a set of rules regarding the syntax of the various statement types,
so that the statements can be correctly parsed (interpreted).

The standard syntax of the UPDATE statement is:

Update [tablename] set [field1] = value1, [field2] = value2,... WHERE
conditions.

Your SQL string has a "FROM" in it - which is not part of the syntax, and it
has "go " at the end (what is that for?), thus generating syntax errors when
the statement is executed.

You are on the right track though in that you are putting the SQL into a
string variable and then using DoCmd.RunSQL SQL1 to execute the statement.

Are you perhaps taking your SQL from some other database system, one which
allows the syntax you are using but which does not work in MS Access?

John

So you're basically telling me that there's no other way of running this
statement from Access itself? This query obviously runs within SQL no
problem...
Thx
W

Hi -

[quoted text clipped - 25 lines]

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com

 

[PsyberFox]

Well I'm fairly new to running SQL statements from within Access...

This query is to update certain information in a table when other values
change. The table is originally created by formulas, etc from another table,
so when the user changes some of these values, a re-calculation need to take
place.

Thank you for your help!
W

For one thing, "go" isn't a valid instruction in Access.

In theory, you should be able to use the following SQL:

SQL1 = "Update Prod_Assembly_Data_1 INNER JOIN X01_Employees " & _
"ON Prod_Assembly_Data_1.ClockNo = X01_Employees.ClockNo " & _
"SET Operator = X01_Employees.[Name] "

but it would be wrong to do so. <g>

Why store the data redundantly? You can create a query that joins the two
tables together and use that query rather than the table whenever you need
to know the employee's name. What do you do if somehow the name gets updated
in one table but not the other? How will you know which table is correct?

And even though you've at least put square brackets around the field name
Name, I'd strongly advise renaming that field. You should never use reserved
words for your own purposes. For a good discussion on what names to avoid,
see what Allen Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I've got the following SQL statement.

SQL1 = "Update Prod_Assembly_Data_1 " & _
"Set Operator = E.[Name] " & _
"from Prod_Assembly_Data_1 D, X01_Employees E " & _
"where D.ClockNo = E.ClockNo " & _
"go "

DoCmd.RunSQL SQL1

But when running it still gives syntax error. I've even tried to put the
whole string in one line, but no luck... putting it into a message box
produces the (i think) right string to be run.

Can anyone please help?

 

[PsyberFox]

This query runs within the SQL Query Analyser... apologies for my
terminology...
But yes, essentially it is a "correct" query. Also, w.r.t. the "go" at the
end, I neglected to remove it because I was running 7 updates on one table,
with a "go" inbetween each
W

Hi -

So you're basically telling me that there's no other way of running this
statement from Access itself?

I am not saying that at all.

This query obviously runs within SQL no problem...

I am not sure what you mean by "...within SQL...". SQL is a "language" used
to interface with RDMS databases, and virtually all systems use some flavour
of SQL, including of course MS Access. However, since it is a "language", it
does have a set of rules regarding the syntax of the various statement types,
so that the statements can be correctly parsed (interpreted).

The standard syntax of the UPDATE statement is:

Update [tablename] set [field1] = value1, [field2] = value2,... WHERE
conditions.

Your SQL string has a "FROM" in it - which is not part of the syntax, and it
has "go " at the end (what is that for?), thus generating syntax errors when
the statement is executed.

You are on the right track though in that you are putting the SQL into a
string variable and then using DoCmd.RunSQL SQL1 to execute the statement.

Are you perhaps taking your SQL from some other database system, one which
allows the syntax you are using but which does not work in MS Access?

John

So you're basically telling me that there's no other way of running this
statement from Access itself? This query obviously runs within SQL no
problem...
Thx
W

Hi -

[quoted text clipped - 25 lines]

Can anyone please help?

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com

 

[Douglas J. Steele]

I'll just repeat that it's usually a sign of a bad design to have the same
piece of data in more than one place.

In general, you should store the data once in the correct place, and use
queries to pull all the individual pieces together rather than storing the
data in multiple places.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Well I'm fairly new to running SQL statements from within Access...

This query is to update certain information in a table when other values
change. The table is originally created by formulas, etc from another
table,
so when the user changes some of these values, a re-calculation need to
take
place.

Thank you for your help!
W

For one thing, "go" isn't a valid instruction in Access.

In theory, you should be able to use the following SQL:

SQL1 = "Update Prod_Assembly_Data_1 INNER JOIN X01_Employees " & _
"ON Prod_Assembly_Data_1.ClockNo = X01_Employees.ClockNo " & _
"SET Operator = X01_Employees.[Name] "

but it would be wrong to do so. <g>

Why store the data redundantly? You can create a query that joins the two
tables together and use that query rather than the table whenever you
need
to know the employee's name. What do you do if somehow the name gets
updated
in one table but not the other? How will you know which table is correct?

And even though you've at least put square brackets around the field name
Name, I'd strongly advise renaming that field. You should never use
reserved
words for your own purposes. For a good discussion on what names to
avoid,
see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I've got the following SQL statement.

SQL1 = "Update Prod_Assembly_Data_1 " & _
"Set Operator = E.[Name] " & _
"from Prod_Assembly_Data_1 D, X01_Employees E " & _
"where D.ClockNo = E.ClockNo " & _
"go "

DoCmd.RunSQL SQL1

But when running it still gives syntax error. I've even tried to put
the
whole string in one line, but no luck... putting it into a message box
produces the (i think) right string to be run.

Can anyone please help?

 

[J_Goddard via AccessMonster.com]

I'm not familiar with SQL Query Analyser - is it part of MS Access, or is it
3rd party software which can connect to an MS Access database?

If the SQL you are showing is produced by the query analyser and not by MS
Access, then while it is correct and understood by the SQL Query Analyser, it
is not correct when used (unchanged) in MS Access code. As I said before,
different systems (programs) have different "flavours" (extensions) of SQL,
and they cannot always be ported from one to the other without changes.

But yes, essentially it is a "correct" query.

Bottom line: in MS Access, it is not a correct query.

John

This query runs within the SQL Query Analyser... apologies for my
terminology...
But yes, essentially it is a "correct" query. Also, w.r.t. the "go" at the
end, I neglected to remove it because I was running 7 updates on one table,
with a "go" inbetween each
W

Hi -

[quoted text clipped - 39 lines]

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com