Running queries from multiple Access Databases

[Guest]

Hi,

What would your suggestion be to the following scenario?

I'm creating a new database (DBA-main) where it takes data from two
secondary databases (DBA-sub1) and (DBA-sub2) and expands on them. These
subs are maintained by different people. Both DBA-subs consists of make,
append and select queries; however, the respective final products are just
select queries, tables are not created. The raw data which these two subs
grab are updated periodically, however, unless the macro is run in each of
these two databases, they would not be reflected in the queries. They have
append queries somewhere along the process.

The most inefficient way of updating the DBA-main is to set up a make table
query for each of the DBA-subs and have DBA-main link to these tables. To
update, someone would open DBA-sub1 and run the make table query, then go to
DBA-sub2 and do the same and finally go to DBA-main to run the remaining
steps (select, append, make....).

Is it possible to setup a macro in Access to open and run macros for other
Access databases. Then I won't have to go into each DBA-sub to run it (the
number of sub dbas are expected to increase).

Is it possible to grab data from a select query without actually making the
data into a table? I tried import query, but then it requires all supporting
tables.


Thanks,

Carmen

 

[Allen Browne]

Alternatives:

a) You can link the tables from the 2 databases:
File | Get External | Link

b) If you are comfortable with SQL, you can also specify the source file in
your query, e.g.:
SELECT Employees.* FROM Employees IN 'C:\Northwind.mdb';
You could turn that into a pair of Append querise to add the data from each
database into your local copy so you can manipulate it.

c) A UNION query might be useful to combine the results from the 2 sources.

 

[Guest]

Is Macros an option? Can I set up a Macro in DBA-main to run the queries in
the DBA-subs.

 

[Allen Browne]

No. You cannot execute a macro that exists in a different mdb frorm your
own.