Results 1 to 5 of 5

Thread: Database Query

  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a spreadsheet that I want to connect to a database query. Under Data in Excel, I choose "Import External Data" and connect to the query in Access. My problem is that when I choose the area in the Excel spreadsheet, it can shrink or grow. Is there a way to accomodate this? I want the area in the spreadsheet to conform to the Access query. I want to overwrite the data in the spreadsheet and remove the excess rows that contain the old data. Help!

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Not sure what you mean.


    I have just tried this with both Excel 2007 and 2003 and the ranges dynamically change
    to suit the data when refreshed, regardless of whether the range grows or reduces.

    Can you give a bit more explanation.

    As another aside but related.

    Be aware that IF you are using Like Operator in your Access Query,
    it will not work in Excel 2002 and after unless you use SQL % and _
    instead of the Access standard * and ?

    Andrew

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    After inserting the query, click the properties button on the external data toolbar. On that screen you can tell Excel what to do with growing/shrinking data.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I do have overwrite existing cells with new data, clear unused cells but it overwrites the cells and leaves the extra cells there. I have to manually delete them. I am not sure what it does if the data grows.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    What do you mean i leaves the extra cells there?
    I'd experiment with the three available options.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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