Mass Table Rename

[jesusoneez]

I've been asked to come up with a way (one-off) of renaming over 100 hundred tables. The table are all called "STUD_ADMIN_blah". We need to remove the "STUD_ADMIN_" part of the table name.

Is there a nice easy way of doing this without having to physically rename each table individually?

Thanks in advance.

Steve

 

[namliam]

Yes there most definatly is...

Check out the Tabledefs object (currentdb.tabledefs) in the Access help and i think you can go from there.

Regards

 

[jesusoneez]

Thanks for replying. I am however a noober, and although I found the tabledefs object in the help, I've no VB knowledge and I didn't really understand any of it. I can't see how I'd change the name of all tables using a module using this object.

More prompting..?

Regards,

Steve

 

[reclusivemonkey]

Steve,

Unless someone is kind enough to write this code for you, you need to learn how to construct a simple loop in VBA. You would then loop through each table in your database, get the name, remove the first eleven characters, then rename the table with this. If you want to become more adept with access (I presume from your email you are the Access guru at Swiftcs), you will gain a LOT of productivity from VBA. How long would it take you to manually rename each table? Even if you spend this equivalent amount of time learning VBA, the next task you have to automate will benefit from what you learn in this exercise.

 

[jesusoneez]

Nope, NOT an access guru. SwiftCS is my own domain name, so anything can go before "@swiftcs.co.uk". I organise Outlook that way. Everything to that e-mail address automatically goes to an Access folder...helps to filter out spam when I join websites (I use crap@swiftcs.co.uk for that). Anyway, sidetracked.

I could probably pseudo it out on paper, it's just knowing the code to put in place of the pseudo. I'd also need to find out about concatenation functions I guess...

I'll give it a whirl and see where I get...

Thanks,

Steve

 

[reclusivemonkey]

I could probably pseudo it out on paper, it's just knowing the code to put in place of the pseudo. I'd also need to find out about concatenation functions I guess...

Steve,

Thats by far the best way to do it IMHO. This way, you know exactly what you want to do in plain english, then you can see how this is done in code. If you post your pseudo code, I should be able to help you turn it into VBA without too much trouble (and there are plenty of experts here to spot any schoolboy errors I make ;-)

 

[allan57]

Steve

A word of warning

If you do master the renaming of your tables, any forms, reports and queries that currently use these tables will no longer function correctly.

Allan

 

[jesusoneez]

Thanks, I'm aware of that. Luckily the database is just a collection of tables at the moment with no relationships set up (I believe they were imported from elsewhere).

Ste

 

[jesusoneez]

Pseudo(ish)

define variable varTableName as a string

for varTableName = the name of a table
remove the "STUD_ADMIN_" from varTableName
rename the table as varTableName
next table

end somewhat messily

Ste

 

[allan57]

Steve

Try the following code:-

Dim dbs As Database
Dim tdf As TableDef

On Error Resume Next

Set dbs = CurrentDb

dbs.TableDefs.Refresh

For Each tdf In dbs.TableDefs

If tdf.Name Like "STUD_ADMIN_*" Then

tdf.Name = "tbl" & MID(tdf.Name, 12, 255)

End If

Next

Set tdf = Nothing
dbs.Close
Set dbs = Nothing

I have only tested it on Access 97 and works OK

Hope that helps



Allan

 

[jesusoneez]

Thanks,

I'll have a play with that tomorrow (I work at a school so I finish early).

Ste

 

[namliam]

Steve

Try the following code:-

Dim dbs As Database
Dim tdf As TableDef
    
On Error Resume Next
    
    Set dbs = CurrentDb
    
    dbs.TableDefs.Refresh
    
    For Each tdf In dbs.TableDefs
        
        If tdf.Name Like "STUD_ADMIN_*" Then
           
            tdf.Name = "tbl" & MID(tdf.Name, 12, 255)
        
        End If
   
    Next
 
    Set tdf = Nothing
    dbs.Close
    Set dbs = Nothing

I have only tested it on Access 97 and works OK

Hope that helps



Allan

Yep that is about it... I would change a few things tho...

Use the [] tags for code and /code to make it look like this, which is more readable...

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef

To prevent problems in newer versions than 97. Make sure to add Microsoft DAO3.something to the references in newer versions tho!
How to add this? That used to be in the FAQ, Not sure if it still is....

tdf.Name = "tbl" & MID(tdf.Name, 12, 255)
change it to
tdf.Name = "tbl" & MID(tdf.Name, 12)
Its the same but a little more clear... Take the rest after....

NOTE to jesusoneez: Note that it uses 12 not 11, Mid starts before character #12 not after #11....

 

[jesusoneez]

Sorry I haven't got back to you guys sooner on this. The guy who was trying to do this ended up taking a copy of the database home and renaming them all manually...took him about three hours!

It's something that happens oncea year though so your help won't be a total waste.

Thanks,

Steve Swallow

 

[ColinEssex]

If you have 100 tables in your database, you have a problem. Also, if you need to rename tables once a year, you have a problem.

Your database is not "normalised" - spend time checking it out on this forum. It will make your database run correctly and save loads of time.

Col

 

[jesusoneez]

If you have 100 tables in your database, you have a problem. Also, if you need to rename tables once a year, you have a problem.

Your database is not "normalised" - spend time checking it out on this forum. It will make your database run correctly and save loads of time.

Col

It's not a database of our design, it's some third party thing specifically for schools. The company sends out an update by way of these table roughly once a year but they're all named wrong, every time, every year, despite us telling them over and over.

Why there's so many tables I don't know, but it is a big system...should be running on SQL in my opinion...on the other hand, no-one is complaining of slowdown.

Hopefully this is the last time this will happen as we're moving over to something a bit flasher that uses a SQL backend and internet portal front end...basically the software is for teacher taking registers, making reports on students, checking a calendar of event and many other functions.

Steve

 

[OPGForce]

Hi all,

I used the following code, and it worked fine in "MS Access 2002 - SP2":

Function RenameTablePrefix()

Dim dbs As Variant
Dim tdf As Variant

On Error Resume Next

Set dbs = CurrentDb

dbs.TableDefs.Refresh

For Each tdf In dbs.TableDefs

If tdf.Name Like "QA_620_NEW_*" Then

tdf.Name = Mid(tdf.Name, 12)

End If

Next

Set tdf = Nothing
dbs.Close
Set dbs = Nothing

End Function


Only issue I'm concerned about is that I had to declare the variables as "Variant" because the following declaration didn't compile:

Dim dbs As Database
Dim tdf As TableDef

And..

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef


Any ideas why these data types don't work?

Thanks.