编写动态SQL语句 [英] Writing Dynamic SQL Statement

问题描述

我是Microsoft Access的新手.

我想根据参数的值从表中选择一列(即我的表具有x，y和z列，而我有一个chosencol参数，该参数由用户使用下拉菜单设置. /p>

我可以使用select命令选择一个/所有列，但是，我想使用我的参数chosencol来做到这一点.

已经阅读了很多书，我发现了许多有关使用SET和EXEC命令的参考，但是，在Access中将它们输入到SQL命令中只会产生错误.

请有人就我如何在Access中实现动态SQL查询提供建议(详细信息，因为我认为我目前在错误的位置编写了命令...)

解决方案

首先，我在Access中创建了一个示例表.

接下来，我创建了一个示例表单来查询您的值.该下拉列表称为"chosencol".从列选择"下拉列表中选择一个值，然后按查找值"按钮.

这是查找值"按钮的单击时"事件下的代码. SQL语句是使用您选择的列动态构建的.该列被重命名为[FieldName]以使其可以被引用.

Private Sub btnLookup_Click()
    Dim rsLookup As New ADODB.Recordset
    Dim strSQL As String
    strSQL = "select " & chosencol.Value & " as [FieldName] from Table1 where ID=1"
    rsLookup.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    If rsLookup.EOF = False Then
        txtValue.SetFocus
        txtValue.Text = rsLookup![FieldName]
    End If
    rsLookup.Close
End Sub

按下按钮时，将返回您选择的任何列中的值.对于这个简单的示例，我总是返回第1行的数据.

I am very new to Microsoft Access.

I would like to select a column from a table based on the value of a parameter (i.e. my table has columns x, y and z and I have a chosencol parameter which is set by the user using a dropdown.

I can select one / all of the columns using a select command, however, I would like to do this using my parameter chosencol instead.

Having read around, I have found a number of references to using the SET and EXEC commands, however, entering them into the SQL command in Access just yields errors.

Please could someone advise me as to how I go about implementing a dynamic-sql query in Access (in fine detail as I think I am writing the commands in the wrong place at the moment...)

解决方案

First I created an example table in Access.

Next, I created an example form to query your value. The dropdown is called 'chosencol'. Select a value from the Column Select dropdown and press the "Lookup Value" button.

Here is the code under the "Lookup Value" button's On Click event. A SQL statement is dynamically built with the column you chose. The column is renamed to [FieldName] to that it can by referenced.

Private Sub btnLookup_Click()
    Dim rsLookup As New ADODB.Recordset
    Dim strSQL As String
    strSQL = "select " & chosencol.Value & " as [FieldName] from Table1 where ID=1"
    rsLookup.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    If rsLookup.EOF = False Then
        txtValue.SetFocus
        txtValue.Text = rsLookup![FieldName]
    End If
    rsLookup.Close
End Sub

When the button is pushed, the value from whatever column you selected will be returned. For this simple example, I'm always returning row 1's data.

这篇关于编写动态SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！