Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Duesseldorf, Germany
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel external data from access (Access 97/ Excel 97)

    I use external data in Excel from access via the Access ODBC Driver. When the source database is relocated, Excel cannot find the database anymore as it stores the absolute path. Changing the ODBC source doesn't help therefore.
    Is the a workaround? Has this behaviour changed in Office XP? <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

  2. #2
    LuiGreco
    Guest

    Re: Excel external data from access (Access 97/ Excel 97)

    <P ID="nt"><font size=-1>(No Text)</font>

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Duesseldorf, Germany
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel external data from access (Access 97/ Excel 97)

    Was your empty answer an error or was it an accident

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel external data from access (Access 97/ Excel 97)

    If you move the database, then how should ODBC know where you've moved it to? Perhaps you moved it to a floppy disk drive, and then what good does it do if it somehow follows it?

    If you've got machines which are relying on your database to retrieve data, then you will have to live with the fact that that database must not move or change name, otherwise those machines will not be able to use it anymore.

    The 'workaround' would be to NOT move the source database!

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    Duesseldorf, Germany
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel external data from access (Access 97/ Excel 97)

    ODBC knows because I told it where the database has gone to. But Excel/MSQuery don't care. Of course I know that no one can access my database if I wear it in my pocket on a floppy. The reason is that I want to give the entire package of combined Excel & Access files to a collegue who doesn't want to construct exactly the same file structure I have got. Also if you develop somtehing on your local drive and want to place it on a network drive you will get the same problem

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel external data from access (Access 97/ Excel 97)

    I'm not clear on the problem you're having. Are you trying to query Access from Excel using existing saved MS Queries? In that case, it will not adapt itself to a different location for the database. Are you doing this from code, for the MS Query grid or what?

    You can't expect it to adapt itself to an entirely different file structure without any human intervention. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel external data from access (Access 97/ Excel 97)

    Aha - so you changed the ODBC setting and MSQuery doesn't pick up the change?

    Then I misunderstood your original post, apologies.

    Perhaps one way to workaround it would be to re-bind the query to the ODBC entry at runtime. Have you tried this?

  8. #8
    Star Lounger
    Join Date
    Jan 2001
    Location
    Duesseldorf, Germany
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel external data from access (Access 97/ Excel 97)

    The queries are not saved externally, but stored in the Excel sheet. The normal behaviour I would expect, is that EXCEL/MSQUERY look into ODBC to get the location of the access database, but they do only the first time, when the query is set-up, the they use the absolute path from the first setting in the ODBC source. The human intervantion was that after moving the database I changed the ODBC source according to the new location.

  9. #9
    Star Lounger
    Join Date
    Jan 2001
    Location
    Duesseldorf, Germany
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel external data from access (Access 97/ Excel 97)

    I tried that, but the query was totally destroyed and you have to redo them all by hand.

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel external data from access (Access 97/ Excel 97)

    what a horrible bug, I do hope it's fixed in XP! <img src=/S/wartgun.gif border=0 alt=wartgun width=73 height=24>

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel external data from access (Access 97/ Excel 97)

    It is not a bug. That is, and has always been, normal behavior since the path is hard coded into the query. You can get around it somewhat in Office 2000 by using a Microsoft DataLink (udl) to hold the link to the data source. Then you just point at the datalink, which can be in the same folder as the spreadsheet, and let it find the data source.
    Charlotte

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel external data from access (Access 97/ Excel 97)

    ODBC is supposed to abstract data storages and their locations into simple, commonly accessible Datasources, referenced by their DSN in the local ODBC configuration. If you should move the datasource and change the ODBC entry accordingly, the client application of that DSN should not notice, or indeed, care that the underlying datasource has been moved. I'm still of the opinion that this it is a bug in MSQuery in that it reads the underlying path and remembers it. Or, if it is indeed by design, I deem this to be very bad design.

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel external data from access (Access 97/ Excel 97)

    It isn't really an MSQuery or Excel issue, it's an operating system issue.
    Charlotte

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel external data from access (Access 97/ Excel 97)

    why do you say it's an operating system issue? how is the OS causing a problem here?

  15. #15
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel external data from access (Access 97/ Excel 97)

    I recorded a macro whist using 'GET EXTERNAL DATA' to get data from an Access database. I now run the macro & just ignore the MS Query. Don't know if this is a viable workaround, but here is the code (I don't know how much of this is extraneous rubbish):

    With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
    "ODBC;DSN=MS Access Database;DBQ=P:INFOTECUK_CSSYS3MAR_ADDMarine Accrual.mdb;DefaultDir=P:INFOTECUK_CSSYS3MAR_ADD;D riverId=2" _
    ), Array("5;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;")), Destination:= _
    Range("A6"))
    .CommandText = Array( _
    "SELECT MarData.LOSS_ID, MarData.LOSSNAME, MarData.CAT_CODE, MarData.PAID_GBP, MarData.PAID_USD, MarData.PAID_CAD, MarData.OSLR_GBP, MarData.OSLR_USD, MarData.OSLR_CAD, MarData.PAID_cSTG, MarData.INC_c" _
    , _
    "STG" & Chr(13) & "" & Chr(10) & "FROM `P:INFOTECUK_CSSYS3MAR_ADDMarine Accrual`.MarData MarData" & Chr(13) & "" & Chr(10) & "ORDER BY MarData.LOSS_ID" _
    )
    .Name = "Import Marine FGU"
    .FieldNames = False
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = True
    .SaveData = True
    .AdjustColumnWidth = False
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False
    End With

Page 1 of 2 12 LastLast

Posting Permissions

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