Solved

Change the data source for a query with VBA

Posted on 2004-09-24
9
278 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
  • 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
 
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now