How to resolve name of linked table in Access 2000 with VB6 code?

Hello. We make new tables each year with same structure but slightly different table data in MS SQl Server.
So if the table is called, MY_TABLE_2021, for this year it will be called MY_TABLE_2022. The only thing that changes is the year number in the table name.

We have a problem with maintaining Access 2000 application, and people here just copy the same 50 MB mdb file over and over again, when just the year in the linked table names changes.

I want to develop a VB6 script that will resolve the table name by getting it from the computer system time, or maybe simpler, just type the current year in a text box.

I am a relatively new to Access 2000 and don't know really where to start. Some guidance is welcome.

Any idea on the topic?

Linking tables go from where to where? Access linked in SQL-Server, or the other way round?

Tables exist originally in MS SQL Server, and people here tend to link them as ODBC data-sources with an Access 2000 table name, not necessarily same as the name of the table in SQL Server, which becomes confusing, misleading and error prone.

And you need to re-link the tables for the new year, each year, and change the name so that they can be found by the Access 2000 macros and queries.

For example, lets say, table in MS SQL Server is called, MY_TABLE_2022 for this year. In Access it is called for example THE_TABLE_2000 but it links to MY_TABLE_2021 for the previous year in SQL Server.

Now you will need to

1. link MY_TABLE_2022,
2. rename THE_TABLE_2000 to THE_TABLE_2000_something,
3. rename MY_TABLE_2022 to THE_TABLE_2022 and
4. delete the dummy link THE_TABLE_2000_something,

because macros/queries reference table called THE_TABLE_2000 in Access 2000.

Why trying to resolve/relink the OLD table?
Just delete it, and link to the new table
https://stackoverflow.com/questions/...code-over-odbc

In the Link i posted look at accepted answer in the code, pretty much at the bottom: A TableDef-Object has an "SourcetableName"-Attribute/Property