read only problem RESOLVED

[peter piper]

I have an Access db with 4 tables in it which I'm accessing through vb (customers, jobs, quotes and invoices).
I can access the customer table ok.
The db has a query which links 3 tables together but when I try to add a new record to it using

datJobs.recordset.addnew I get an error message

"Run-time error 3027

Cannot update database or object is read only."

The db isn't read only as I can access the customers table and make changes.
The read only property on the data object is also set to false.

Any help would be appreciated

[Pasvorto]

Sometimes I have inadvertantly opened my recordsets as Read-Only. You might check that sort of theing.

[mendhak]

Post the code you're using to open the DB and save to it.

What is datJobs?

[peter piper]

datJobs is the name of the data object
the code used is

Private Sub cmdNew_Click()
datJobs.Recordset.AddNew
End Sub

[visualAd]

If your query joins more than two tables and you have referrential integrity enforced on your relationships, access will not let you modify the recordset returned.

[peter piper]

Thanks.
Is there no way round this

[visualAd]

The reason your recordset is readonly is becuase it is made from data from lots of different tables.

If you change a row in your recordset then the table that the data comes from needs to be updated.

Take for example a students table, a courses table and an enrolments table.

+----------------+               +------------------+                 +---------------+
|    Students    |               |     Enrolments   |                 |    Courses    |
+----------------+               +------------------+                 +---------------+
| StudentId (PK) |--|-----------<| StudentId        |                 | CourseName    |
| StudentName    |               | CourseId         |>-------------|--| CourseId (PK) |
+----------------+               | EnrolmentId (PK) |                 +---------------+
                                 | EnrolmentDate    |
                                 +------------------+

You use a query to give the following recordset:

| StudentName | CourseName | EnrolmentDate |
|             |            |               |

If you attempt to change the student name or course name which reside in the student and courses table. How will access know whether the change you are making is to add a new student (i.e. a student that does not exist) or changing it to the name of a student who already exists. Unless the StudentName is the primary key in the students table (not advisable), Access has no way of knowing.

The best way of doing this is to open up the actual table you want to modify as a recordset. Don't use a query - you can then add records. You may need to open up several recordsets if you want to modify several tables.

For example - adding a student and enroling on a course would require:

1. Open the students table recordset
2. Insert a new student record
3. Store the new students StudentId
4. Close the student recordset.
5. Open the courses recordset
6. Search for the course you want (i.e. using the CourseName)
7. Store the CourseID
8. Close the courses recordset.
9. Open the enrolemtns recordset.
10. Insert the new enrolment using the CourseId and the StudentID remembered earlier.

As a rule of thumb. Select queries are for pulling data previously stored, not inserting - they should always be treated as read-only.

[peter piper]

Thanks I understand now, I appreciate you're explanation