Non Updateable Recordset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi!
I have a form bound to a query which uses three tables; "business",
"Contact", and
"Industry". The business table has a business ID field. The Contact table
has a Business ID field as a relationship to the business table. The business
table has an industry code field which joins to an industry code field in the
industry table. The query has fields in all three files to be able display
the business data, the contact information, and the industry information. If
I run the query, I cannot change any data as it informs me that the recordset
is not updateable. Of course the form I have bound to this query is also not
updateable. I've done much stuff like this before. Nothing fancy. What could
be the problem. I now included the SQL code for the query. I thought it might
be helpful.


SELECT Businesses.[Postal Code], Businesses.[Business Name],
Businesses.[Business Activity], NAICIndustries_New.NAICINDUSTRY,
NAICIndustries_New.NAICCODE, Businesses.EmployeeRange, Businesses.[Business
ID], Businesses.[Mailing Unit], Businesses.[Street Number],
Businesses.[Street Name], Businesses.[Street Type], Businesses.[Street
Direction], Businesses.[Address Line 1], Businesses.[Address Line 2],
Left$([Address Line 2],13) AS City1, Left$([Address Line 2],10) AS City2,
Left$([Address Line 2],9) AS City3, Left$([Address Line 2],7) AS City4,
Left$([Address Line 2],5) AS City5, Businesses.Community,
Businesses.Municipality, Businesses.Province, Businesses.[Mailing Address],
Businesses.[Mailing Postal Code], Businesses.[Last Update], Contacts.Initial,
Contacts.[Last Name], Contacts.Phone, Contacts.Ext,
NAICIndustries_New.INDUSTRYCAPTION
FROM (Businesses LEFT JOIN Contacts ON Businesses.[Business ID] =
Contacts.[Business ID]) INNER JOIN NAICIndustries_New ON Businesses.[Primary
NAIC1] = NAICIndustries_New.NAICINDUSTRY
WHERE (((Businesses.EmployeeRange)="10-19" Or
(Businesses.EmployeeRange)="20-49" Or (Businesses.EmployeeRange)="50-99") AND
((Left$([Address Line 2],13))="Richmond Hill")) OR
(((Businesses.EmployeeRange)="10-19" Or (Businesses.EmployeeRange)="20-49" Or
(Businesses.EmployeeRange)="50-99") AND ((Left$([Address Line
2],10))="Woodbridge")) OR (((Businesses.EmployeeRange)="10-19" Or
(Businesses.EmployeeRange)="20-49" Or (Businesses.EmployeeRange)="50-99") AND
((Left$([Address Line 2],9))="Thornhill")) OR
(((Businesses.EmployeeRange)="10-19" Or (Businesses.EmployeeRange)="20-49" Or
(Businesses.EmployeeRange)="50-99") AND ((Left$([Address Line
2],7))="Concord")) OR (((Businesses.EmployeeRange)="10-19" Or
(Businesses.EmployeeRange)="20-49" Or (Businesses.EmployeeRange)="50-99") AND
((Left$([Address Line 2],5))="Maple"))
ORDER BY Businesses.[Postal Code];
 
Basically, the query is non-updateable if there are many-to-many or
many-to-one relationships represented in the output. Access (actually SQL
in general) can't do updates in that case because it means that it would be
trying to update multiple rows in one (or more) of the underlying tables for
each of the individual rows that you are trying to update in your form.

