MS Query within Excel...
MS Query within Excel...
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: MS Query within Excel...

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    New Member
    Join Date
    Feb 2002
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com