cannot write to database query

[Asherman]

I have created a query using a table and another query but cannot write into
it. I dont get an error message at all just a bleep. I have tried doing the
query using the 3 tables that I need but the same thing happens. Each
query/table concerned has an ID field which is linked to each other in the
relationships area.

 

[John Spencer]

That means that you have created a query that is not updatable. Beyond that
we would need to know more about your table structure and query.

As a guess you may need to use a Main form and subform(s) to control the
data entry in this situation.

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.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Asherman]

Thanks John

What I failed to mention is that this database was fully functioning last
month but since then the linked tables have had to be re linked to the sql
database. A collegue did suggest that I compact and repair the database but
this did not work. However whilst trying to find a solution I did a
replicate it, which fixed the problem, but when I tried it on a similar
database it did not work. Someone else told me that it looked as if the
relationships needed refreshing as some of them may still be linked
incorrectly even thought they look OK. I dont quite understand that if
anyone has any suggestions?! In the end instead of linking the tables for
the other database, I imported the ones I needed but still have the same
problem.

 

[Duane Hookom]

I would suggest you provide more details as asked by John "Beyond that we
would need to know more about your table structure and query."

Are there primary key fields in the tables and does Access identify them?
What are the SQL views of significant queries?

 

[Asherman]

Hope this helps, as I am still very much a novice in this area.

The tables are ordinary tables that I have imported from a sql database that
I have read only access to. I can change the data within these tables. The
UserID in the dbo_UserDetails table is the primary key and is indexed. This
is linked by a relationship to the StudentID in the other table, but only
where the joined fields in both tables are equal. The StudentID is also a
primary key and indexed.


The select query is as shown below, I cannot change the information in the
query:

SELECT dbo_UserDetails.LastName, dbo_UserDetails.PrefFirstName,
[lastname]+", "+[preffirstname] AS [Full Name],
dbo_MIS_CORE_VIEW_StudentListSimplified.TutGrpName,
dbo_MIS_CORE_VIEW_StudentListSimplified.YearGrpID,
IIf([yeargrpid]=1,10,IIf([yeargrpid]=2,8,IIf([yeargrpid]=3,11,IIf([yeargrpid]=4,9,IIf([yeargrpid]=5,7,0))))) AS [Yr Group], dbo_UserDetails.DateOfExit, *
FROM dbo_MIS_CORE_VIEW_StudentListSimplified INNER JOIN dbo_UserDetails ON
dbo_MIS_CORE_VIEW_StudentListSimplified.StudentID = dbo_UserDetails.UserID
WHERE (((dbo_UserDetails.PrefFirstName)<>"Emarkbook") AND
((dbo_MIS_CORE_VIEW_StudentListSimplified.TutGrpName)<>"LEAVER") AND
((dbo_UserDetails.UserType)=1))
ORDER BY dbo_UserDetails.LastName, dbo_UserDetails.PrefFirstName;

Is there anything else you may need to understand my problem?

 

[Asherman]

In addition to the previous message with the sql details, I have exported the
two tables into a new database and create the query as per the original one -
everything works perfectly and I can amend it, however if I copy or export
the original query into the new database then the old problem re ocuurs.

This is getting confusing but I bet there is a simple explanation.

 

[Asherman]

OK now I am confused.

I created a new database and exported the two tables into it, then
exported/copied the query. The same problem arose. However when I did a new
query with the same parameters in the new database everything worked and I
could amend it. So I tried doing a new query in the original database with
the same parameters and I can amend that one.

HELP!

Hope this helps, as I am still very much a novice in this area.

The tables are ordinary tables that I have imported from a sql database that
I have read only access to. I can change the data within these tables. The
UserID in the dbo_UserDetails table is the primary key and is indexed. This
is linked by a relationship to the StudentID in the other table, but only
where the joined fields in both tables are equal. The StudentID is also a
primary key and indexed.


The select query is as shown below, I cannot change the information in the
query:

SELECT dbo_UserDetails.LastName, dbo_UserDetails.PrefFirstName,
[lastname]+", "+[preffirstname] AS [Full Name],
dbo_MIS_CORE_VIEW_StudentListSimplified.TutGrpName,
dbo_MIS_CORE_VIEW_StudentListSimplified.YearGrpID,
IIf([yeargrpid]=1,10,IIf([yeargrpid]=2,8,IIf([yeargrpid]=3,11,IIf([yeargrpid]=4,9,IIf([yeargrpid]=5,7,0))))) AS [Yr Group], dbo_UserDetails.DateOfExit, *
FROM dbo_MIS_CORE_VIEW_StudentListSimplified INNER JOIN dbo_UserDetails ON
dbo_MIS_CORE_VIEW_StudentListSimplified.StudentID = dbo_UserDetails.UserID
WHERE (((dbo_UserDetails.PrefFirstName)<>"Emarkbook") AND
((dbo_MIS_CORE_VIEW_StudentListSimplified.TutGrpName)<>"LEAVER") AND
((dbo_UserDetails.UserType)=1))
ORDER BY dbo_UserDetails.LastName, dbo_UserDetails.PrefFirstName;

Is there anything else you may need to understand my problem?

I would suggest you provide more details as asked by John "Beyond that we
would need to know more about your table structure and query."

Are there primary key fields in the tables and does Access identify them?
What are the SQL views of significant queries?

 

[John Spencer]

One possibility

Check the properties of the OLD query.
-- Recordset Type should not be Snapshot (not updatable)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Asherman]

Thanks John

I cannot see anything about the recordset in the query properties, it just
has the general tab showing the name, description and if it is hidden etc.
Should I be looking somewhere else?

 

[John Spencer]

Yes, you need to look elsewhere.

Open up the query in design view
Double-click in the upper area in the blank space (not on a table).
The properties window should now show
-- Description
-- Default View
-- Output all fields
-- a whole bunch more properties for the query

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Asherman]

You learn something new every day.

I have checked the properties and the record set is a dynaset.

 

[John Spencer]

Well, at this point all I can say is

Score
Computer 1
Human(s) 0

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Asherman]

Thanks John for the first laugh of the day.

At least by re writing the query the thing works now. I think I will try to
find another way to record this info for next Sept, I dont think I can go
through this again.

many thanks