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
|
|||
|
|||
Recordset not Updateable
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 -- Thanks! -Howard |
#2
|
|||
|
|||
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]; "Howard" wrote: 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 -- Thanks! -Howard |
#3
|
|||
|
|||
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/de...DataQueryS.asp "Harnessing the Power of Updatable Queries" http://msdn.microsoft.com/library/de...dn_harness.asp -- Ken Snell MS ACCESS MVP "Howard" wrote in message ... 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]; "Howard" wrote: 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 -- Thanks! -Howard |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Non Updateable Recordset | Howard | Using Forms | 3 | March 7th, 2005 06:03 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 |