Access 2000 with Excel 2003 linked

R

Reggie Laffond

I have an Access2000 App that links in an Excel 2003 worksheet. When I
attempt to update the linked table I get the message "This table is not
updateable". I have encountered this before with a local table where a query
had a one to many relationship and there was no primary key defined. But in
this application I am simply tying to update the linked table.

In another post I found while searching for an answer to my problem the
poster had a table linked to Excel and wrote "The Excel data contains a
unique identifier (Key field) call Serial Numbers (SN)". How do you create a
(Primary) key in a linked table. Most properties in a linked table can not
be edited.

This application has been allowing updates to this linked table for 6
months but suddenly is issuing this new message.

Any one know why and how to resolve?

Thanks in advance.
 
R

Reggie Laffond

I just learned that this problem surfaced after installing Office SP2. So
evidently SP2 plugged some security hole and at the same time took away
existing functionality. SP2 can NOT be uninstalled.

If anyone knows how I should proceed please offer any suggestions.
 
R

Reggie Laffond

I finally found on the Microsoft website that this functionality is by
design. After SP2 you can no longer edit a linked Excel file. You must
import the file, edit it and then export.

I can't believe this ability was eliminated. Microsoft should rethink their
position and restore this feature.
 
R

Reggie Laffond

I read that it was for "legal issues" but can't imagine what legally could
would force this fuctionality to be removed when both products are developed
by Microsoft. If there was an issue updating tables linked to Oracle or
dBase I could understand.
 
J

John Nurick

Hi Reggie,

This appears to have been done "because of legal issues" rather than
security ones. It's described but not explained at
http://support.microsoft.com/?id=904953. This article offers a couple of
manual workrounds, either manipulating the data in Excel or importing it
into Access, manipulating there, and then exporting back to Excel.

I haven't installed SP2 myself yet, but as far as I know it's still
possible to write VBA code to automate either of these workrounds.
 
G

Garry Robinson

Years ago I wrote an article on using Excel as backend database. Rarely
have I used the technology since but anyway when this upgrade popped up
in my Windows Upgrade an hour or so ago, I was very curious. So I
tested the download with the article at the following page before and
after the upgrade and guess what, the links went from read/write to
read-only.

http://www.vb123.com/toolshed/98docs/excelbe.htm

My big concern on this issue is that quite a lot of applications are
simply going to fail for no reason. Microsoft should release a tools so
that we can search access databases for the use of excel linked tables.


I am also concerned that other styles of linked tables are going to be
turned off for legal reasons. What about an explanation Microsoft.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access: linked tables 1
Establish relationship beteen SQL query results and Access table 2
How to link single excel spreadsheet to multiple access tables 1
Load data from a flat file to a linked file 1
Linking Access 2003 table to Excel 2003 spreadsheet 1
Re-link an existing xls linked table to a different excel file 4
Conneting to SQL server, Need advice 3
Link From Access To Excel 3

Top