Updating table in second Db from current Db

[GrahamT]

I am OK creating a SQL query to update a table in a second database from a look-up table in the second database, and I can create a query to update a table in the current database from a look-up table in the current database - simple.

However, I am trying to create an Update Query to update a table in a second database from a look-up table in the current database.

(and I want to avoid copying the look-up table to the second database.)

Does anyone know how to do this?

 

[GrahamT]

I am OK creating a SQL query to update a table in a second database from a look-up table in the second database, and I can create a query to update a table in the current database from a look-up table in the current database - simple.

However, I am trying to create an Update Query to update a table in a second database from a look-up table in the current database.

(and I want to avoid copying the look-up table to the second database.)

Does anyone know how to do this?

Any ideas anyone?

 

[dhx10000]

This forums sucks, I never get my questions answered. I would recommend this one: http://www.tek-tips.com/threadminder.cfm?pid=701

 

[GrahamT]

This forums sucks, I never get my questions answered. I would recommend this one: http://www.tek-tips.com/threadminder.cfm?pid=701

Thanks dhx10000. A good site for answers, I hope. Actually, it looks familiar - some years ago I think came across it, but then lost the URL during a system failure (lesson: back up your favourites, too).

So thanks again for helping!

 

[RV]

This forums sucks, I never get my questions answered. I would recommend this one: http://www.tek-tips.com/threadminder.cfm?pid=701

You started 9 theards and got a reaction plus solution (I guess as you don't always respond to provided suggestions) on 6 of them.
Seems you're exaggerating a bit mate

RV

 

[RV]

However, I am trying to create an Update Query to update a table in a second database from a look-up table in the current database.

You'll have to refer to the full location of your second database.
For instance, if you'd like to retrieve all rows from a table tblCustomer located in a database not being your current database, you'd use a statement like

SELECT *
FROM tblCustomer IN 'C:\test\Customer.mdb';

RV

 

[GrahamT]

You'll have to refer to the full location of your second database.
For instance, if you'd like to retrieve all rows from a table tblCustomer located in a database not being your current database, you'd use a statement like

SELECT *
FROM tblCustomer IN 'C:\test\Customer.mdb';

RV

OK RV, you just try it in an Update Query, like I originally suggested!

Go on, try it!

Access treats both the main table and the lookup table as coming from the same database, wherever its located!!

 

[Jon K]

According to the syntax in the help files, the IN Clause can be used in a Select or Append query, but not in an Update query.

You can link the table from the second database to the current database and update it as if it were in the current database.
.

 

[GrahamT]

According to the syntax in the help files, the IN Clause can be used in a Select or Append query, but not in an Update query.

You can link the table from the second database to the current database and update it as if it were in the current database.
.

Thanks Jon K. I too had a sudden flash of inspiration along the same lines (as you do when sitting on the loo - best place for cogitating).

One way, as you say Jon K, is by linking the table from the second Db to the current Db: by creating a Select Query referencing all the fields of the table in the second Db, and using the Select Query in the Update Query.

By the way, the help files must be wrong because you can use the IN Clause in an Update Query - the only problem is that it seems to apply to ALL Dbs in the query.

Does anyone have any better ways of doing this - preferably in just one query?