Cannot insert new record into sql 2005 View from Access form
in [General]
|
From: NancyM on 19 Jan 2010 13:16 I have a sql server 2005 database containing several tables: Orders, Shipping, and Planning. They are joined together in the sql view V_Main. I also have a ms access 2007 mdb that links to V_Main via odbc. I created an access form using the view V_Main as the record source. I cannot insert a new record into each of the 3 base tables using V_Main as the record source. A new record is inserted into only one table. I did find a suggestion in another forum that suggested copying the sql that creates the view into the form's record source, rather than the name of the view. This does work, but adds another place to be updated manually should and other table be added to the view in the future. I did verify that all tables have primary keys. How can I add a new record for each of the 3 base tables using the view as the record source? What is the code that could be placed in vb of the form? Any suggestions would be helpful. Thank you.
From: Arvin Meyer [MVP] on 19 Jan 2010 13:24 I view or query generally will update 1 table. I suggest that you create 3 separate append queries, or triggers and use a form event to run them and update the 3 tables. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "NancyM" <NancyM(a)discussions.microsoft.com> wrote in message news:97D1161A-347D-40D0-AD9E-5719F0D89C17(a)microsoft.com... >I have a sql server 2005 database containing several tables: Orders, > Shipping, and Planning. They are joined together in the sql view V_Main. > I > also have a ms access 2007 mdb that links to V_Main via odbc. I created > an > access form using the view V_Main as the record source. I cannot insert a > new record into each of the 3 base tables using V_Main as the record > source. > A new record is inserted into only one table. > I did find a suggestion in another forum that suggested copying the sql > that > creates the view into the form's record source, rather than the name of > the > view. This does work, but adds another place to be updated manually > should > and other table be added to the view in the future. > I did verify that all tables have primary keys. > How can I add a new record for each of the 3 base tables using the view as > the record source? What is the code that could be placed in vb of the > form? > Any suggestions would be helpful. > Thank you.
|
Pages: 1 Prev: How to stop outlook resending attachments multiple times Next: OLE server and .PDF files |