How do I execute multiple SQL Statements in Access' Query Editor?

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.


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...

Tags:

Ms Access