Linked Excel Spreadsheet - cannot edit query!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ste - 07 Feb 2004 10:32 GMT

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

Reply to this Message

Ste - 07 Feb 2004 11:19 GMT

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

>-----Original Message-----
>hi wondered if anyone could help?
[quoted text clipped - 8 lines]
>Ste
>.

Reply to this Message

John Vinson - 07 Feb 2004 22:53 GMT

>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!

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Reply to this Message

Tim Ferguson - 07 Feb 2004 18:22 GMT

> 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

Reply to this Message