Results 1 to 8 of 8
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Rename Tables (ForLoop) based on records in a *key table*

    Hello:

    I need some assistance with updating/creating a ForLoop which renames tables. I have attached 2 DBs for illustration purposes:

    1. "Rename Tables v01.accdb"
    - Contains 3 tables.
    - Upon opening DB, form "F01_RenameTables".
    - Either click "Add Prefix PHNSY" or "Remove Prefix PHNSY" command buttons to rename tables.


    - Existing VBA uses a ForLoop to handle the table renaming process.

    The above version works great; however, I now need to modify the ForLoop to handle the renaming process differently. See 2nd ACCDB for details.

    2. "Rename Tables v02.accdb"
    - v02 contains same 3 tables included in v01.
    - v02 also contains an additional 2 tables {[LK_ACCIDENT], [LK_EMERGENCY]}.
    - Finally, it also contains **key table** [00_TABLE_NAMES] which -- for testing purposes only -- lists the three (3) v01 tables: {[LK_BLS_SOURCE],[LK_CIV_MIL],[LK_GRADE]}

    Envisioned Process:
    - Upon click the command button "Rename Tables" (in form), all existing tables that are listed in [00_TABLE_NAMES].[TABLE_NAME_OLD] shall be renamed.
    - Their new names should changed to the values listed in [00_TABLE_NAMES].[TABLE_NAME_NEW].

    Final Table Naming Convention once the VBA is executed:
    a. [00_TABLE_NAMES] itself is NOT found in [00_TABLE_NAMES].[TABLE_NAME_OLD]. No change.
    b. [LK_ACCIDENT] is NOT found in [00_TABLE_NAMES].[TABLE_NAME_OLD]. No change.
    c. [LK_BLS_SOURCE] is found in [00_TABLE_NAMES].[TABLE_NAME_OLD]. Table shall be renamed to [TABLE_ABC].
    d. [LK_CIV_MIL] is found in [00_TABLE_NAMES].[TABLE_NAME_OLD]. Table shall be renamed to [TABLE_KLM].
    e. [LK_EMERGENCY] is NOT found in [00_TABLE_NAMES].[TABLE_NAME_OLD]. No change.
    f. [LK_GRADE] is found in [00_TABLE_NAMES].[TABLE_NAME_OLD]. Table shall be renamed to [TABLE_XYZ].

    Of course, once this VBA has been executed and the three (3) tables have been renamed, executing the VBA routine a 2nd time would NOT update any of the 6 tables in this DB... i.e., after renaming the 3 tables, their new names are not listed in [00_TABLE_NAMES].[TABLE_NAME_OLD].

    Any ideas/recommendations as to how I can rename tables from/to based on records in the **key table**?

    Thank you,
    Tom
    Attached Files Attached Files

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    1. Create a function that accepts an old table name and new table name as parameters. This function searches the tabledefs for the old table name and renames it to the new table name provided in said paremeters.
    2a. Create a function that queries the 00_TABLE_NAMES and loops over each record, passing the old and new names to the function created in step 1.
    2b. finish function #2 with a query that deletes the records in 00_TABLE_NAMES

    This should be pretty straight forward. Post the code you've tried if you get stuck.

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    kd2017... thanks, I'll give it a try.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Perhaps look at all your other renaming code?
    https://www.accessforums.net/search.php?searchid=588975
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    kd2017:

    I apologize for the delayed response... I was side-tracked w/ something else. Anyhow, thank you again for yesterday's recommendation. I was able to change the VBA function so that renaming of tables occurs (based on the "mapping table"). However, the renaming is offset by n tables given that I have -- purposely for testing -- fewer LK tables listed in [00_TABLE_NAMES] than I have total # of LK tables in dbs.TableDefs.

    Attached zip file contains the following:
    1. "Rename Tables v01 -- Before.accdb" -- file name is self-explanatory. It contains the 5 LK tables with their original names.
    2. "Rename Tables v02 -- After.accdb" -- reference that incorrect table names. Please refer to [00_TABLE_NAMES] and each LK tables [FIELD1] to see the underlying issue.
    3. "Issues and Summary.pptx" -- includes 3 slides to illustrate/summarize the incorrect renaming of tables.

    As stated on slide #3, how can I keep the correct linkage between TDF.Name and its associated matching record in [00_TABLE_NAMES].[TABLE_NAME_OLD] so that the renaming function works properly?

    Thank you for any help on this in advance.

    Tom
    Attached Files Attached Files

  6. #6
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Your procedure is overly complicated.

    Your psuedo code could look like this
    Code:
    ...
    for each tdf in dbs.tabledefs
        if tdf.name = rs!table_name_old then
            tdf.name = rs!table_name_new
            ...
            Exit For 'go ahead and leave the inner loop after you've renamed the table as you shouldn't need to rename the same table name more than once
    ...
    You'll want to improve your error handling. For instance you may want to double check that you table name that your trying to change to to make sure it doesn't already exist.

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You don't need to loop through the tabledefs collection, only through the 00_TABLE_NAMES recordset.
    Code:
    Private Sub cmdRenameTable_Click()
    
    
        On Error Resume Next
        Set dbs = CurrentDb
    
    
        Dim rs As DAO.Recordset
        Dim n As Integer
        
        Set rs = CurrentDb.OpenRecordset("SELECT * FROM 00_TABLE_NAMES")
        
        'Check to see if the recordset contains records
        If Not (rs.EOF And rs.BOF) Then
            rs.MoveFirst
            Do Until rs.EOF = True
    
    
            'Rename table(s) name based on [00_TABLE_NAMES]
            'For Each tdf In dbs.TableDefs
                
                'If tdf.Name Like "LK_*" Then
    
    
                    'Stores table names in TEMP fields
                    'Me.txtOld = rs!TABLE_NAME_OLD
                    'Me.txtNew = rs!TABLE_NAME_NEW
                    
                    'Execute renaming of table(s)
                    'tdf.Name = Me.txtNew
                    n = n + 1
                
                'End If
            'Next
            CurrentDb.TableDefs(rs("TABLE_NAME_OLD")).Name = rs("TABLE_NAME_NEW")
                'Move to the next record
                rs.MoveNext
            Loop
        
        Else
            MsgBox "There are no records in the recordset.", vbInformation, "Status"
        End If
        
        MsgBox n & " LK tables have been renamed!", vbInformation, "Status"
        
        rs.Close                        'Close the recordset
        Set rs = Nothing                'Clean up
        
        'Refresh navigation pane
        RefreshDatabaseWindow
            
    End Sub
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    kd2017 -- thank you for the feedback. After viewing your code, I fully agree.... it was overly complicated. Upon replacing the required (pseudo) code, the no "renaming" of tables took place.

    Vlad -- thank you for your input. Your recommended functions works 100% as envisioned! Many thanks!!!

    Again, thank you for assisting w/ this post. 'Much appreciated.

    Cheers,
    Tom

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-12-2021, 06:54 AM
  2. Replies: 1
    Last Post: 05-20-2021, 04:27 PM
  3. VBA ForLoop to rename all tables with same *prefix*
    By skydivetom in forum Programming
    Replies: 7
    Last Post: 04-02-2021, 09:26 AM
  4. Replies: 3
    Last Post: 05-14-2015, 04:17 PM
  5. Rename cell based on duplicate compound key
    By luckycharms in forum Access
    Replies: 1
    Last Post: 04-23-2012, 06:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums