a question about Renaming Tables by code 1

[VickyC]

hello to all

I have a large collection of table that I need to rename using values from the following table:

[u]OldVal   NewVal[/u]  
  101      101
  103      102
  108      103
  113      104
  ...     ...  
 1940     1639
 1947     1649           
 1952     1650

There are 1550 OldVal numbers, running from 101 to 1952 in ASC order. The NewVal numbers run CONSECUTIVELY from 101 to 1650. In all cases, NewVal <= OldVal.

Here's what the table names look like:

[u]Current 'Old' name                             New table name[/u]
 PN_00101_of_1952                                PN_00101_of_1650
 PN_00103_of_1952                                PN_00102_of_1650
 PN_00108_of_1952                                PN_00103_of_1650
 PN_00113_of_1952                                PN_00104_of_1650
 ....                                            ....
 PN_01940_of_1952                                PN_01648_of_1650
 PN_01947_of_1952                                PN_01649_of_1650
 PN_01952_of_1952                                PN_01650_of_1650

Now, changing the 1952 to 1650 is easy, using code like...

Public Sub RenameTables()
  Dim db As DAO.Database, tdf As DAO.TableDef
  Set db = CurrentDb

  For Each tdf In db.TableDefs
    If Right(tdf.Name, 4) = "1952" Then
        tdf.Name = Left(tdf.Name, Len(tdf.Name) - 4) & "1650"
    End If
  Next tdf
End Sub

But, I'm having trouble changing the first number in each table name. I could do something similar to the code above, but I have no assurance that the 'For Each...' processes the tables in the same order that they appear in the db window. This could lead to assigning a new name that already exists. I'm especially worried about using bulh SQL coding for this same reason. Is there a safe and reliable way to change these table names?

thanks in advance
Vicky C.

 

[PHV]

The For Each processes the tables in alphabetic order, so you can try (do a backup before) this:

Public Sub RenameTables()
  Dim db As DAO.Database, tdf As DAO.TableDef
  Set db = CurrentDb
  Dim i As Integer: i = 101
  For Each tdf In db.TableDefs
    If tdf.Name Like "PN_#####_of_####" Then
        tdf.Name = "PN_" & Format(i, "00000") & "_of_1650"
        i = i + 1
    End If
  Next tdf
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VickyC]

Thanks PHV. This works fine.
Vicky

 

[Golom]

Just as a matter of casual interest, why do you have 1,550 tables? Are there really that many distinct logical entities in your system?