Non Updateable Recordset

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];
 
J

JP

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

JP

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

Ken Snell [MVP]

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Split data in field on character return 0
Pass a variable from one form to another 6
Return String from Boolean Field 2
Calling Query in Report 3
Recordset not Updateable 2
Address Envelope with Trim 1
VBA Multiple Line handling with Split() 1
strip "similar" records from table 1

Top