1

My program is written in VB.NET and I have an excel sheet with only one column and type of column is Text. Now I want to add all the rows of excel sheet into MS Access table.

My Access table has 5 columns, and I have to provide some static data for rest of fields. I can't let them empty as my table structure won't allow me to let them empty. they are mandatory.

My algorithm works this way

  • Read excel sheet data into a DataTable
  • Loop to the end of all rows of DataTable

  • Append text of each cell and some of my static information to make a query string

    INSERT into TableName(c1,c2,c3) 
values 
(v1,v2,v3),
(v1,v2,v3),
(v1,v2,v3),
(v1,v2,v3)
...
...
(n1,n2,n3);
  • Execute this query

But I got error in this query, Any suggestions?

umirza47
  • 940
  • 2
  • 10
  • 21
  • You would need to provide which columns you're inserting if you're only doing 3 out of 5, you would also need to do 1 insert line per row. – BugFinder Oct 04 '12 at 07:23
  • If I do one INSERT per row then I am taking risk on performance. It slows down the loop – umirza47 Oct 04 '12 at 07:25
  • I have also viewed [this post](http://stackoverflow.com/questions/1212943/sql-code-to-insert-multiple-rows-in-ms-access-table) but still no progress – umirza47 Oct 04 '12 at 07:27
  • OK, what actual error are you getting? – BugFinder Oct 04 '12 at 07:29
  • Invalid Query! I have also tried to execute this query manually on MS Access and got that ';' missing at (+) INSERT into TableName(c1,c2,c3) values (v1,v2,v3)+ – umirza47 Oct 04 '12 at 07:32
  • Show an actual query example then - as most likely its a data thing – BugFinder Oct 04 '12 at 07:33
  • Sorry I can't show you my query! I tried same query in mySQL and SQLServer as well and it shows no error. Why MS Access not allowing me to do so? Would you like to mention any query by which we can add multiple inserts using OleDB Command execution? – umirza47 Oct 04 '12 at 09:26
  • If you cant show your query, I cant help you, access is a crappy tool half the time, but, if you wont even show some of your code, or a limited cut down but with actual data that fails.. You're expecting too much of the world. – BugFinder Oct 04 '12 at 09:37

1 Answers1

2

You can run SQL against a connection to interact directly with Access and Excel. For example, working with the connection:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Z:\Test.xlsm;
Extended Properties="Excel 12.0 xml;HDR=Yes;";

Or Jet

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Z:\Test.xls;
Extended Properties="Excel 8.0;HDR=Yes;";

You can run the following SQL:

INSERT INTO [;DATABASE=Z:\Docs\Test.accdb].Table1 (ID,Atext) 
SELECT ID, AText FROM [Sheet7$]

Or Jet

INSERT INTO [;DATABASE=Z:\Docs\Test.mdb].Table1 (ID,Atext) 
SELECT ID, AText FROM [Sheet7$]
Fionnuala
  • 90,370
  • 7
  • 114
  • 152