Recordset not Updateable

[Guest]

Hi!
I have 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. Thew 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. Thanks - Howard

 

[Guest]

I now included the SQL code for the query. I thought it might be helpful.
Thanks! - Howard

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

 

[Ken Snell [MVP]]

There can be myriad reasons for why a query is nonupdateable: not enough
"primary" key fields in the query's list, it's a "totals" query, etc.

See these links for more info (watch for word- and line-wrapping; all URLs
are one line):

"When can I update data from a query?"
http://msdn.microsoft.com/library/d...l/acconDeterminingWhenCanUpdateDataQueryS.asp


"Harnessing the Power of Updatable Queries"
http://msdn.microsoft.com/library/default.asp?url=/archive/en-us/dnaraccgen/html/msdn_harness.asp


--

Ken Snell
<MS ACCESS MVP>

I now included the SQL code for the query. I thought it might be helpful.
Thanks! - Howard

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

Hi!
I have 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. Thew
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. Thanks - Howard