Adding queries to groups using VBA

[BSJWright]

I have a number of groups in a particular database. These are based on the month that they relate to, but they are also split into four separate groups for each month. So for example, I will have four groups for November 2014:

November 2014 Raw Data (which contains linked tables in another database)
November 2014 (which contains the bulk of the queries that are used for processing)
November 2014 Reporting (which are the queries that are used for month end reporting - obviously)
November 2014 Misc (anything that doesn't fit into the above, although there are always queries in these groups)

Now, I have been using this database since 2012, so as you can imagine there are quite a significant number of groups. Adding queries to new groups for each new month is therefore starting to take a while by scrolling down to the bottom... Is there a way to do this by using VBA? It would save a bit of time each month. :banghead:

 

[Minty]

You shouldn't need to make new queries every month! Simply add dates as parameters that allow the same queries to be reused over and over. Then you could also report over more than one month...

You can even add the queries to a form, that has date from / to controls to allow simple running / exporting of the query data.

 

[BSJWright]

You shouldn't need to make new queries every month! Simply add dates as parameters that allow the same queries to be reused over and over. Then you could also report over more than one month...

You can even add the queries to a form, that has date from / to controls to allow simple running / exporting of the query data.

Thanks Minty, but the problem is that the source data changes each month (a changing portfolio with different data downloaded separately each month, but also our parent company likes to change the field names from in Business Objects without telling us), which is the main reason for doing it this way. Believe me, if it was a simple date query I would have implemented that solution a long while ago... And I know it seems stupid to use Access when we have Business Objects but it is quicker and easier to customise our reporting in Access, rather than a) wait for our German colleagues to develop custom reports; and b) wait for the IT issues that we have with Business Objects to be resolved - it is a major issue at the moment.

 

[Minty]

Okay - I can understand that !

I would have thought the easiest route would be to add the new data to an existing overall raw data table in the data base, then run from there.

If they add fields or change field names you would only have to accommodate it once during the import, then your existing reporting / queries etc. should still work.

You could even record the field name changes/mapping in your database to assist with the import at a later date? In fact that approach would make most sense.

 

[essaytee]

I don't envy you your problem. I had something similar typed as per Minty but he beat me to the punch, so pointless posting that.

I'm assuming groups are in alphabetical order (haven't got Access open at the moment), why not simply name them YYYY-MM and entries within those groups also YYYY-MM-QueryName. At least they will be at the top of the list.

If need be you could also weave a bit of VBA magic (QueryDefs) and rename all the existing queries.

 

[BSJWright]

I don't envy you your problem. I had something similar typed as per Minty but he beat me to the punch, so pointless posting that.

I'm assuming groups are in alphabetical order (haven't got Access open at the moment), why not simply name them YYYY-MM and entries within those groups also YYYY-MM-QueryName. At least they will be at the top of the list.

If need be you could also weave a bit of VBA magic (QueryDefs) and rename all the existing queries.

Yes, I have the raw data in a separate database anyway (once this one started getting massive, I felt that it was necessary to split the data from the queries). I create the bulk of the queries just before month end via VBA using SQL (much easier and quicker). The groups I am referring to though are the ones in the left hand side-bar, which are basically shortcuts to queries. It would be helpful if it was possible to simply move queries into these... I have attached an image so you can see what I mean. I don't mind having the group names as they are, it's just the pain of having to move the queries each month. I have also been thinking of making a common structure to the DB, as minty has suggested, so that any problems with the fieldnames can be eliminated, it's also a case of finding the time to do that though...

 

[Minty]

I've seen somewhere (but can't find it currently) how to us sysObjects to rename database objects, but I'm not sure about groups as it's a container rather than an object. OR is it ?