Needing a little SQL help

[nickf123 via AccessMonster.com]

Hello,

I'm having some trouble with a Select query I wrote; I'm getting "Error 3027 -
Cannot update. Database or Object is read-only." I think it's a problem with
my SQL statement, which is executed from VBA. All of my other
queries/recordsets work, so it's not the database or permissions; the query
even executes correctly, I can view the datasheet and it contains all my
desired values, but changing them does not work. Here is the SQL:

SELECT [Tbl - Mod BOM].* FROM ([Tbl - Mod Kits Progress] INNER JOIN [Tbl -
Step Definitions] ON [Tbl - Mod Kits Progress].[ID Step] = [Tbl - Step
Definitions].[ID Step]) INNER JOIN [Tbl - Mod BOM] ON [Tbl - Mod Kits
Progress].Unicode = [Tbl - Mod BOM].Unicode WHERE ((([Tbl - Step Definitions].
Number)=2) AND (([Tbl - Mod Kits Progress].[To Be Modified])=True) AND ((
[Tbl - Step Definitions].[Category]) = [Tbl - Mod BOM].[Category]));

This SQL opens up a recordset; I can browse the recordset and see all my
values, but cannot edit or addnew. I know enough about SQL to stumble through
writing a query, but as far as updating rules, etc. go, I am clueless. Any
help would be greatly appreciated.

-Nick

 

[John Spencer]

You might be able to make this work by adding DISTINCTROW to the query.

SELECT DistinctRow [Tbl - Mod BOM].*
FROM ([Tbl - Mod Kits Progress] INNER JOIN
[Tbl - Step Definitions] ON [Tbl - Mod Kits Progress].[ID Step] =
[Tbl - Step Definitions].[ID Step]) INNER JOIN
[Tbl - Mod BOM] ON [Tbl - Mod Kits Progress].Unicode = [Tbl - Mod
BOM].Unicode
WHERE ((([Tbl - Step Definitions].Number)=2)
AND (([Tbl - Mod Kits Progress].[To Be Modified])=True)
AND (([Tbl - Step Definitions].[Category]) = [Tbl - Mod BOM].[Category]));

SELECT queries with multiple tables are often not updatable.

In Access Help type the following in the Answer Wizard tab
When can I update data from a query
Select that from the list for an explanation of some of the causes and
alternative solutions.

Access MVP Allen Browne has summarized the reasons:

Query results will be read-only if any of the following apply:
.. It has a GROUP BY clause (totals query).
.. It has a TRANSFORM clause (crosstab query).
.. It contains a DISTINCT predicate.
.. It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause
(performs aggregation).
.. It involves a UNION.
.. It has a subquery in the SELECT clause.
.. It uses JOINs of different directions on multiple tables in the FROM
clause.
.. The query's Recordset Type property is set to Snapshot.
.. The query is based on another query that is read-only (stacked query.)
.. Your permissions are read-only (Access security.)
.. The database is opened read-only, or the file attributes are read-only, or
the database is on read-only media (e.g. CD-ROM, network drive without write
privileges.)

Unfortunately, that list is not exhaustive of all the reasons.

Hello,

I'm having some trouble with a Select query I wrote; I'm getting "Error
3027 -
Cannot update. Database or Object is read-only." I think it's a problem
with
my SQL statement, which is executed from VBA. All of my other
queries/recordsets work, so it's not the database or permissions; the
query
even executes correctly, I can view the datasheet and it contains all my
desired values, but changing them does not work. Here is the SQL:

SELECT [Tbl - Mod BOM].* FROM ([Tbl - Mod Kits Progress] INNER JOIN [Tbl -
Step Definitions] ON [Tbl - Mod Kits Progress].[ID Step] = [Tbl - Step
Definitions].[ID Step]) INNER JOIN [Tbl - Mod BOM] ON [Tbl - Mod Kits
Progress].Unicode = [Tbl - Mod BOM].Unicode WHERE ((([Tbl - Step
Definitions].
Number)=2) AND (([Tbl - Mod Kits Progress].[To Be Modified])=True) AND ((
[Tbl - Step Definitions].[Category]) = [Tbl - Mod BOM].[Category]));

This SQL opens up a recordset; I can browse the recordset and see all my
values, but cannot edit or addnew. I know enough about SQL to stumble
through
writing a query, but as far as updating rules, etc. go, I am clueless.
Any
help would be greatly appreciated.

-Nick

 

[Nick via AccessMonster.com]

Odd - I had left this problem until today, but when I ran it executed with no
problems. I'm halfway wondering if someone had wandered into the back-end
database and had managed to lock through an edit or something like that.

Still, I appreciate the help; at least now I know what to do in the future.

Again, thanks.

-Nick