Replace part of table name using wildcard

[KeenKiwi]

G'day
I've inherited a database that imports a text file each month, which is then
manually saved to an empty but formatted (i.e. with column labels and data
types pre-set) existing table with the name "Data Mmm" e.g. Data Mar. There
is an existing SELECT query called "Mmm Source" e.g. Mar Source, which
references the relevant month's table. This is called from another SELECT
query called "Major" which has to be manually updated to point at the correct
Mmm Source query, in the process of exporting the results to an Excel
spreadsheet. So, just to be clear, at the beginning of the year there are 12
empty but pre-formatted tables Data Mmm, and 12 queries Mmm Source each
pointing to the respective table, plus another query called Major. With my
own very limited knowledge, I'm thinking this is far from perfect. However,
since I don't have the time to dismantle it, I'm trying to make it as easy as
possible to use.

The following is used to update the name of the query:-

LastMonth = InputBox("Enter old Month", "Old Month Selection")
ThisMonth = InputBox("Enter new Month", "New Month Selection")

Application.CurrentDb.QueryDefs("Major").SQL = _
Replace(Application.CurrentDb.QueryDefs("Major").SQL, (LastMonth & "
Source"), (ThisMonth & " Source"))

This works fine when the existing query referenced is from the previous
month, but doesn't accommodate if an older routine was run again, for example.

I gather that Replace doesn't allow wildcards, any ideas how I can ensure
the old query name is correctly replaced?

I have, incidentally, attempted writing the query in code each month, which
would avoid the issue, but haven't got the hang of it. I'm also not sure what
to do with a SELECT query relative to Execute, DoCmd.RunSql etc.

All reasonable suggestions greatly appreciated.

 

[Douglas J. Steele]

Probably not the answer for which you're looking, but having tables named
"Date Mmm" is generally a sign of a misdesigned database.

Store all your data in a single table (after adding a YearMonth field to
it). Continue importing your data into a temporary table, then run an Append
To query to take the data from that temporary table and put it to the single
table.