insert into ms access db from another database with c# code

Question

I use the following code to insert a record from one database to another but it doesn't work. I tried the query in MS-ACCESS 2007 and it works fine but it doesn't work when called programmatically from my C# code?

string query_insert = "INSERT INTO Questionnaires_Table(BranchName,Factor,Region,Branch_ID,Current_Date,No_Employees) "
    + "SELECT BranchName,Factor,Region,Branch_ID,Current_Date,No_Employees "
    + "FROM Questionnaires_Table IN '" + dialog.FileName + "' Where Branch_ID = " + textBox1.Text ;

dbConnDest.Open();


   OleDbDataAdapter dAdapter = new OleDbDataAdapter();
   OleDbCommand cmd_insert = new OleDbCommand(query_insert, dbConnDest);

   dAdapter.InsertCommand = cmd_insert;
   cmd_insert.ExecuteNonQuery();

dbConnDest.Close();

When I take the the content of query_insert in ms access, it works fine

It throws

INSERT INTO syntax error exception in line cmd_insert.ExecuteNonQuery();

EDIT

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace WindowsFormsApplication4
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            OpenFileDialog dialog = new OpenFileDialog();

            dialog.Title = "select database";



            if ((dialog.ShowDialog() == DialogResult.OK) && (textBox1.Text == ""))
            {
                    MessageBox.Show("insert reference year", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            else
            {




                    OleDbConnection dbConnDest;
                    dbConnDest = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= C:\Users\SystemA.accdb;Persist Security Info=False;");

                    try
                    {



                        string query_insert = "INSERT INTO Questionnaires_Table(BranchName,Factor,Region,Branch_ID,Current_Date,No_Employees) "
                                               + "SELECT BranchName,Factor,Region,Branch_ID,Current_Date,No_Employees "
                                               + "FROM Questionnaires_Table1 IN '" + dialog.FileName + "' Where ReferenceYear = " + textBox1.Text + ";";




                        dbConnDest.Open();



                        OleDbCommand cmd_insert = new OleDbCommand(query_insert, dbConnDest);

                        try
                        {
                            cmd_insert.ExecuteNonQuery();
                        }
                        catch (Exception g)
                        {
                            MessageBox.Show(g.ToString());
                        }



                        textBox2.Text = query_insert.ToString();

                        dbConnDest.Close();
                    }
                    catch (Exception h)
                    {
                        MessageBox.Show(h.ToString());
                    }



                }


        }

    }
}

EDIT

possible duplicate of insert into ms access database

Daniel A. White
– Daniel A. White

2013-03-25 17:12:25 +00:00
Commented Mar 25, 2013 at 17:12 — Daniel A. White
– Daniel A. White, Commented Mar 25, 2013 at 17:12
Are you sure you are using IN correctly?

JMK
– JMK

2013-03-25 17:22:07 +00:00
Commented Mar 25, 2013 at 17:22 — JMK
– JMK, Commented Mar 25, 2013 at 17:22
Where do you get that syntax FROM table IN filename ?

Steve
– Steve

2013-03-25 17:37:17 +00:00
Commented Mar 25, 2013 at 17:37 — Steve
– Steve, Commented Mar 25, 2013 at 17:37
In Access can you use IN to point to a file location?

JMK
– JMK

2013-03-25 18:32:04 +00:00
Commented Mar 25, 2013 at 18:32 — JMK
– JMK, Commented Mar 25, 2013 at 18:32
i try it and works perfect

user2183831
– user2183831

2013-03-25 18:47:49 +00:00
Commented Mar 25, 2013 at 18:47 — user2183831
– user2183831, Commented Mar 25, 2013 at 18:47

Steve · Accepted Answer · 2013-03-25 23:03:33Z

1

I have found a different syntax in this Microsoft forum

  INSERT INTO [AccessTable] SELECT * FROM [MS Access;DATABASE=D:\My Documents\db2.mdb].[Table2]

so you could try this

