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

sumdumgai

Registered User.
Local time
Today, 13:41
Joined
Jul 19, 2007
Messages
453
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.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 17:41
Joined
Jul 9, 2003
Messages
16,244
Sounds like you have major design problems with your dB ..

Suggest you explain what you want to achieve. I'm sure you will get some good advice.
 

sumdumgai

Registered User.
Local time
Today, 13:41
Joined
Jul 19, 2007
Messages
453
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

Banishment Pending
Local time
Today, 12:41
Joined
May 11, 2011
Messages
11,611
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?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 17:41
Joined
Jul 9, 2003
Messages
16,244
Access DB max size 2GB!!!
Still think you might have a design issue. Have you taken advantage of using lookup tables to get rid of loads of text entries, which would bloat your tables?

The attached example database will copy a selected years records into an archive table. It names each row with the year you are copying. However, to do this you need to have a form to hold the year.
 

Attachments

  • AchiveYear_1a.zip
    4.3 MB · Views: 323

sumdumgai

Registered User.
Local time
Today, 13:41
Joined
Jul 19, 2007
Messages
453
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:41
Joined
Oct 29, 2018
Messages
21,358
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

You've got your good things, and you've got mine.
Local time
Today, 13:41
Joined
May 21, 2018
Messages
8,463
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.

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

Registered User.
Local time
Today, 13:41
Joined
Jul 19, 2007
Messages
453
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.

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've got your good things, and you've got mine.
Local time
Today, 13:41
Joined
May 21, 2018
Messages
8,463
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:41
Joined
Feb 19, 2002
Messages
42,971
If a record has field value 202101
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
 

sumdumgai

Registered User.
Local time
Today, 13:41
Joined
Jul 19, 2007
Messages
453
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

Registered User.
Local time
Today, 13:41
Joined
Jul 19, 2007
Messages
453
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?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:41
Joined
Feb 19, 2002
Messages
42,971
You would define the table name in the query using VBA. I believe others have mentioned that you CANNOT change the structure of a querydef using parameters. You can only use parameters to provide a data value in the selection criteria, not as a substitute for a structural element such as the name of a table.
 

GPGeorge

Grover Park George
Local time
Today, 10:41
Joined
Nov 25, 2004
Messages
1,776
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.
 

Users who are viewing this thread

Top Bottom