Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking to an Excel Spreadsheet (2003)

    I am creating a link in Access to an Excel spreadsheet. I have done this many times before and was under the impression that when you link to an Excel spreadsheet in Access that the data in the Excel spreadsheet link (within Access) cannot be changed or manipulated. One can see the change when opening the Access link (table) if someone has made a change in the Excel spreadsheet, which is nice of course if you are micromanaging what is going on over there in Excel...you can see that change in your linked Excel Table in the Access main database window....but today when I did this in class all computers did what I expected them to do, except two. They actually clicked on a record in the Excel linked table within Access and deleted a record. I thought one could not do this. What's up? Are there settings somewhere in Access that I haven't noticed that allows this behavior to occur?
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking to an Excel Spreadsheet (2003)

    Up to September 2005, it was possible to edit data in a linked Excel sheet in Access. But Microsoft removed this capability in Access 2003 SP2 (I think they lost a law suit about the technology used to edit linked Excel sheets).

    I suspect that the computers on which students were able to delete records from a linked Excel table have never been updated to SP2.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking to an Excel Spreadsheet (2003)

    Thank you Hans
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  4. #4
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking to an Excel Spreadsheet (2003)

    Hans is there any work around or patch for doing the linking and updating?

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Linking to an Excel Spreadsheet (2003)

    I believe automation is the only way to go.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking to an Excel Spreadsheet (2003)

    It' still not possible to edit a linked Excel worksheet, so if you want to manipulate the Excel data from Access you can

    - Use Insert | Object on a form to create an unbound OLE control containing a linked Excel sheet. If you set Enabled to True and Locked to False, the user can double-click the control to edit the Excel sheet.
    - Place a hyperlink to the Excel workbook on a form.
    - Use Automation to control Excel from Access using VBA, as patt suggests.

Posting Permissions

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