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

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.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"ges" <u44973@uwe> wrote in message news:894385be4804b@xxxxxx
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

--
Ges Khu
Geskhu@xxxxxxxxx
Access Beginner

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-adp-sqlserver/200808/1



.

Relevant Pages

  • Re: CREATE TEMPORARY TABLE
    ... suggestions that this syntax relates to SQL Server or an ADP. ... CREATE TABLE, or any of the DDL statements, with non-Microsoft Jet ... for non-Jet engine databases. ... The syntax includes WITH COMPRESSION which only applies to Jet ...
    (microsoft.public.access.tablesdbdesign)
  • Re: SQL-Server nach dBase
    ... The syntax that you are using is supported only by the Microsoft Jet OLE DB ... Jet Provider, the easiest solution would be to create an Access database ... SQL Server, then use SQL syntax over a SQLClient connection to SELECT INTO ...
    (microsoft.public.de.german.entwickler.dotnet.datenbank)
  • Re: Is Access SQL FROM Clause different from MS SQL Server SQL?
    ... The specific question about tbl4 in this query can be answered. ... syntax and functionality of this is the same as in SQL Server. ... CROSS JOIN in SQL Server's Books Online, ... The syntax of representing a CROSS JOIN by just a comma may be less ...
    (microsoft.public.access.queries)
  • RE: Access project vs. Access db
    ... Because the syntax you are using is asking the database engine to compare ... Jet won't warn you of this logic error, but SQL Server does. ... Where can I get general/ongoing help with what may be a host of these ...
    (microsoft.public.access.modulesdaovba)
  • Re: Better "Join" vs "Where" clause?
    ... The result differs even among Jet versions: ... With Jet 3.5, you got no record, but with MS SQL Server, ... All of my attempts to use that syntax in Jet had failed ... I know about the different update/delete syntax - I thought we were ...
    (microsoft.public.access.queries)