if database exists... MS Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

savalou - 12 Sep 2007 22:12 GMT

Hello,

I'm trying to pull data from access to excel using MS Query. I have
three Access databases (for three different countries) that will
reside in three seperate subdirectories on the harddrive. I want to
create one excel file that will work for all three applications.
Problem is that the data source is hard coded into the MS Query. Is
there some syntax in SQL that I can use to find the correct data
base?

like,

select table1.* from c:\app1\table1.mdb if exists
else select table1.* from c:\app2\table1.mdb if exists
else select table1.* from c:\app3\table1.mdb.

or something like that.

Thanks!

Dave

Reply to this Message

FSt1 - 13 Sep 2007 03:34 GMT

hi Dave,
if i understand you, you want 1 MSQ to select from 3 different db tables. If
the dbs are on your hard drive the table1 in first db will always exist and
be selected first always.
I have worked with MSQs a good deal in the past (not recently) and found
that MSQ is not that sophisticated. My suggestion is to have 1 workbook with
3 sheets and 3 MSQs, one for each of your dbs. you could record a macro to
run each one after the other.
my thoughts.

Regards
FSt1

> Hello,
>
[quoted text clipped - 17 lines]
>
> Dave

Reply to this Message

savalou - 13 Sep 2007 04:00 GMT

Thanks for responding. I guess I didn't explain it very well; I'd
like one MSQ to select from 3 differerent databases (.mdb). One
computer (in 1 of 3 different countries) would only have one of these
databases, each has it's own subdirectory. (This is an application
for collecting, analysing and reporting HIV/AIDS data.) The excel
workbook is a summary of the data in the database. There are more than
40 MSQs in the workbook which is why I'd like to avoid setting up
three different workbooks. Ideally I'd like the MSQs in the workbook
to look for the existing database (there can be only one of the three
in a given country). Is this doable in SQL?

thanks again,

Dave

Reply to this Message