Linked Excel Spreadsheet - cannot edit query!

[Ste]

hi wondered if anyone could help?

I have an Access database in which I use 4 linked tables
(which are linked to Excel spreadsheets). I have a query
pointing to two of the spreadsheets and a normal table.
The problem I have is that I cannot edit or add to the
data stored in the access table. Does anyone know the
problem?

Ste

 

[Ste]

I've tried removing the linked tables and importing them
instead, but I have the same problem. So it seems not to
be a problem with linked tables but the query itself.

Ste

 

[Tim Ferguson]

I have an Access database in which I use 4 linked tables
(which are linked to Excel spreadsheets). I have a query
pointing to two of the spreadsheets and a normal table.
The problem I have is that I cannot edit or add to the
data stored in the access table. Does anyone know the
problem?

Whether or not recordsets are updateable is a wondrous piece of black magic
that happens deep in the bowels of the Jet Engine, particularly with
complex queries. It is not uncommon for fields from one or more tables to
be editable and others not. With really large queries, it is sometimes just
not possible to make them entirely updateable anyway. Later versions of Jet
do better than earlier ones.

You can sometimes track things down by checking very carefully which PK
fields are present. You must have the PK of a record in order to be able to
edit its fields; on the other hand you need the FK in order to be able to
change the record used. An example:

Students EnrolledOn
======== ==========
*StudentID >------- *StudentID
FullName *CourseID
HomeAddress FeePaid
etc etc

Now, if you include both EnrolledOn.StudentID and EnrolledOn.CourseID, then
you can change which student is enrolled on the course. On the other hand,
you can't change anything about the student (i.e. the name or address)
unless you have Students.StudentID in the query.

Hope that helps a bit


Tim F

 

[John Vinson]

I've tried removing the linked tables and importing them
instead, but I have the same problem. So it seems not to
be a problem with linked tables but the query itself.

Ste

Please post the SQL of the query. A four-table query will be very
unlikely to be updateable; a two-table query will be updateable ONLY
if you have a Primary Key or other unique index on the linking field
on the "one" side of the query, and an index on the linking field on
the "many" side - and Excel spreadsheets don't have indexes!