MS Query within Excel...

[Waxaholic]

I have a spreadsheet that gets its data from Access. The problem i ran into today is that if i move the spreadsheet and DB to a different drive/PC, all connections are lost. I do not want to manually update each and every connection when i distribute it to another user or pc. Is there a way of modifying the SQL statement "FROM" so that it will always look for the DB in the same directory as the spreadsheet?

Here is the current SQL "FROM" statement:

FROM E:SPMNETOM_Collected_Access`.ICPHO2 ICPHO2

This would be a tremendous time saver as well as headache cure.

Thanks,

Waxaholic

[Paul-Johnson]

Can you just save the Excel file and database on a shared server ?

_________________
Paul

[ This Message was edited by: Paul-Johnson on 2002-04-04 09:53 ]

[Waxaholic]

This is not an option. I do all of the design on the Spreadsheets and DB at home (E: drive). From time to time i use it at work (C: drive), and then at other times i take it on the road (D: drive laptop). If i update the file at home and copy it to either of the other pc's, all connection references on those pc's would be overwritten with the home machines references.

Waxaholic

[Russell Hauf]

If you use VBA to run your query, you can do it. Something like this:

"...FROM " & ThisWorkbook.Path & "OM_Collected_Access`.ICPHO2 ICPHO2"

Hope this helps,

Russell