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.

Fribble

Using LINQPad Query Editor, we can execute multiple queries by adding Go after each statement – Clavate

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

Sternwheeler

Why use a variable of type Variant? Just so you can skip using a counter an UBound()? When I first saw this, I thought it looked like code from a non-Access refugee, and then see it's from you, Albert. I'd never do it this way. I'm splitting strings, so the array should be of type string, in my opinion. – Pedo

@David W. Fenton -- "Why use a variable of type Variant?" -- sometimes VBA doesn't give you much choice in the matter and this is one of them. The benefit of using Split and For Each is that the code is IMO more readable... but that's a question of taste. I notice you more often than not omit the ByVal/ByRef keywords, resulting in implicit ByRef whereas explicit ByVal would be more appropriate. But just a matter of taste, no biggie. – Kelda

Yes for each means I don't use ubound() command. It also saves declaring counter for the for/next loop. I find it a bit less typing and I Don’t have to use an array references like vSql(I) . And, also in place of "Next I" I can use: "Next" (so I can change the variable used but and less other places in code effected if you re-name the var used). "for each" var has to be variant, however the array (As Dave points out) really should have been declared an string array Dim vSql() As String – Sternwheeler

In re: ByRef/ByVal. I use ByRef when it's appropriate and ByVal when it's appropriate. If I don't specify in my code, I've determined that ByRef is appropriate. Please point out a case in any of my code where ByRef is clearly wrong (not just a matter of taste). – Pedo

@onedaywhen: in this case, there's no need for the Variant data type, (I originally thought that it was needed to use a variant in the For/Each, but it's not -- you can use Albert's code with the array declared as type String). The results from Split() are strings, so in my opinion, they should be stored in an array of type String. – Pedo

In re: "saves declaring counter" -- but you have to declare your item variable, which serves the same purpose as a counter. With a counter, you would execute CurrentDb.Execute vSql(i), so the number of variable declarations is identical. I did learn something here, in that I didn't know that with a string array you could use a variant item variable in the For/Each loop. – Pedo

@David W. Fenton: "I originally thought that it was needed to use a variant in the For/Each". My VBA6 complier disagrees: when I change the type for Variant to String I get a compile error, "For Each control variable on arrays must be Variant." That seems fairly clear to me :) – Kelda

@David W. Fenton: "Please point out a case in any of my code where ByRef is clearly wrong (not just a matter of taste)." -- Try this: #1016330: "IsOrderOpen(CustID as Long)" -- why would a function that checks the state of an entity ("IsOrderOpen") seek to alter its key value? As this is an intrinsic value-type (i.e. Long) parameter, it should be passed ByVal. – Kelda

Such a great idea to use VBA to improve Access the application itself. It never crossed my mind! – Klemperer

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.

Jelene

Answer 3

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.

Animalist

I cannot edit XLXS files with vim, so that is not an option for me. – Ulotrichous

@RenéNyffenegger you can use csv's - but why do you have Access but no Excel? why no "full" DBMS? – Embrangle

Answer 4

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.

Klemperer

WinSQL Lite is free, but Dev and Pro aren't. see compare – Butterball

does not execute multiple statements against Access. – Sunglasses

Did you try the query terminator function? – Klemperer

Answer 5

create a macro like this

Option Compare Database

Sub a()

DoCmd.RunSQL "DELETE * from TABLENAME where CONDITIONS"

DoCmd.RunSQL "DELETE * from TABLENAME where CONDITIONS"

End Sub

Gabriel

Answer 6

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

Shoa