Results 1 to 6 of 6

Update a linked spreadsheet from Access

This is a discussion on Update a linked spreadsheet from Access within the Microsoft Access forums, part of the Question Forums category; I used to update linked spreadsheets from a query in access with no problems in an earlier version of access ...

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Campbell-Ewald
    Posts
    53

    Angry Update a linked spreadsheet from Access

    I used to update linked spreadsheets from a query in access with no problems in an earlier version of access (97). Now (access 2003) I don't seem to be able to.

    Example-I link to tab "policies" in an excel file that contains policy counts by zip code. In an access database, I have a table zips with zipcodes and the county and state they are in. I want to add the county to the excel file. I set up a query with the linked excel tab and the access table and join them on the common zip field. Then try to do an update on [policies]![county] from [zips]![county] with [policies]![zip] joined to [zips]![zip]. Field types match etc. there's no obvious reason to me why this cant be done

    I get a "cant update" error. When I go to the linked table and try to edit, I can't. The spreadsheet is not write protected or otherwise in use. Is there something that changed in the linking process between access 97 and access 2003? I can import and do the update and then export, but what a lot of extra work--can I update the spreadsheet directly somehow?

    Tx
    Dale

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,052

    Default Re: Update a linked spreadsheet from Access

    Is there something that changed in the linking process between access 97 and access 2003?
    Yes, beginning with version 2002 or 2003, Access lost the ability to update linked Excel sheets. It has something to do with some copyright battle or something like that. I'll see if I can find a link to that information.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Campbell-Ewald
    Posts
    53

    Default Re: Update a linked spreadsheet from Access

    Just beautiful. So an import is required to update the data?

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,052

    Default Re: Update a linked spreadsheet from Access

    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,052

    Default Re: Update a linked spreadsheet from Access

    Just beautiful. So an import is required to update the data?
    I don't think you need to import anything into Access, you can link the Excel file to Access to get what you need. However, to update the Excel file in the end, I think you would need to export from Access at that point (and import that into Excel).

    Conceivably, it might also be possible to come up with an elegant VBA solution to update an existing Excel file from Access.

    Of course, the really simple solution would be to use an older version of Access (i.e. 2000) where the capability to update linked files still exist. But that may not be feasible or possible (unless you have an old copy available to you).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Location
    Campbell-Ewald
    Posts
    53

    Default Re: Update a linked spreadsheet from Access

    thanks for the info--theres no going back on this one. Its not a frequent enough problem to write code for, so i'll be importing and exporting.

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