How to use user input to build table name in query

[sumdumgai]

Hello. Is there a way to solicit user input and use that input to create a table name in a query? For example, when the query asks for the parameter, the user enters for parameter 'year', "2021". The Access query then builds a table name as "Table " + year, resulting in a table name 'Table 2021'. The query would then use that name to insert records into that table.

Thanks.

 

[sumdumgai]

I'd like to give the user an easy way to archive records. Records are added in bulk on a monthly basis to a production database. One of the fields in the record is 'Year_Month' (format yyyymm). When the user wants to archive last year's records, the query is executed. It asks the user for the yyyymm. User enters yyyymm. The query selects records from the production table using yyyymm, and appends the records to a new table 'Table yyyymm'. User can then go to the archive database and import the 'Table yyyymm'. A delete query would then be used to remove the archived records from the production table.

I could do this in VBA but just wondered if a simple query could be used.

Thanks.

 

[plog]

I'm with uncle Gizmo. Stop telling us the details of what you want to execute and start telling us the purpose.

Why must records be 'archived'? Why can't this table just hold all your data?

 

[sumdumgai]

Access DB max size 2GB!!!

 

[theDBguy]

Access DB max size 2GB!!!

A query alone can't do it, but VBA can. What is the size of your db now? How many records does it have?

 

[sumdumgai]

Thanks for all the suggestions. No one is going to pay to redesign the database. It's been in use for years and serving its purpose. So, can I please get an answer to my original question. Can I form a query that creates a table name from user input and use that table name to update a table? For example, something like "INSERT INTO [Table " + [?] + "]"

Thanks.

 

[sumdumgai]

A query alone can't do it, but VBA can. What is the size of your db now? How many records does it have?

1.8 GB 4.5 mil records that go back several years.

 

[theDBguy]

1.8 GB 4.5 mil records that go back several years.

If you do a C&R right now, will it reduce the file size?

 

[theDBguy]

So, can I please get an answer to my original question. Can I form a query that creates a table name from user input and use that table name to update a table? For example, something like "INSERT INTO [Table " + [?] + "]"

Hi. I sort of answered that question earlier. I said you can't do it that way. What you could do is use VBA instead.

 

[MajP]

So, can I please get an answer to my original question. Can I form a query that creates a table name from user input and use that table name to update a table? For example, something like "INSERT INTO [Table " + [?] + "]"

As stated you cannot do "dynamic sql" in access, but you can do it through vba. If this was SQL Server you could build this dynamically.

SQL Server Dynamic SQL

This tutorial shows you how to use the SQL Server dynamic SQL to construct general purpose and flexible SQL statements.

www.sqlservertutorial.net

Does the user really need to tell it what table? I would think this happens automatically since the database should know which records go into which archive. As long as all archives are named consistently.

 

[sumdumgai]

Thanks to all.

 

[sumdumgai]

As stated you cannot do "dynamic sql" in access, but you can do it through vba. If this was SQL Server you could build this dynamically.

SQL Server Dynamic SQL

This tutorial shows you how to use the SQL Server dynamic SQL to construct general purpose and flexible SQL statements.

www.sqlservertutorial.net

Does the user really need to tell it what table? I would think this happens automatically since the database should know which records go into which archive. As long as all archives are named consistently.

"the database should know which records go into which archive" - please explain. If a record has field value 202101 (Jan 2021), what name would the archive table have to have? I can rename the archive tables to whatever I want.

 

[MajP]

You said your table naming convention is Table_yyyymm

User can then go to the archive database and import the 'Table yyyymm'

so 202101 record would get archived in Table_202101. Or maybe I misunderstood. I am assuming you have a Front end back end/s set up where you are linked to all the archived tables.

 

[sumdumgai]

You said your table naming convention is Table_yyyymm

so 202101 record would get archived in Table_202101. Or maybe I misunderstood. I am assuming you have a Front end back end/s set up where you are linked to all the archived tables.

Yes, the production database contains tables that are linked to tables in the archive database.

 

[sumdumgai]

So, you are not using actual date fields? Instead, you are mushing two attributes into a single field?

In the archive form, specify the year, assuming you are archiving an entire year at a time, That will be used in the name of the new table and with the Left() function as selection criterial

Got it. Thanks. This is without VBA, right?

 

[GPGeorge]

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.

 

[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

 

[sumdumgai]

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