Using MS Access Query to switch from one table to another based on user input

Question

To be honest, I have tried searching the internet and have not found a way to achieve this. So am hoping Stackoverflow might be able to help here. Am open to options (parameterization, use of VBA, etc etc). I don't want to create multiple queries that might grow each month due to this.

I will be getting multiple tables each month and I have to use the same query to fetch from these new tables and I don't want to change my query each time or write a new one each time. The query is something like this (I have tried to anomalize the names of APIs endpoints or the department):

SELECT MyDeptAprilLogs.apiproxy, Sum(MyDeptAprilLogs.sum_message_count) AS SumOfsum_message_count
FROM MyDeptAprilLogs   <--- This is variable & so, must be a parameter (won't know until I get it)
GROUP BY MyDeptAprilLogs.apiproxy, MyDeptAprilLogs.response_status_code
HAVING (((MyDeptAprilLogs.apiproxy)="MyDept-MyAPI-Endpoint1-v6" Or 
         (MyDeptAprilLogs.apiproxy)="MyDept-MyAPI-Endpoint2-v5" Or 
         (MyDeptAprilLogs.apiproxy)="MyDept-MyAPI-Endpoint3-v6") AND 
       ((MyDeptAprilLogs.response_status_code)=200));

Table cannot be dynamic in query object. Would require VBA and QueryDefs to modify. — June7
– June7, Commented Jun 9, 2020 at 10:53
Does this answer your question? Passing a table name as a query parameter in Ms-Access — June7
– June7, Commented Jun 9, 2020 at 11:00
June, could you pls explain with a code sample ? It would be helpful. I saw another user commenting (replying) about performance based on VBA. So could you pls check ? This table has a huge volume of records so am kind of leaning to that possibility as well. Anyway, a code sample that would help and I can try it out. — S.S.Prabhu
– S.S.Prabhu, Commented Jun 9, 2020 at 11:01

Ssp · Accepted Answer · 2020-06-09 10:58:35Z

1

So far as I know, there is no direct way of achieving this. With VBA or forms, we can get input from user and pass it to the query but I don't think the table name can be changed.

I have tried via VBA to spawn out to different tables based on input but query execution from VBA is never ending and consumes resources and hangs.

answered Jun 9, 2020 at 10:58

Ssp

263 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

June7 Over a year ago

Have you tried VBA using QueryDefs to modify query object?

Applecore · Accepted Answer · 2020-06-09 11:05:01Z

1

I would recommend creating a QueryDef, and then modifying the .SQL as required through VBA:

Sub sSwitchQuerySource(strTable As String)
    Dim strSQL As String
    strSQL = "SELECT L.apiproxy, Sum(L.sum_message_count) AS SumOfsum_message_count" _
        & " FROM [" & strTable & "] AS L " _
        & " GROUP BY L.apiproxy, L.response_status_code " _
        & " HAVING (((L.apiproxy)='MyDept-MyAPI-Endpoint1-v6' Or " _
        & " (L.apiproxy)='MyDept-MyAPI-Endpoint2-v5' Or " _
        & " (L.apiproxy)='MyDept-MyAPI-Endpoint3-v6') AND " _
        & " ((L.response_status_code)=200));"
    CurrentDb.QueryDefs("qryLog").SQL = strSQL
End Sub

In this piece of code I've replaced the double quotes around the text strings with single quotes, used square brackets around the table name (just in case it has spaces in), and then aliased the table name (AS L') in theFROM` part.

You may also want to look at querying a system table, MSysObjects, to populate a combo box that lists the matching tables in your database that the user may select from.

Regards,

answered Jun 9, 2020 at 11:05

Applecore

4,0792 gold badges11 silver badges15 bronze badges

1 Comment

S.S.Prabhu Over a year ago

Thanks Applecore. I will try it out and let you know.

Collectives™ on Stack Overflow

Using MS Access Query to switch from one table to another based on user input

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related