在 Ms-Access 中将表名作为查询参数传递 [英] Passing a table name as a query parameter in Ms-Access

问题描述

我正在开发一个由多个具有相同结构的表组成的访问数据库.我想要做的是使用表单上的组合框来选择表，然后使用所选表名执行查询.

I'm working on a access database that is composed of multiple tables with the same structure. What I am trying to do is use a combo box on a form to select the table and then execute a query with the chosen table name.

除了正在使用的表名之外，查询将是相同的.

The query would be the same except for the table name that's being used.

我在将表名从组合框中传递给查询时遇到问题.

I'm having trouble passing the table name from the combo box to the query.

我知道它可能不是最好的数据库结构，但它是我需要做的.如果有人有任何建议可以分享，那就太好了！

I know its probably not the best database structure but its what I need to do. If anyone has any advice to share that would be great!

谢谢！

推荐答案

您不能简单地使用预编译的 SQL 查询来做到这一点.你需要一点 VBA 才能让它运行起来，这就是你要做的.

You cannot simply do that using a pre compiled SQL Query. You need a little bit of VBA to get it going, this is how you would do it.

用一个 ComboBox 和一个按钮创建一个表单.

Create a Form with one ComboBox and one button.

将 ComboBox 命名为 tableNameCombo，将按钮命名为 runQueryBtn.保存表单，名称为 frm_QueryRun.

Name the ComboBox as tableNameCombo and the button as runQueryBtn. Save the Form, with the name frm_QueryRun.

创建一个新的查询，

SELECT * FROM randomTableName;

另存为 qry_Tmp.

现在返回表单设计，然后在表单的属性表上，查找当前方法.然后将以下代码粘贴到 Form Current 中.

Now go back to Form design, then on Property sheet of the Form, look for the Current Method. Then paste the following code into the Form Current.

注意:如果这是您的第一个 VBA，请查看:http://www.baldyweb.com/FirstVBA.htm

Note: If this is your First VBA, check out : http://www.baldyweb.com/FirstVBA.htm

Private Sub Form_Current()
    Dim tblStr As String
    Dim dbObj As DAO.Database, tdObj As DAO.TableDef

    Set dbObj = CurrentDB()

    Me.tableNameCombo.RowSourceType = "Value List"
    For Each tdObj In db.TableDefs
        If Left(tdObj.Name, 4) <> "MSys" Then tblStr = tblStr & tdObj.Name & ";"
    Next

    tblStr = Left(tblStr, Len(tblStr)-1)
    Me.tableNameCombo.RowSource = tblStr
    Set dbObj = Nothing
End Sub

一旦完成，您将需要构建您的 qry_Tmp.类似的，点击按钮.

Once this is done, you would need to construct your qry_Tmp. something like, on the click of the button.

Private Sub runQueryBtn_Click()
    Dim dbObj As DAO.Database, qdObj As DAO.QueryDef
    If Me.tableNameCombo.ListIndex = -1 Then
        MsgBox "Table Name needs to be selected, before continuing.", vbCritical
        Exit Sub
    End If

    Set dbObj = CurrentDB()
    Set qdObj = dbObj.QueryDefs("qry_Tmp")

    qdObj.SQL = "SELECT " & Me.tableNameCombo & ".* FROM " & Me.tableNameCombo & ";"

    qdObj.Execute dbFailOnError

    qdObj.Close
    Set qdObj = Nothing
    Set dbObj = Nothing
End Sub

保存表单，关闭它，为任何错误编译代码.然后运行代码.希望这可以帮助.

Save the Form, Close it, compile the code for any error. Then run the code. Hope this helps.

这篇关于在 Ms-Access 中将表名作为查询参数传递的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！