Solved

Change the data source for a query with VBA

Posted on 2004-09-24
9
295 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
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

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

860 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