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.

Answer 1

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 <> 0 then    debug.print "sql err" & err.Descripiton & "-->" vSqls end dif 

Regardless, the above split() and string read does alow your sql to be on more then one line...

Answer 2

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.