MS Query within Excel...

Waxaholic

Board Regular
Joined
Apr 2, 2002
Messages
72
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top