Help making my query writable

[miracleman]

hello all,

Here is the situation, I have various tables that relates to Students. The Student table (genera info), StudentEOD table (student information that changes every semester of schoolyear) and a Guardians table (table for people related to the students). All of them are related through the StudentID field which is a long integer.

I'm trying to create a query to filter out the guardians of the students that belong to a certain class. I want all the fields from the table Guardians and only the Class from the StudentEOD table. The problem is that when I create the relationship, inmediatly the recorset becomes readonly. Any suggestions?

 

[jal]

hello all,

Here is the situation, I have various tables that relates to Students. The Student table (genera info), StudentEOD table (student information that changes every semester of schoolyear) and a Guardians table (table for people related to the students). All of them are related through the StudentID field which is a long integer.

I'm trying to create a query to filter out the guardians of the students that belong to a certain class. I want all the fields from the table Guardians and only the Class from the StudentEOD table. The problem is that when I create the relationship, inmediatly the recorset becomes readonly. Any suggestions?

A join produces a new table that is a combination of 2 or more tables. Most databases do not consider this new table to be updateable because it is not itself a real table in the database. This might explain the readOnly recordset (although I'm not sure because I've never used a recordset).

Although a join is not itself updateable, you CAN, however, use a join in an UPDATE query like this:


Update Table1
Inner Join Table2 on Table1.Id = Table2.ID
Set Table1.First = Table2.First,
Table1.Last = Table2.Last

If you need to change the values in the joined table, perhaps you can output the join into a temporary table and then open a recordset on that table. The temp table should be updateable.

The only other reason that might cause a readonly recordset, that I can think of, would be a lack of write-permissions on certain tables.

Again, I myself have never used a RS.

 

[boblarson]

Perhaps this will help:

http://allenbrowne.com/ser-61.html