Change the data source for a query with VBA

Posted on 2004-09-24
Last Modified: 2008-02-01
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.
Question by:AaronGreene1906
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
LVL 41

Expert Comment

ID: 12146317
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.
LVL 12

Accepted Solution

pique_tech earned 500 total points
ID: 12146614

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?
LVL 41

Expert Comment

ID: 12146737
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

LVL 12

Expert Comment

ID: 12146920
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

Author Comment

ID: 12146938
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.
LVL 12

Expert Comment

ID: 12147006
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.  
LVL 41

Expert Comment

ID: 12147062
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.

Author Comment

ID: 12147224
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.

Author Comment

ID: 12147239
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question