Update external database

[Guest]

I have 2 databases (db1 and db2)

I would like to update Table1 from db1 with data from Table1 in db2 where
the field ReqNum in both of the tables are the same. How would I write a SQL
statement to do this?

Thanks for your help,
Chad

 

[John Vinson]

I have 2 databases (db1 and db2)

I would like to update Table1 from db1 with data from Table1 in db2 where
the field ReqNum in both of the tables are the same. How would I write a SQL
statement to do this?

Thanks for your help,
Chad

Simplest would be to open db1, and use File... Get External Data...
Link to link db2.Table1. Access will alias it as Table1_1 since there
is a duplicate name.

You should then be able to run a Query

UPDATE Table1
INNER JOIN Table1_1 ON Table1.ReqNum = Table1_1.ReqNum
SET Table1.[ThisField] = Table1_1.[ThisField],
Table1.[ThatField] = Table1_1.[ThatField];


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

 

[Guest]

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?

Thanks,
Chad

I have 2 databases (db1 and db2)

I would like to update Table1 from db1 with data from Table1 in db2 where
the field ReqNum in both of the tables are the same. How would I write a SQL
statement to do this?

Thanks for your help,
Chad

Simplest would be to open db1, and use File... Get External Data...
Link to link db2.Table1. Access will alias it as Table1_1 since there
is a duplicate name.

You should then be able to run a Query

UPDATE Table1
INNER JOIN Table1_1 ON Table1.ReqNum = Table1_1.ReqNum
SET Table1.[ThisField] = Table1_1.[ThisField],
Table1.[ThatField] = Table1_1.[ThatField];


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

 

[John Vinson]

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

 

[Guest]

Mr. Vinson,
Thank you again for your response. However, the code you provided still
gave me a syntax error and pointed towards the IN statement. I am not sure
as to how to making this query. I want to avoid link the tables so any more
help would be greatly appreciated.

Thanks,
Chad

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