string query_insert = "INSERT INTO Questionnaires_Table " +
    "(BranchName,Factor,Region,Branch_ID,[Current_Date],No_Employees) " +
    "SELECT BranchName,Factor,Region,Branch_ID,[Current_Date],No_Employees " +
    "FROM [MS Access;DATABASE=" + dialog.FileName  + "].Questionnaires_Table " +
    "Where Branch_ID = @branch";

dbConnDest.Open();
OleDbCommand cmd_insert = new OleDbCommand(query_insert, dbConnDest);
cmd_insert.Parameters.AddWithValue("@branch", textBox1.Text);
cmd.ExecuteNonQuery();

Tested with "Provider=Microsoft.ACE.OLEDB.12.0;"

However, with more research it is clear that the syntax error given is due to the presence of a reserved keyword CURRENT_DATE. This could be resolved encapsulating the field name with square brackets.

By the way, the IN syntax works as well once cured the problem of CURRENT_DATE.

edited Mar 25, 2013 at 23:03

answered Mar 25, 2013 at 17:42

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

user2183831 Over a year ago

i use IN because the source database is different from the destination database

user2183831 Over a year ago

i'm using MS ACCESS 2007 .accdb i test this but still nothing happens thank you anyway for your courage

Steve Over a year ago

I have tested the code above (MS Access;.....) and it works with two different database and with ACE provider.

Steve Over a year ago

of course no, I have set up two different databases with a simple table and some data. Then I have done the transfer with the syntax like the one above. The important thing is: REMOVE the IN, add [MS Access;DATABASE=.......YOUR FILE.....;

user2183831 Over a year ago

my databases has different structure does this made the problem?

|

XTGX · Accepted Answer · 2013-03-25 17:12:28Z

1

you are missing 'values' keyword in query_insert.

answered Mar 25, 2013 at 17:12

XTGX

1129 bronze badges

1 Comment

user2183831 Over a year ago

the values is the SELECT query

Denys Denysenko · Accepted Answer · 2013-03-25 17:14:20Z

0

I'm not really familiar with ms access queries but I think you shouldn't use OleDbDataAdapter. The code should look similar to following:

string query_insert = "INSERT INTO Questionnaires_Table(BranchName,Factor,Region,Branch_ID,Current_Date,No_Employees) "
    + "SELECT BranchName,Factor,Region,Branch_ID,Current_Date,No_Employees "
    + "FROM Questionnaires_Table IN '" + dialog.FileName + "' Where Branch_ID = " + textBox1.Text ;

dbConnDest.Open();

OleDbCommand cmd_insert = new OleDbCommand(query_insert, dbConnDest);
cmd_insert.ExecuteNonQuery();

dbConnDest.Close();

answered Mar 25, 2013 at 17:14

Denys Denysenko

7,9341 gold badge22 silver badges31 bronze badges

3 Comments

user2183831 Over a year ago

did you know any other way to insert a record from one database table to other database table?

Denys Denysenko Over a year ago

@user2183831, yes but I didn't use ms-access for a while. What I'm wandering is it is there any way to set db schema explicitly in access? Because it looks like you're inserting into the same table from which you query.

user2183831 Over a year ago

it just the same table name

Michel Vaz Ramos · Accepted Answer · 2013-03-25 18:29:22Z

0

Like others said, thats a SQL syntax problem. You are missing the VALUES keyword, look:

INSERT INTO TABLENAME (COL1, COL2, COL2) VALUES (VAL1, VAL2, VAL3)

Where is your VALUES keyword?

answered Mar 25, 2013 at 18:29

Michel Vaz Ramos

1311 silver badge5 bronze badges

3 Comments

Michel Vaz Ramos Over a year ago

@user2183831: First: try to write the VALUES keyword, like: INSERT INTO (COL1,COL2,COL3) VALUES (SELECT (COL1,COL2,COL3) FROM...) Second: try to use SqlParam in your Command object instead of concatenating strings. Will be better to you to find the errors source.

user2183831 Over a year ago

i still got the same exception

user2183831 Over a year ago

VALUES is the SELECT query

Collectives™ on Stack Overflow

insert into ms access db from another database with c# code

4 Answers 4

16 Comments

1 Comment

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

16 Comments

1 Comment

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related