hello I am archiving experimenta 1

[VickyC]

hello

I am archiving experimental data that is held if over 2550 tables like this...

TestData_1a
TestData_1b
TestData_1c_Ron
TestData_2_Sue
TestData_3a_part1
etc....

I want these tables renamed as....

TestData_0001_of_2550
TestData_0002_of_2550
TestData_0003_of_2550
...
TestData_2550_of_2550

The numbering order of these tables is exactly the same as it was in the original listing.
All of the tables have exactly the same structure, differing only in data and table name.
In fact, not all 2550 tables are in the same mdb file, but I can work around that.

I hope to automate this by using VBA to build and run a queryDef that loops 2550 times. On the 30th iteration, for example, I'd like ...

SELECT * FROM [the name of the 30th table in the list] INTO [the new table name, TestData_0030_of_2550] ....

I guess my main problem is knowing how to refer to, say, the 30th table listed.

Any help with this is appreciated

Vicky

 

[dhookom]

Do you want to simply rename the tables or create new tables?

I think I would use a query to build a list

SELECT msysObjects.Name
FROM msysObjects
WHERE (((msysObjects.Name) Not Like "msys*") AND ((msysObjects.Type)=1));

Paste this into Excel and use some expressions/calculations to create the new table names. Then paste the values back into an Access table to use in your code.

Duane
Hook'D on Access
MS Access MVP

 

[VickyC]

thanks for your help Duane.

I've already tried using For Each tdf in db.TableDefs etc..., and this produces the same exact same list as the method you suggested. But, when I've tested these methods on some random mdb files, it doesn't always produce the files in the same order that they appear in the database window.

For example, one of my mdb files shows tables in this order...
tblSetsAvail, tblSetsNotAvail, tbl_0_Comps, tbl_1_Comps, tbl_2_Comps,...etc.

But, when I use either of the query methods above, the tables are listed in the order...
tbl_0_Comps, tbl_1_Comps, tbl_2_Comps, tblSetsAvail, tblSetsNotAvail,...etc.
I'm guessing that the "_"s are affecting how the tables are ordered.

In my case, the order of the table names is very important, because when renamed, the 1st table will contain "0001_of_2550" in its name, the 2nd table will contain "0002_of_2550", etc.

Also, you asked if I was just renaming the tables, or making new ones. I just want to rename them, but was hoping to not have to manually cut and paste 2550 names. So, I was thinking of using SELECT FROM (old table names looped in the order they appear in the db window) INTO (new names that contain "0001_of_2550", "0002_of_2550", etc.) I don't really want to create new tables, but I can't think how else I can automate the renaming.

Any thoughts? Thanks, Vicky C.

 

[dhookom]

Try create a query like:

SELECT Id, [Name]
FROM msysObjects
WHERE [Name] Not Like "msys*" AND [Type]=1 AND Flags=0
ORDER BY msysObjects.Id;

I would try something like the following with a copy of the database:

Private Sub cmdChangeName_Click()
[green]    ' TestData_0001_of_2550[/green]
[green]    ' TestData_0002_of_2550[/green]
[green]    ' TestData_0003_of_2550[/green]
    Dim strSQL As String
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim rs As DAO.Recordset
    Dim intTableCount As Integer
    Dim intI As Integer
    Dim strBegin As String
    Dim strNewName As String
    
    strBegin = "TestData"
    Set db = CurrentDb
    strSQL = "SELECT msysObjects.Id, msysObjects.Name " & _
        "FROM msysObjects " & _
        "WHERE (((msysObjects.Name) Not Like 'msys*') AND ((msysObjects.Type)=1) AND ((msysObjects.Flags)=0)) " & _
        "ORDER BY msysObjects.Id;"
    Set rs = db.OpenRecordset(strSQL)
    With rs
        .MoveLast
        intTableCount = .RecordCount
        .MoveFirst
        intI = 1
        Do Until .EOF
            Set td = db.TableDefs(.Fields("name"))
            strNewName = strBegin & "_" & Format(intI, "0000") & "_of_" & Format(intTableCount, "0000")
            Debug.Print "Old: " & td.Name & "    New: " & strNewName
            td.Name = strNewName
            .MoveNext
            intI = intI + 1
        Loop
        .Close
    End With
    
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[VickyC]

Thanks for the ideas, Duane. Awesome solution.
Vicky C.