在 vba 中 MS 访问 SELECT INTO

MS access SELECT INTO in vba

提问by Scotch

I'm having some issues with some functionality of my application. There is a particular instance where I have an instance of a 'pending class' on a form for an administrator to review. The form is populated with students associated with this pending class. After their grades are finished, I have a button at the footer that will delete this class from my 'pending' table and add the grades to all of the students. This works. However, I want to essentially copy this pending class, which just has the class name, date, and teacher to a completed class table before it's deleted from pending. Since no data about this class other than the primary key(class number) persists throughout this form, I can't populate the other fields(class name, date) of the row into my completed class table.

我的应用程序的某些功能存在一些问题。有一个特定的实例，我在表单上有一个“待定类”的实例供管理员查看。该表单填充了与此待处理课程相关联的学生。完成他们的成绩后，我在页脚处有一个按钮，可以从我的“待处理”表中删除该课程并将成绩添加到所有学生。这有效。但是，我想从根本上将这个挂起的班级复制到一个完整的班级表，然后从挂起中删除它。由于除了主键（类号）之外没有关于这个类的数据在整个表单中持续存在，我无法将行的其他字段（类名、日期）填充到我完成的类表中。

I am trying a "SELECT INTO" operation in VBA to get these values. It's going like this:

我正在 VBA 中尝试“SELECT INTO”操作来获取这些值。它是这样的：

   dim cname as String
   dim classdate as Date
   dim pid as integer
   dim teacher as String
   dim qry as String

    pid = [Forms]![frmClasses]![txtID]

   qry = "Select className INTO cname FROM tblPending WHERE tblPending.id = " & " ' " & pid & " ' " & ";"

   db.execute qry
   debug.print qry
   debug.print cname   

From here, I do the same operations for each other variable, build my INSERT query, and execute it. The problem is-- my select into's are not working. Debug.print shows that the local variables were never initialized from the SELECT INTO statement. Any thoughts?

从这里开始，我对每个其他变量执行相同的操作，构建我的 INSERT 查询并执行它。问题是--我的select into 不起作用。Debug.print 显示局部变量从未从 SELECT INTO 语句初始化。有什么想法吗？

采纳答案by Christian Specht

First, having all classes in one table and just setting a "NotPending" or "Completed" column would be better.
Having two identical tables for classes and moving values from one into the other to indicate status changes is bad database design.

首先，将所有类放在一个表中并只设置“NotPending”或“Completed”列会更好。
拥有两个相同的类表并将值从一个移动到另一个以指示状态更改是糟糕的数据库设计。

---

---

If you reallyneed to do this by using two tables and copying rows, then you need an INSERT INTOquery(and not SELECT INTO), as already mentioned by Remou in the comments, because SELECT INTOcreates a new table (or overwrites an existing one with the same name, if already there).

如果您确实需要通过使用两个表并复制行来做到这一点，那么您需要一个INSERT INTO查询（而不是SELECT INTO），正如 Remou 在评论中已经提到的，因为SELECT INTO创建一个新表（或覆盖现有的同名表，如果已经在那里）。

The syntax for INSERT INTOlooks like this:

的语法INSERT INTO如下所示：

INSERT INTO CompletedClassTable (ClassName, Teacher)
SELECT ClassName, Teacher FROM tblPending WHERE id = 123

---

---

And finally, you asked this in a comment:

最后，您在评论中提出了这个问题：

So SELECT INTO is completely different in Access than Oracle? In Oracle and PL/SQL, you can select a row into a variable OR a table. In Access can you not select into a variable?

那么 SELECT INTO 在 Access 中与 Oracle 完全不同？在 Oracle 和 PL/SQL 中，您可以将行选择到变量或表中。在 Access 中，您不能选择变量吗？

To load a row into a variable, you need to use a Recordset.

要将一行加载到变量中，您需要使用Recordset。

Example code to load your query into a Recordset and output the ClassNamefield:

将查询加载到 Recordset 并输出ClassName字段的示例代码：

Dim RS As DAO.Recordset

Set RS = CurrentDb.OpenRecordset("SELECT * FROM tblPending WHERE id = 123")
If Not RS.EOF Then
    Debug.Print RS("classname")
End If
RS.Close
Set RS = Nothing

回答by HansUp

Seems you want to retrieve a text value, className, from tblPendingwhere tblPending.idmatches the value found in your text box, txtID, and store that text value in a string variable named cname.

似乎您想要检索一个文本值 ,className从tblPendingwheretblPending.id与文本框中找到的值匹配txtID，并将该文本值存储在名为 的字符串变量中cname。

If that interpretation is correct, you needn't bother with a query and recordset. Just use the DLookup Functionto retrieve the value, similar to this untested code sample.

如果这种解释是正确的，您就不必为查询和记录集而烦恼。只需使用DLookup 函数来检索值，类似于这个未经测试的代码示例。

Dim cname As String
Dim pid As Integer
Dim strCriteria As String
pid = [Forms]![frmClasses]![txtID]
strCriteria = "id = " & pid
cname = Nz(DLookup("className", "tblPending", strCriteria), vbNullString)
Debug.Print "cname: '" & cname & "'"

Notes:

笔记：

1. I assumed the data type of the idfield in tblPendingis numeric. If it is actually text data type, change strCriterialike this:

   strCriteria = "id = '" & pid & "'"

2. DLookup()returns Null if no match found. Since we are assigning the function's return value to a string variable, I used Nz()to convert Null to an empty string. Alternatively, you could declare cname As Variant (so that it can accept a Null value) and get rid of Nz().

1. 我假设id字段的数据类型tblPending是数字。如果它实际上是文本数据类型，则更改strCriteria如下：

   strCriteria = "id = '" & pid & "'"

2. DLookup()如果未找到匹配项，则返回 Null。由于我们将函数的返回值分配给字符串变量，因此我曾经Nz()将 Null 转换为空字符串。或者，您可以将 cname 声明为 Variant（以便它可以接受 Null 值）并删除Nz().