Re: Update external database

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: John Vinson (jvinson_at_STOP_SPAM.WysardOfInfo.com)
Date: 10/18/04 

Date: Mon, 18 Oct 2004 16:42:23 -0600

On Mon, 18 Oct 2004 11:41:03 -0700, "Chad"
<Chad@discussions.microsoft.com> wrote:

>Mr. Vinson,
> Thank you for yoru reply, unfortunately I believe I might not have
>accurately depicted what I need. I actually need to updated a table in
>another database.... Here is the statement that I have:
>
>UPDATE Table1 In "C:\db1.mdb"
>INNER JOIN Table1 AS Table1_1 ON Table1.Req = Table1_1.Req
>SET Table1.Field2 = [Table1_1].[Field1], Table1.Field3 = [Table1_1].[Field2]
>WHERE (((Table1.field4)=True));
>
>
>But when I run this I get a syntax error and it points to 'INNER JOIN'. Is
>there a way to do this?

Yes. The way I suggested: using File... Get External Data... Link to
link to the remote table rather than using the IN clause.

I think your IN syntax can be made to work though: try running it from
db1.mdb, to update the local table instead of the remote table.

UPDATE Table1
INNER JOIN Table1 In "C:\db2.mdb" AS Table1_1
ON Table1.Req = Table1_1.Req
SET Table1.Field2 = [Table1_1].[Field1], Table1.Field3 =
[Table1_1].[Field2]
WHERE (((Table1.field4)=True));

                  John W. Vinson[MVP]
             Join the online Access Chats
        Tuesday 11am EDT - Thursday 3:30pm EDT
      http://community.compuserve.com/msdevapps

Relevant Pages

  • Re: Syntax Error
    ... formulation is less error prone, in syntax, since it is easier to spot which ... > Can anyone spot an error in this SQL? ... I am getting an error 'Syntax error ... > FROM (tblSpendbySupplier INNER JOIN tblSuppliers ON ...
    (microsoft.public.access.queries)
  • Remote table hint example needed for simple join
    ... table is being joined to table on a linked server and the select needs ... to run on the remote table. ... FROM Books INNER JOIN BookOrders ON Books.TitleID = BookOrders.TitleID ... server using the remote table hint. ...
    (microsoft.public.sqlserver.programming)
  • Re: HELP Please with this update query
    ... , BulkFlow = FL.BulkFlow ... Does SQL not allow you to join two tables to ... > UPDATE tReceived_Loans RL INNER JOIN tFunded_Loans AS FL ... > syntax error. ...
    (microsoft.public.sqlserver.programming)
  • Using LIKE in SQL
    ... FROM REP_LIST INNER JOIN (CS_LIST INNER JOIN ACCOUNT_LIST ... I have named the varible containing the SQL ... statement: strsearch. ... it gives me no syntax error, ...
    (microsoft.public.access.modulesdaovba)
  • Re: using sp_executesql for dynamic search in stored procedure.
    ... Hi Adam, when I insert the case statements I am getting a syntax error ... (Incorrect syntax near the keyword 'END), ... FROM dbo.DML$Data_Item_Log_T INNER JOIN ...
    (microsoft.public.sqlserver.server)