How do I execute multiple SQL Statements in Access Query Editor

Question

I have a text file with a few SQL statements in it that I want to run on an Access database  I thought that should be possible with Access   Query Editor  So  I go into this editor and paste the statements   insert into aFewYears  yr  values   2000   insert into aFewYears  yr  values   2001   insert into aFewYears  yr  values   2002   insert into aFewYears  yr  values   2003     Trying to run them  by hitting the red exclamation mark  I receive a Missing semicolon     at end of SQL statement     This could be taken as an indication that the editor would allow to execute multiple statements  So  I change the statements and append such a semicolon at the end   insert into aFewYears  yr  values   2000    insert into aFewYears  yr  values   2001    insert into aFewYears  yr  values   2002    insert into aFewYears  yr  values   2003      Then I get a Characters found after end of SQL statement  which probably could be taken as an indication that it is not possible to execute multiple statements   Ok  so the question  is it possible to execute multiple statements in the query editor  or is it possible to somehow batch-execute sql statements in a file in on against Access   Thanks   Rene  edit The insert statements were used as an example and I realize that they are less than perfect  because they all go to the same table and such a thing can obviously somehow be solved by using one statement that has a union or something  In my actual case that I am trying to solve  the file contains not only insert statements but also create table statements and insert statements with different underlying tables  So I hoped  and still hope  that there is something like my beloved SQL Plus for Oracle that can execute a file with all kinds of SQL Statements

User · Answer

You might find it better to use a 3rd party program to enter the queries into Access such as WinSQL I think from memory WinSQL supports multiple queries via it's batch feature.

I ultimately found it easier to just write a program in perl to do bulk INSERTS into an Access via ODBC. You could use vbscript or any language that supports ODBC though.

You can then do anything you like and have your own complicated logic to handle the importing.

User · Answer

create a macro like this  Option Compare Database Sub a   DoCmd RunSQL  quot DELETE   from TABLENAME where CONDITIONS quot  DoCmd RunSQL  quot DELETE   from TABLENAME where CONDITIONS quot  End Sub

User · Answer

I hoped  and still hope  that there is something like my beloved SQL Plus for Oracle that can execute a file with all kinds of SQL Statements    If you re looking for a simple program that can import a file and execute the SQL statements in it  take a look at DBWConsole  freeware    I have used it to process DDL scripts  table schema  as well as action queries   It does not return data sets so it s not useful for SELECT queries   It supports single line comments prefixed by -- but not multi-line comments wrapped in         It supports command line parameters     If you want an interactive UI like Oracle SQL Developer or SSMS for Access then Matthew Lock s reference to WinSQL is what you should try

User · Answer

Unfortunately  AFAIK you cannot run multiple SQL statements under one named query in Access in the traditional sense     You can make several queries  then string them together with VBA  DoCmd OpenQuery if memory serves    You can also string a bunch of things together with UNION if you wish

User · Answer

You can easily write a bit code that will read in a file  You can either assume one sql statement per line  or assume the    So  assuming you have a text file such as   insert into tblTest  t1  values   2000     update tbltest set t1    2222         where id   5    insert into tblTest  t1 t2 t3          values   2001   2002   2003      Note the in the above text file we free to have sql statements on more then one line   the code you can use to read   run the above script is   Sub SqlScripts       Dim vSql       As Variant    Dim vSqls      As Variant    Dim strSql     As String    Dim intF       As Integer     intF   FreeFile      Open  c  sql txt  For Input As  intF    strSql   input LOF intF    intF     Close intF    vSql   Split strSql           On Error Resume Next    For Each vSqls In vSql       CurrentDb Execute vSqls    Next  End Sub   You could expand on placing some error msg if the one statement don t work  such as  if err number  lt  gt  0 then    debug print  sql err   amp  err Descripiton  amp   -- gt   vSqls end dif   Regardless  the above split   and string read does alow your sql to be on more then one line

User · Answer

Better just create a XLSX file with field names on top row  Create it manually or using Mockaroo  Export it to Excel or CSV  and then import it to Access using New Data Source -  From File  IMHO it s the best and most performant way to do it in Access

[ms-access] How do I execute multiple SQL Statements in Access' Query Editor?

Examples related to ms-access