Updating a table in a another database

[rushdib]

How do I update a table in a another database using a table in the current database?

Thank You

 

[tlbroadbent]

The easiest way is to link the table from the other database into the current database. Then you can use it just as any other table.

You can also do updates in VB code. There are a lot of references available in the Access forums at Tek-Tips and elsewhere.

With proper security, Access allows the creation and execution of queries with external references as in the following example.

Update Customers In "C:\DB\Shipments.mdb" Set [CustName]=Ucase([CustName]) Where [CustName]="woolsey"; Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[rushdib]

The above code works if only one table involved, but if I try join a another table (update a table from a table), I am getting an error(it doesn't recognize "IN" clause).

 

[tlbroadbent]

I recommend linking the tables. It is much easier, more flexible and a proven method. As I mentioned previously, you can also create and perform updates in VB code using ADO.

However, you could create a query in the external database and reference that query from the Update query. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin