none
Access Insert Error in Append Query

    Question

  •  

    I have a problem similar to the thread at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2433541&SiteID=1

    but with a different error.

     

    this is the query

     

    Code Block
    INSERT INTO PLC ( VariableIndicator, Address, Symbol, VariableType, Desc1, Desc2, Desc3, Desc4, Desc5, LC, SC1, SC2, SC3, SC4 )
    SELECT "N" AS VariableIndicator, CitectAlarmCoils.Address, CitectAlarmCoils.Symbol, "BOOL" AS VariableType, CitectAlarmCoils.Desc1, CitectAlarmCoils.Desc2, CitectAlarmCoils.Desc3, CitectAlarmCoils.Desc4, CitectAlarmCoils.Desc5, CitectAlarmCoils.LC, CitectAlarmCoils.SC1, CitectAlarmCoils.SC2, CitectAlarmCoils.SC3, CitectAlarmCoils.SC4
    FROM CitectAlarmCoils
    WHERE (((CitectAlarmCoils.Symbol) Is Not Null));

     

     

    the error is

    The INSERT INTO statement contains the following unknown field "VariableIndicator". make sure you have typed the name correctly, and try the operation again.

     

    The SELECT statement preceding the INSERT INTO works and brings creates a table with the specified fields VariableIndicator, Address, Symbol.. etc..

     

    I think the problem is that the VariableIndicator and VariableType are not included in the table CitectAlarmCoils.

     

    Is there a way around this, or will I need to create a table which includes VariableIndicator and VariableType? I am trying to find out more about how the INSERT INTO statement work, but there is only a short description in the documentation.

    Wednesday, November 28, 2007 1:42 AM

Answers

  •  

    Hi,

    The error message means that table PLC doesn’t contain a column named VariableIndicator. Please check.

    Thursday, November 29, 2007 7:28 AM

All replies

  • Please post your schema for the PLC table. Do you have the fields that it's complaining about not being there?

    Wednesday, November 28, 2007 4:08 AM
  •  

    Hi,

    The error message means that table PLC doesn’t contain a column named VariableIndicator. Please check.

    Thursday, November 29, 2007 7:28 AM
  • yes, please check your spelling.

    Thursday, November 29, 2007 9:46 AM
  • Well when i first saw your code then i assume that you are doing bulk insert.
    If is this right then there is error in syntax.

    Just remove into then try...

    ++++++++++++++++++
    INSERT PLC ( VariableIndicator, Address, Symbol, VariableType, Desc1, Desc2, Desc3, Desc4, Desc5, LC, SC1, SC2, SC3, SC4 )
    SELECT "N" AS VariableIndicator, CitectAlarmCoils.Address, CitectAlarmCoils.Symbol, "BOOL" AS VariableType, CitectAlarmCoils.Desc1, CitectAlarmCoils.Desc2, CitectAlarmCoils.Desc3, CitectAlarmCoils.Desc4, CitectAlarmCoils.Desc5, CitectAlarmCoils.LC, CitectAlarmCoils.SC1, CitectAlarmCoils.SC2, CitectAlarmCoils.SC3, CitectAlarmCoils.SC4
    FROM CitectAlarmCoils
    WHERE (((CitectAlarmCoils.Symbol) Is Not Null));

    ++++++++++++++++++

    I hope this will help you.
    Thursday, November 29, 2007 10:43 AM
  • thanks for the replies

    it was the table i was inserting into

    it did not have the VariableIndicator and VariableType fields

    i had thought that the table was dynamic and created upon the Insert command

     

    but Insert Into appends to the table and hence the table had to first exist

    once I modified the table, the query was fine

    Friday, November 30, 2007 1:12 AM