Excel to Access Import Error

Question

Purpose: To import data from excel to ms access (.mdb) database.

Reference: https://www.mikesdotnetting.com/article/79/import-data-from-excel-to-access-with-asp-net

Technology: C#.net Windows Forms

Error: "The Microsoft Jet database engine cannot find the input table or query 'Persons$'. Make sure it exists and that its name is spelled correctly."

Code:

 private void button6_Click(object sender, EventArgs e)
        {
            string Access = @"c:\exportdb\DestinationDB.mdb";
            string connect = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\exportdb\DestinationDB.mdb;";

            using (OleDbConnection conn = new OleDbConnection(connect))
            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = "INSERT INTO [MS Access;Database=" + Access + "].[Persons] SELECT * FROM [Persons$]";
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
        }

Note: I have created a MS Access database named "DestinationDB.mdb" with table name as "Persons" with the following fields: ContactID, FirstName, SecondName, Age

Thereafter i have exported the same to excel in order to retain the header structure.

Once this excel is exported, i added some 10 records to it manually.

Both the files are located under "c://exportdb/source.xls" & "c://exportdb/DestinationDB.mdb".

Excel Snapshot:

Ms Access Snapshot:

Please help me to resolve the error stated above.

Thanks!

Answer 1

Your Connection String doesn't match the reference you're following:

Your Connection String:

 string connect = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\exportdb\DestinationDB.mdb;";

His Connection String has the Extended Properties and is specifying Excel 8.0:

string connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Excel +";Extended Properties=Excel 8.0;";

The other problem is that your datasource is the Access Database DestinationDB.mdb, notice how the reference you're following specifies Excel as the datasource.

Answer 2

The error sounds correct. It looks like Persons$ is in the spreadsheet but you've opened a connection to the database (you can't do a "insert into select from" because they're not in the same database or linked). You will likely need to open up a connection to both (then query one, insert into the other). Something like the below pseudo code would work:

1.) Open a connection to the access database like you have.

2.) Open a separate connection to the spreadsheet.

3.) Query the Persons$ in the spreadsheet, return say a DataReader (ExecuteReader).

4.) Iterate over the DataReader and run an insert statement for each row (inserting into the access database). Use a parameterized statement for this to protect against SQL injection/things that will mess the SQL up inadvertently. You're talking from one data source and putting in into the other record by record.

If you're concerned the entire batch of records making it in, use a transaction and rollback if there are any errors. That will ensure you get all or nothing.