<p>I am using MS Access 2003. I want to run a lot of insert SQL statements in what is called 'Query' in MS Access. Is there any easy(or indeed any way) to do it?</p>

[{'answer_id': 62572, 'author': 'Rikalous', 'author_id': 4271, 'author_profile': 'https://Stackoverflow.com/users/4271', 'pm_score': 1, 'selected': False, 'text': '<p>No - a query in Access is a single SQL statement. There is no way of creating a batch of several statements within one query object. \nYou could create multiple query objects and run them from a macro/module.</p>\n'}, {'answer_id': 62583, 'author': 'Jonathan', 'author_id': 6910, 'author_profile': 'https://Stackoverflow.com/users/6910', 'pm_score': 2, 'selected': False, 'text': '<p>Personally, I\'d create a VBA subroutine to do it, and connect to the database using some form of sql connection.</p>\n\n<p>Off the top of my head, the code to do it should look something like:</p>\n\n<pre><code>Sub InsertLots ()\n    Dim SqlConn as Connection\n    SqlConn.Connect("your connection string")\n    SqlConn.Execute("INSERT &lt;tablename&gt; (column1, column2) VALUES (1, 2)")\n    SqlConn.Execute("INSERT &lt;tablename&gt; (column1, column2) VALUES (2, 3)")\n    SqlConn.Close()\nEnd Sub\n</code></pre>\n'}, {'answer_id': 65027, 'author': 'BIBD', 'author_id': 685, 'author_profile': 'https://Stackoverflow.com/users/685', 'pm_score': 6, 'selected': True, 'text': '<p>yes and no.</p>\n\n<p>You can\'t do:</p>\n\n<pre><code>insert into foo (c1, c2, c3)\nvalues ("v1a", "v2a", "v3a"),\n       ("v1b", "v2b", "v3b"),\n       ("v1c", "v2c", "v3c")\n</code></pre>\n\n<p>but you can do</p>\n\n<pre><code>insert into foo (c1, c2, c3)\n    select (v1, v2, v3) from bar\n</code></pre>\n\n<p>What does that get you if you don\'t already have the data in a table? Well, you could craft a Select statement composed of a lot of unions of Selects with hard coded results. </p>\n\n<pre><code>INSERT INTO foo (f1, f2, f3)\n    SELECT *\n    FROM (select top 1 "b1a" AS f1, "b2a" AS f2, "b3a" AS f3 from onerow\n    union all\n    select top 1 "b1b" AS f1, "b2b" AS f2, "b3b" AS f3 from onerow\n    union all \n    select top 1 "b1c" AS f1, "b2c" AS f2, "b3c" AS f3 from onerow)\n</code></pre>\n\n<p>Note: I also have to include a some form of a dummy table (e.g., onerow) to fool access into allowing the union (it must have at least one row in it), and you need the "top 1" to ensure you don\'t get repeats for a table with more than one row</p>\n\n<p>But then again, it would probably be easier just to do three separate insert statements,\nespecially if you are already building things up in a loop (unless of course the cost of doing the inserts is greater than the cost of your time to code it).</p>\n'}, {'answer_id': 66817, 'author': 'David-W-Fenton', 'author_id': 9787, 'author_profile': 'https://Stackoverflow.com/users/9787', 'pm_score': 2, 'selected': False, 'text': "<p>I think it's inadvisable to propose a particular data interface, as Jonathan does, when you haven't clarified the context in which the code is going to run.</p>\n\n<p>If the data store is a Jet database, it makes little sense to use any form of ADO unless you're running your code from a scripting platform where it's the preferred choice. If you're in Access, this is definitely not the case, and DAO is the preferred interface.</p>\n"}, {'answer_id': 148584, 'author': 'onedaywhen', 'author_id': 15354, 'author_profile': 'https://Stackoverflow.com/users/15354', 'pm_score': 1, 'selected': False, 'text': '<p>@Rik Garner: Not sure what you mean by \'batch\' but the </p>\n\n<pre><code>INSERT INTO foo (f1, f2, f3)\n    SELECT *\n    FROM (select top 1 "b1a" AS f1, "b2a" AS f2, "b3a" AS f3 from onerow\n    union all\n    select top 1 "b1b" AS f1, "b2b" AS f2, "b3b" AS f3 from onerow\n    union all \n    select top 1 "b1c" AS f1, "b2c" AS f2, "b3c" AS f3 from onerow)\n</code></pre>\n\n<p>construct, although being a single SQL <em>statement</em>, will actually insert each row one at a time (rather than all at once) but in the same transaction: you can test this by adding a relevant constraint e.g. </p>\n\n<pre><code>ALTER TABLE foo ADD\n   CONSTRAINT max_two_foo_rows\n      CHECK (2 &gt;= (SELECT COUNT(*) FROM foo AS T2));\n</code></pre>\n\n<p>Assuming the table is empty, the above <code>INSERT INTO..SELECT..</code> should work: the fact it doesn\'t is because the constraint was checked after the first row was inserted rather than the after all three were inserted (a violation of ANSI SQL-92 but that\'s MS Access for you ); the fact the table remains empty shows that the internal transaction was rolled back.</p>\n\n<p>@David W. Fenton: you may have a strong personal preference for DAO but please do not be too hard on someone for choosing an alternative data access technology (in this case ADO), especially for a vanilla <code>INSERT</code> and when they qualify their comments with, " Off the top of my head, the code to do it should look something like…" After all, you can\'t use DAO to create a <code>CHECK</code> constraint :)</p>\n'}, {'answer_id': 15187738, 'author': 'user2129206', 'author_id': 2129206, 'author_profile': 'https://Stackoverflow.com/users/2129206', 'pm_score': 2, 'selected': False, 'text': '<p>MS Access does not allow multiple insert from same sql window. If you want to <strong><em>insert</em></strong>, say <strong>10 rows in table</strong>, say <strong>movie (mid, mname, mdirector,....)</strong>, you would need to \nopen the sql windows, </p>\n\n<ol>\n<li>type the 1st stmt, execute 1st stmt, delete 1st stmt</li>\n<li>type the 2nd stmt, execute 2nd stmt, delete 2nd stmt</li>\n<li>type the 3rd stmt, execute 3rd stmt, delete 3rd stmt ......</li>\n</ol>\n\n<p>Very boring. \nInstead you could import the lines from excel by doing:</p>\n\n<ol>\n<li>Right-click on the table name that you have already created</li>\n<li>Import from Excel (Import dialog box is opened)</li>\n<li>Browse to the excel file containing the records to be imported in the table</li>\n<li>Click on "Append a copy of the records to the table:"</li>\n<li>Select the required table (in this example movie)</li>\n<li>Click on "OK"</li>\n<li>Select the worksheet that contains the data in the spreadsheet</li>\n<li>Click on Finish</li>\n</ol>\n\n<p>The whole dataset in the excel has been loaded in the table "MOVIE"</p>\n'}, {'answer_id': 16428930, 'author': 'Mark', 'author_id': 2360066, 'author_profile': 'https://Stackoverflow.com/users/2360066', 'pm_score': 1, 'selected': False, 'text': "<p>MS Access can also Append data into a table from a simple text file. CSV the values (I simply used the Replace All box to delete all but the commas) and under External Data select the Text File.</p>\n\n<pre><code>From this:\nINSERT INTO CLASS VALUES('10012','ACCT-211','1','MWF 8:00-8:50 a.m.','BUS311','105');\nINSERT INTO CLASS VALUES('10013','ACCT-211','2','MWF 9:00-9:50 a.m.','BUS200','105');\nINSERT INTO CLASS VALUES('10014','ACCT-211','3','TTh 2:30-3:45 p.m.','BUS252','342');\nTo this:\n10012,ACCT-211,1,MWF 8:00-8:50 a.m.,BUS311,105\n10013,ACCT-211,2,MWF 9:00-9:50 a.m.,BUS200,105\n10014,ACCT-211,3,TTh 2:30-3:45 p.m.,BUS252,342\n</code></pre>\n"}, {'answer_id': 65726799, 'author': 'John Bentley', 'author_id': 872154, 'author_profile': 'https://Stackoverflow.com/users/872154', 'pm_score': 0, 'selected': False, 'text': '<p>Based on the VBA workaround from @Jonathan, and for execution in the current Access database:</p>\n<pre class="lang-vb prettyprint-override"><code>Public Sub InsertMinimalData()\n  CurrentDb.Execute &quot;INSERT INTO FinancialYear (FinancialYearID) VALUES (\'FY2019/2020\');&quot;\n  CurrentDb.Execute &quot;INSERT INTO FinancialYear (FinancialYearID) VALUES (\'FY2020/2021\');&quot;\nEnd Sub\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/62504', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/6613/']