There is (or used to be) a writeup in the Access help (do a search on
queries) and also in the Microsoft knowledgebase (I don't have the link
off-hand, you'll have to do a search). You might also want to do a general
web search on something like

+updateable +query + access

and see if anyone has a simpler writeup. The Microsoft writeup is a bit
complicated and you'll need an aspirin or two to work through it.



Howard said:
Hi!
I have a form bound to a query which uses three tables; "business",
"Contact", and
"Industry". The business table has a business ID field. The Contact table
has a Business ID field as a relationship to the business table. The business
table has an industry code field which joins to an industry code field in the
industry table. The query has fields in all three files to be able display
the business data, the contact information, and the industry information. If
I run the query, I cannot change any data as it informs me that the recordset
is not updateable. Of course the form I have bound to this query is also not
updateable. I've done much stuff like this before. Nothing fancy. What could
be the problem. I now included the SQL code for the query. I thought it might
be helpful.


SELECT Businesses.[Postal Code], Businesses.[Business Name],
Businesses.[Business Activity], NAICIndustries_New.NAICINDUSTRY,
NAICIndustries_New.NAICCODE, Businesses.EmployeeRange, Businesses.[Business
ID], Businesses.[Mailing Unit], Businesses.[Street Number],
Businesses.[Street Name], Businesses.[Street Type], Businesses.[Street
Direction], Businesses.[Address Line 1], Businesses.[Address Line 2],
Left$([Address Line 2],13) AS City1, Left$([Address Line 2],10) AS City2,
Left$([Address Line 2],9) AS City3, Left$([Address Line 2],7) AS City4,
Left$([Address Line 2],5) AS City5, Businesses.Community,
Businesses.Municipality, Businesses.Province, Businesses.[Mailing Address],
Businesses.[Mailing Postal Code], Businesses.[Last Update], Contacts.Initial,
Contacts.[Last Name], Contacts.Phone, Contacts.Ext,
NAICIndustries_New.INDUSTRYCAPTION
FROM (Businesses LEFT JOIN Contacts ON Businesses.[Business ID] =
Contacts.[Business ID]) INNER JOIN NAICIndustries_New ON Businesses.[Primary
NAIC1] = NAICIndustries_New.NAICINDUSTRY
WHERE (((Businesses.EmployeeRange)="10-19" Or
(Businesses.EmployeeRange)="20-49" Or (Businesses.EmployeeRange)="50-99") AND
((Left$([Address Line 2],13))="Richmond Hill")) OR
(((Businesses.EmployeeRange)="10-19" Or (Businesses.EmployeeRange)="20-49" Or
(Businesses.EmployeeRange)="50-99") AND ((Left$([Address Line
2],10))="Woodbridge")) OR (((Businesses.EmployeeRange)="10-19" Or
(Businesses.EmployeeRange)="20-49" Or (Businesses.EmployeeRange)="50-99") AND
((Left$([Address Line 2],9))="Thornhill")) OR
(((Businesses.EmployeeRange)="10-19" Or (Businesses.EmployeeRange)="20-49" Or
(Businesses.EmployeeRange)="50-99") AND ((Left$([Address Line
2],7))="Concord")) OR (((Businesses.EmployeeRange)="10-19" Or
(Businesses.EmployeeRange)="20-49" Or (Businesses.EmployeeRange)="50-99") AND
((Left$([Address Line 2],5))="Maple"))
ORDER BY Businesses.[Postal Code];
Click to expand...
 
Sorry, I just noticed that your SQL includes some functions that modify the
data that's in one or more of the tables (e.g., the Left$'s)

That too would make the resulting recordset non-updateable since there's no
way for Access (or SQL) to accept changes to a field that is the result of a
function.

To be updateable, all output from the query(ies) must be the original fields
without modification, calculation, concatenation, etc.


Howard said:
Hi!
I have a form bound to a query which uses three tables; "business",
"Contact", and
"Industry". The business table has a business ID field. The Contact table
has a Business ID field as a relationship to the business table. The business
table has an industry code field which joins to an industry code field in the
industry table. The query has fields in all three files to be able display
the business data, the contact information, and the industry information. If
I run the query, I cannot change any data as it informs me that the recordset
is not updateable. Of course the form I have bound to this query is also not
updateable. I've done much stuff like this before. Nothing fancy. What could
be the problem. I now included the SQL code for the query. I thought it might
be helpful.


SELECT Businesses.[Postal Code], Businesses.[Business Name],
Businesses.[Business Activity], NAICIndustries_New.NAICINDUSTRY,
NAICIndustries_New.NAICCODE, Businesses.EmployeeRange, Businesses.[Business
ID], Businesses.[Mailing Unit], Businesses.[Street Number],
Businesses.[Street Name], Businesses.[Street Type], Businesses.[Street
Direction], Businesses.[Address Line 1], Businesses.[Address Line 2],
Left$([Address Line 2],13) AS City1, Left$([Address Line 2],10) AS City2,
Left$([Address Line 2],9) AS City3, Left$([Address Line 2],7) AS City4,
Left$([Address Line 2],5) AS City5, Businesses.Community,
Businesses.Municipality, Businesses.Province, Businesses.[Mailing Address],
Businesses.[Mailing Postal Code], Businesses.[Last Update], Contacts.Initial,
Contacts.[Last Name], Contacts.Phone, Contacts.Ext,
NAICIndustries_New.INDUSTRYCAPTION
FROM (Businesses LEFT JOIN Contacts ON Businesses.[Business ID] =
Contacts.[Business ID]) INNER JOIN NAICIndustries_New ON Businesses.[Primary
NAIC1] = NAICIndustries_New.NAICINDUSTRY
WHERE (((Businesses.EmployeeRange)="10-19" Or
(Businesses.EmployeeRange)="20-49" Or (Businesses.EmployeeRange)="50-99") AND
((Left$([Address Line 2],13))="Richmond Hill")) OR
(((Businesses.EmployeeRange)="10-19" Or (Businesses.EmployeeRange)="20-49" Or
(Businesses.EmployeeRange)="50-99") AND ((Left$([Address Line
2],10))="Woodbridge")) OR (((Businesses.EmployeeRange)="10-19" Or
(Businesses.EmployeeRange)="20-49" Or (Businesses.EmployeeRange)="50-99") AND
((Left$([Address Line 2],9))="Thornhill")) OR
(((Businesses.EmployeeRange)="10-19" Or (Businesses.EmployeeRange)="20-49" Or
(Businesses.EmployeeRange)="50-99") AND ((Left$([Address Line
2],7))="Concord")) OR (((Businesses.EmployeeRange)="10-19" Or
(Businesses.EmployeeRange)="20-49" Or (Businesses.EmployeeRange)="50-99") AND
((Left$([Address Line 2],5))="Maple"))
ORDER BY Businesses.[Postal Code];
Click to expand...
 
