How to rename table using vba? (1 Viewer)

[akino25]

Hi, I'm having about 100 table with the following name: phres01, phres02, phres03 .....phres100

And I need to rename them to ph01, ph02, ph03 ... ph100 respectively

thus removing the "res" part of each table.

Since the ctrl+h doesn't work for access LOL im in hella trouble.

 

[Galaxiom]

It can be done but those names suggest a serious structural problem with your database.

Describe your databse structure and we can help you put it right and get the data out of those tables into what they should be.

 

[Mihail]

Are you aware that, by doing this, all your queries, row sources for forms, for reports, for controls in forms or the code for aggregate functions like DSum, DLookup etc will go to hell ?
After this action everything will stop to work. Your database will become a simple collection.

 

[missinglinq]

...those names suggest a serious structural problem with your database...

They do, indeed! Any bets that all 100 Tables have the exact same Fields in them?

Linq ;0)>

 

[Mihail]

))))))))))))))))) No bet.

 

[akino25]

didn t know it would cause such side effect lol.

But can't vba just rename those with a function? instead of right cliking the table and on rename then change it? those table are simply 2 field with data. Like ID and phone number nothing more.

And yes they all have the same field exact field. I just have to rename it ... just did that manually yesterday but it ll be cool to learn if it could be done using vba...

 

[Mihail]

Dim tbl As TableDef
Dim NewName As String
    With CurrentDb
        For Each tbl In .TableDefs
            If Left(tbl.Name, 5) = "phres" Then
                NewName = "ph" & Mid(tbl.Name, 6)
                DoCmd.Rename NewName, acTable, tbl.Name
            End If
        Next tbl
    End With

And you must understand that every one of us avoid (or should do that) to teach someone something that can irremediable damage his/her database.

 

[akino25]

Dim tbl As TableDef
Dim NewName As String
    With CurrentDb
        For Each tbl In .TableDefs
            If Left(tbl.Name, 5) = "phres" Then
                NewName = "ph" & Mid(tbl.Name, 6)
                DoCmd.Rename NewName, acTable, tbl.Name
            End If
        Next tbl
    End With

And you must understand that every one of us avoid (or should do that) to teach someone something that can irremediable damage his/her database.

Hi thx. but does this really mess up other thing in access ? because right now my database only contains data no function no qry at all... so can't tell if something goes wrong

 

[Mihail]

YES. As I say, can damage (maybe irremediable) the database.

 

[way2bord]

I can't believe it - all these posts and not one reference to the holy grail of database architecture:

Please wiki: "Data Normalization"
Then combine your 100 tables into 1 so no one here has to ... :banghead:

im in hella NorCal.

 

[Mihail]

This is not a database, way2.
This is a collection of tables that store a collection of... things.
And no request for help to design one.