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 execu

Solution 1:

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.

Create a Form with one ComboBox and one button.

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

Create a new Query something along the lines of,

SELECT*FROM randomTableName;

Save this as qry_Tmp.

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.

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

PrivateSub Form_Current()
    Dim tblStr AsStringDim dbObj As DAO.Database, tdObj As DAO.TableDef

    Set dbObj = CurrentDB()

    Me.tableNameCombo.RowSourceType = "Value List"ForEach 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 = NothingEndSub

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

PrivateSub runQueryBtn_Click()
    Dim dbObj As DAO.Database, qdObj As DAO.QueryDef
    IfMe.tableNameCombo.ListIndex = -1Then
        MsgBox "Table Name needs to be selected, before continuing.", vbCritical
        ExitSubEndIfSet dbObj = CurrentDB()
    Set qdObj = dbObj.QueryDefs("qry_Tmp")

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

    qdObj.Execute dbFailOnError

    qdObj.Close
    Set qdObj = NothingSet dbObj = NothingEndSub

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