Change the data source for a query with VBA

Is it possible to change the source table for a query with VBA?  I have several tables with identical structures, and I would like to create a command button on a form that will switch tables, so I can use the same query and form for each table.
Aaron GreeneProgrammerAsked:
Who is Participating?
pique_techConnect With a Mentor Commented:

For these kinds of "purely Access" questions, I prefer DAO.  The only assumption here is that you have created aliases for your tables in your original query design (I used the alias 'datasource' for my table in the code below).  Here's approximately what you'd do in the On Click event of your button.  Replace {QueryYouWantToModify} and {YourNewTableName} with the appropriate values, which you can retrieve from your form, for example, or you can hard-code.

    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim strSQL As String
    Dim strRight As String
    Dim strLeft As String

    Set db = CurrentDb()
    Set qd = db.QueryDefs("{QueryYouWantToModify}")   'this is the query for which you want to change the recordsource

    strLeft = Left(qd.SQL, InStr(1, qd.SQL, "FROM ") - 1)
    strRight = IIf(InStr(1, qd.SQL, "WHERE "), Right(qd.SQL, Len(qd.SQL) - InStr(1, qd.SQL, "WHERE ") + 1), "")
    strSQL = strLeft & "FROM " & "{YourNewTableName}" & " datasource " & strRight

    qd.SQL = strSQL

    Set qd = Nothing
    Set db = Nothing

That said, if you have forms based on a query based on a single table, why not just base your form on the table and use filters and the form's recordsource property to present the desired data to the user?
You can change the SQL of a query by altering the querydef:

CurrentDb.QueryDefs("MyQuery").SQL="SELECT * FROM SomeTable"

You'd have to write some kind of parser for the SQL though if you just want to change the table. It would be simpler just to change the form's record source. Also, you shouldn't generally have multiple tables with the same structure - this usually suggests a violation of data normalisation rules.
pique_tech, bear in mind of course that the SQL might have specified table names in the SELECT part, as in:

SELECT TableA.Field1, TableA.Field2 FROM Table2
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

That's why I advised the user that the original query would have to alias the tablename:  
>The only assumption here is that you have created aliases for your tables in your original query design
Aaron GreeneProgrammerAuthor Commented:
I'm not familar with using aliases.  I think that I may have to create a query for each table and then switch queries.  I have calculations in the form that would need to change when the recordset changes.  I think.
Alias is pretty easy.  In query design mode, open the Properties panel.  Click on the table.  The properties panel will change to only two lines.  Enter your table alias in the first line.  Then the table name's title bar in the design panel will change to whatever alias you've provided.

If the data is of the same kind from query to query, then your form's calculations should be able to handle changing data.  But as I asked above, can you consider just changing the form's record source, because that might make things easier for you.  
Here's a thought - you could rename the tables rather than trying to rewrite the queries.

Let's say all your queries are based on Table1. You have 4 tables, TableA,TableB etc. You can rename the table that you want to use as the record source to Table1:

Rename "Table1", acTable, "TableA"

Then when you want to use TableB, just name Table1 back to TableA and rename TableB to Table1.
Aaron GreeneProgrammerAuthor Commented:
I think I am going to go with switching queries.  That does the job and I understand what I'm doing in case it changes later.  It has led me to another question, though.  I want to create a list box that has the names of all of the queries, and by double clicking the list box change to that query.
Aaron GreeneProgrammerAuthor Commented:
This is what I've come up with so far.

Private Sub List0_DblClick()
    Dim strName As String
        strName = Me.List0.Column(2)
        DoCmd.Close acForm, "frmEmpList"
        Forms!frmTimeSheetInput.Form.Recordset = strName
End Sub
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.