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

user_180275 0
Viewed 130917

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.

ms-access
6 Answers

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.

user_12662860

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

enter image description here

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_18985240

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

user_7202420
Related