HELP! UPDATE syntax that work in sql server and access vba

[ges]

have upsized access to sql2005 server. Access front end. I have an open form
for user to type in data to update the table link to sql2005 server.
I have to re-write the vba code in access form to update tables that's link
to sql server.

In sql server (tsql) I use syntax as follow:
UPDATE table1
SET field1 = 'x',
field2 = 'y',
field3 = 'z'
FROM table2 INNER JOIN table1
ON table2.ID = table1.ID
WHERE (table2.Account ='97') AND (table2.FileType ='Sales')

I can execute the update above in sql server 2005 with no problem, the row
was updated fine. But
it give me error when I run in thru opened form vba in Access. it said there
is syntax error (missing operator in 'z' and FROM table 2 INNER join table 1)

I also tried the following syntax in access query:
UPDATE table1
INNER JOIN table2
ON table1.ID = table2.ID
SET field1 = 'x',
field2 = 'y',
field3 = 'z'
WHERE (table2.Account ='97') AND (table2.FileType ='Sales')

It also give me the same error.

Anyone can help me with the syntax that will work in access also work in sql
server?
Thanks in advance for any input.

Ges

 

[Sylvain Lafontaine]

When you are working with Access ODBC linked tables, you must use the JET
syntaxe. You should ask in a newsgroup about queries under Access how to
correctly translate this update query from T-SQL to JET.

Also, this newsgroup is about ADP and SQL-Server and has nothing to do with
ODBC linked tables.

 

[ges via AccessMonster.com]

Noted, thanks Sylvain. I have figured out the problem, thanks again for the
response.

ges

When you are working with Access ODBC linked tables, you must use the JET
syntaxe. You should ask in a newsgroup about queries under Access how to
correctly translate this update query from T-SQL to JET.

Also, this newsgroup is about ADP and SQL-Server and has nothing to do with
ODBC linked tables.

have upsized access to sql2005 server. Access front end. I have an open
form

[quoted text clipped - 36 lines]

Ges