How to use user input to build table name in query (1 Viewer)

[sumdumgai]

How about, instead of messing about with tables and queries and naming and renaming, you simply make a back up copy of the accdb and call it the "archive" for the previous year? Then in the currently active accdb, you can delete records not intended for current use going forward.

A bit kludgy but I could create say 10 empty tables, same design as production table, on the archive database. I think the archive db can hold about 10 years worth of records. I'll name these tables Archive2021, Archive202, etc. On the production db, I'll set up a select/append query that will ask for the year. The query selects all records for that year and appends them to an empty table, say 'Archive_Temp'. The user is then instructed to open the archive db and Import the 'Archive_Temp' table from the production db into the correct 'Archiveyyyy' table.
Not critical but is there a way, via table design, to prevent import into wrong table based on field 'Year_Month' value?
That would do me for 10 years.

 

[The_Doc_Man]

Mechanically, you can do it in a couple of lines of VBA. You have some constant strings for the front and back part of the query (SELECT in the front, WHERE in the back). Then using perhaps a combo box with a short list of year numbers, concatenate

strSQL = "SELECT thisfield, thatfield, theother field, ... FROM " & tablenamestring & " WHERE DatePart( 'Y', datefield )  = " & CStr(  selectedyear ) & ";"
currentdb.Execute strSQL

Passing a table name as a query parameter in Ms-Access

I'm working on a access database that is composed of multiple tables with the same structure. What I am trying to do is use a combo box on a form to select the table and then execute a query with the

stackoverflow.com

 

[Pat Hartman]

The point is that you build the maketable queries in VBA.

Dim strSQL as String
strSQL = "Select SomeTable.* Into tbl" & Me.tblYear & " from SomeTable Where ForecastYear = " & Me.TblYear & ";"

You could use a querydef to get the year for the where clause but NOT to change the name of the make table since that table name is structural. Querydefs are compiled with an execution plan the first time they are saved. You can only create an execution plan if you know the name of the table at that time. The actual criteria value is irrelevant. doesn't matter if it's 298 or 5 million. What would be relevant is if you were using LIKE or = as the relational operator. Those are structural since they would impact how the query engine would determine the best way to select the data.

 

[sumdumgai]

Thanks to all of you who helped. This is the best forum!!!