Solved

Change the data source for a query with VBA

Posted on 2004-09-24
9
302 Views
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.
0
Comment
Question by:AaronGreene1906
[X]
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
9 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
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.
0
 
LVL 12

Accepted Solution

by:
pique_tech earned 500 total points
ID: 12146614
Yes.

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

Expert Comment

by:shanesuebsahakarn
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
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 12

Expert Comment

by:pique_tech
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
0
 

Author Comment

by:AaronGreene1906
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.
0
 
LVL 12

Expert Comment

by:pique_tech
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.  
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
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.
0
 

Author Comment

by:AaronGreene1906
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.
0
 

Author Comment

by:AaronGreene1906
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
0

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

756 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