update mutiple tables in different databases

[Guest]

Hi Gangs,

I have tbl_2002 in 2002.mdb, tbl_2003 in 2003.mdb and tbl_2004 in
2004.mdb ...and so on...

Because of thier size, I have to break them into differnet databases.

I link them together by using linked table in master.mdb.

Now I want to make update on all tbl_2002, tbl_2003, tbl_2004 based on
some kind of criteria.

Is there possble to use one single update query instead of having 1
update query for every tables.

 

[Douglas J. Steele]

AFAIK, you'll need a separate query for each database.

 

[John Vinson]

Hi Gangs,

I have tbl_2002 in 2002.mdb, tbl_2003 in 2003.mdb and tbl_2004 in
2004.mdb ...and so on...
Eeeuwwww..

Because of thier size, I have to break them into differnet databases.

All of these .mdb files exceed 1,900 megabytes? You run into errors at
2 GByte. If "their size" is less than several million rows per year,
you'ld really be better off with one table in one database; if they
are more, you should seriously consider a true client/server system
such as SQL/Server, Oracle or MySQL (you can still use Access as a
user interface).

I link them together by using linked table in master.mdb.

Now I want to make update on all tbl_2002, tbl_2003, tbl_2004 based on
some kind of criteria.

Is there possble to use one single update query instead of having 1
update query for every tables.

One query per table. You can string the data together for display or
searching purposes in a UNION query, but that query will not be
updateable.


John W. Vinson[MVP]

 

[Joseph Meehan]

Hi Gangs,

I have tbl_2002 in 2002.mdb, tbl_2003 in 2003.mdb and tbl_2004 in
2004.mdb ...and so on...

Because of thier size, I have to break them into differnet databases.

Microsoft Access allows databases of up to 1 or 2 gig (depending on
version) That is a very large database. Are you sure you need to split
them up into different databases?

If you do maybe you need to normalize the data or if you are using
images you may need to use them in a different way to reduce the size.

Sorry if you already know this, but your problem is really unusual.