Results 1 to 4 of 4

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

  1. #1

    Thread Starter
    Addicted Member kutlesh's Avatar
    Join Date
    Jun 2018
    Location
    Skopje, Macedonia
    Posts
    202

    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?

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,419

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

    Linking tables go from where to where? Access linked in SQL-Server, or the other way round?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3

    Thread Starter
    Addicted Member kutlesh's Avatar
    Join Date
    Jun 2018
    Location
    Skopje, Macedonia
    Posts
    202

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

    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.
    Last edited by kutlesh; Feb 14th, 2023 at 07:44 AM.

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,419

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

    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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width