Using the Insert into SQL in MS Access database

Question

I'm trying to copy a number of columns from one table to another table (column names are the same in both tables) when a (tick box) column is checked in my MS Access database. I know that I can use the Insert Into SQL query but it doesn't seem to be working.

When I try to run it I just get

Syntax error in INSERT INTO statement

This is what I have written:

INSERT INTO Fauna_ATT (Burn ID, Burn Name, Value Effected, Pre Burn Action Details, During Burn Action Details, Post Burn Action Details) 
    SELECT Burn ID, Burn Name, Value Effected, Pre Burn Action Details, During Burn Action Details, Post Burn Action Details 
    FROM EPFP 
    WHERE ATT="TRUE";

Fauna_ATT is my table I want the columns to be placed

Burn ID, Burn Name, Value Effected, Pre Burn Actions, During Burn Actions and Post Burn Actions are my columns I want to copy

EPFP is my table I want the columns to come from and I only want the columns to be copied over when my ATT (check box column) is ticked.

Ed Bangga · Answer 1 · 2019-10-09T01:31:32.277

0

enclosed your column names to "[]"

INSERT INTO Fauna_ATT ([Burn ID], [Burn Name], [Value Effected], [Pre Burn Action Details], [During Burn Action Details], [Post Burn Action Details]) 
SELECT [Burn ID], [Burn Name], [Value Effected], [Pre Burn Action Details], [During Burn Action Details], [Post Burn Action Details]
FROM EPFP 
WHERE ATT=1;

details on when to enclose column names to brackets can be found here

edited Oct 09 '19 at 01:31

answered Jul 22 '19 at 00:43

Ed Bangga

12,879
4
16
30

Thanks. It is now coming up with "Data type mismatch in criteria expression" – Madison Read Jul 22 '19 at 00:58
well, you need to check your table columns data types. nothing I can help, unless you'll share your create table script – Ed Bangga Jul 22 '19 at 00:59
1

A checkbox value is a boolean, not a string. WHERE clause should be **WHERE ATT = True** or **WHERE ATT = -1** – Rene Jul 22 '19 at 04:28
@MadisonRead, rene is right, ATT should be boolean not string, i updated the answer – Ed Bangga Oct 09 '19 at 01:32

score 0 · Answer 2 · answered Oct 09 '19 at 02:21

    OleDbConnection conn;
    OleDbCommand cmd;
    OleDbDataReader dr;


   private void connectDb()
   {
            conn = new OleDbConnection(@"Your Connection String");
            conn.Open();//initialization of your connection
   }

   private void performCommandOne()
   {

         String command1 = "INSERT INTO Fauna_ATT values(@Burn_ID, @Burn_Name, 
         @Value_Effected, 
         @Pre_Burn_Action_Details, @During_Burn_Action_Details, 
         @Post_Burn_Action_Details)";

          /*insert statement and the name of your coloumns in your 
          database must be no space */

        /*this code dislays like this.
        if you perform the insert processs..*/
        /*connection of your database must be initialize first*/
        this.connectDb();
        cmd = new OleDbCommand(command1,conn);
        cmd.Parameters.AddWithValue("@Burn_ID", "Fields for Burn_ID");
        cmd.Parameters.AddWithValue("@Burn_Name", "Fields for Burn_Name");
        cmd.Parameters.AddWithValue("@Value_Effected", "Fields for Value_Effected");
        cmd.Parameters.AddWithValue("@Pre_Burn_Action_Details", "Fields for Pre_Burn_Action_Details");
        cmd.Parameters.AddWithValue("@During_Burn_Action_Details", "Fields for During_Burn_Action_Details");
        cmd.Parameters.AddWithValue("@Post_Burn_Action_Details", "Post_Burn_Action_Details");
        cmd.ExecutedNonQuery();
        conn.Close();

   }


   private void performCommandTwo()
   {

            String commandTwo = "SELECT Burn_ID, Burn_Name, Value_Effected, 
            Pre_Burn_Action_Details, 
            During_Burn_Action_Details, Post_Burn_Action_Details 
            FROM EPFP 
            WHERE ATT='TRUE'";

            this.connectDb();
            cmd = new OleDbCommand(commandTwo,conn);
            cmd.Parameters.AddWithValue("@ATT", "fOR aTT FIELDS");
            dr = cmd.ExcecuteReader();

            if(dr.Read())
            {
                 //Found the value
            }else
            {
                 //values is not exist..
            }
    }

Using the Insert into SQL in MS Access database

2 Answers2