see reply in other newsgroup where you posted this question

--

Ken Snell
<MS ACCESS MVP>

Howard said:
Hi!
I have a form bound to a query which uses three tables; "business",
"Contact", and
"Industry". The business table has a business ID field. The Contact table
has a Business ID field as a relationship to the business table. The
business
table has an industry code field which joins to an industry code field in
the
industry table. The query has fields in all three files to be able display
the business data, the contact information, and the industry information.
If
I run the query, I cannot change any data as it informs me that the
recordset
is not updateable. Of course the form I have bound to this query is also
not
updateable. I've done much stuff like this before. Nothing fancy. What
could
be the problem. I now included the SQL code for the query. I thought it
might
be helpful.


SELECT Businesses.[Postal Code], Businesses.[Business Name],
Businesses.[Business Activity], NAICIndustries_New.NAICINDUSTRY,
NAICIndustries_New.NAICCODE, Businesses.EmployeeRange,
Businesses.[Business
ID], Businesses.[Mailing Unit], Businesses.[Street Number],
Businesses.[Street Name], Businesses.[Street Type], Businesses.[Street
Direction], Businesses.[Address Line 1], Businesses.[Address Line 2],
Left$([Address Line 2],13) AS City1, Left$([Address Line 2],10) AS City2,
Left$([Address Line 2],9) AS City3, Left$([Address Line 2],7) AS City4,
Left$([Address Line 2],5) AS City5, Businesses.Community,
Businesses.Municipality, Businesses.Province, Businesses.[Mailing
Address],
Businesses.[Mailing Postal Code], Businesses.[Last Update],
Contacts.Initial,
Contacts.[Last Name], Contacts.Phone, Contacts.Ext,
NAICIndustries_New.INDUSTRYCAPTION
FROM (Businesses LEFT JOIN Contacts ON Businesses.[Business ID] =
Contacts.[Business ID]) INNER JOIN NAICIndustries_New ON
Businesses.[Primary
NAIC1] = NAICIndustries_New.NAICINDUSTRY
WHERE (((Businesses.EmployeeRange)="10-19" Or
(Businesses.EmployeeRange)="20-49" Or (Businesses.EmployeeRange)="50-99")
AND
((Left$([Address Line 2],13))="Richmond Hill")) OR
(((Businesses.EmployeeRange)="10-19" Or (Businesses.EmployeeRange)="20-49"
Or
(Businesses.EmployeeRange)="50-99") AND ((Left$([Address Line
2],10))="Woodbridge")) OR (((Businesses.EmployeeRange)="10-19" Or
(Businesses.EmployeeRange)="20-49" Or (Businesses.EmployeeRange)="50-99")
AND
((Left$([Address Line 2],9))="Thornhill")) OR
(((Businesses.EmployeeRange)="10-19" Or (Businesses.EmployeeRange)="20-49"
Or
(Businesses.EmployeeRange)="50-99") AND ((Left$([Address Line
2],7))="Concord")) OR (((Businesses.EmployeeRange)="10-19" Or
(Businesses.EmployeeRange)="20-49" Or (Businesses.EmployeeRange)="50-99")
AND
((Left$([Address Line 2],5))="Maple"))
ORDER BY Businesses.[Postal Code];
Click to expand...
 
Back
Top