如何在 Access 的查询编辑器中执行多个 SQL 语句? [英] How do I execute multiple SQL Statements in Access' Query Editor?

问题描述

我有一个文本文件，其中包含一些我想运行的 SQL 语句在 Access 数据库上.我认为应该可以使用 Access'查询编辑器.所以，我进入这个编辑器并粘贴语句:

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')

尝试运行它们(通过点击红色感叹号)我收到一个
SQL 语句末尾缺少分号 (;).

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');

然后我得到一个
SQL 语句结束后找到的字符.
这可能被视为不可能的迹象执行多条语句.

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.

好的，那么问题来了:是否可以在查询编辑器，或者是否有可能以某种方式批量执行 sql 语句文件在/上/反对 Access.

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.

谢谢/雷内

edit 以插入语句为例，我意识到它们并不完美，因为它们都位于同一张表中，显然可以通过使用一个语句以某种方式解决这种问题那有一个工会什么的.在我试图解决的实际案例中，该文件不仅包含插入语句，还包含创建表语句和具有不同基础表的插入语句.所以我希望(并且仍然希望)有像我心爱的 SQL*Plus for Oracle 那样的东西，它可以执行包含各种 SQL 语句的文件.

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.

推荐答案

您可以轻松编写可读取到文件中的位代码.您可以假设每行一条 sql 语句，也可以假设 ;

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');

请注意，在上述文本文件中，我们可以在多于一行中自由添加 sql 语句.

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

如果 one 语句不起作用，您可以扩展放置一些错误消息，例如

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

无论如何，上面的 split() 和 string read 确实允许您的 sql 位于多于一行...

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

这篇关于如何在 Access 的查询编辑器中执行多个 SQL 语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！