Update a linked spreadsheet from Access

[dcoons]

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

 

[Joe4]

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.

 

[dcoons]

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

 

[Joe4]

Here's the link:
http://support.microsoft.com/kb/904953/en-us

 

[Joe4]

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).

 

[dcoons]

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.