If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Non Updateable Recordset
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]; -- Thanks! -Howard |
#2
|
|||
|
|||
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" wrote in message ... 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]; -- Thanks! -Howard |
#3
|
|||
|
|||
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" wrote in message ... 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]; -- Thanks! -Howard |
#4
|
|||
|
|||
see reply in other newsgroup where you posted this question
-- Ken Snell MS ACCESS MVP "Howard" wrote in message ... 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]; -- Thanks! -Howard |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
ASP recordset as SQL table | Ennex | Database Design | 8 | March 5th, 2005 06:38 PM |
Porting Access DB to ASP; use recordset as SQL table? | Ennex | Running & Setting Up Queries | 1 | March 4th, 2005 12:27 AM |
How to loop through a recordset and update the field of a recordset or delete current record | Karen Middleton | Running & Setting Up Queries | 1 | January 4th, 2005 10:30 AM |
Recordset is not updateable | jk | Running & Setting Up Queries | 1 | October 8th, 2004 12:11 PM |
Recordset not updateable | New Users | 2 | September 9th, 2004 12:50 AM |