Code to rename tables

[LisaB]

I have just imported 50 tables from SQL to Access and they all have the dbo
name in front of the table name

dbo_tblCustomers

I would like to know if there is VBA code to go through the table and drop
the "dbo_ " that appears before each tables name.


Thank you

 

[Gerald Stanley]

Try something along the lines of
Dim tbl As DAO.tabledef

For Each tbl In TableDefs
If Left$(tbl.Name, 4) = "dbo_" Then
tbl.Name = Mid$(tbl.Name, 5)
End If
Next

Hope This Helps
Gerald Stanley MCSD

 

[Chris Nebinger]

I thought the Name property was read-only. I had to try
this to verify it.....


I always used the DoCmd.Rename method to rename objects...

I did find that QueryDef.Name is also read/write, but
dbs.Containers("Forms").Documents("Form1").Name is Read
Only....

I guess you learn something every day.


Chris Nebinger

 

[LisaB]

I get a runtime error 424 Object required

Dim tbl As TableDef
Dim TD As TableDefs

For Each tbl In TD ****(Error - code stops Here)*****
If Left$(tbl.Name, 4) = "dbo_" Then
tbl.Name = Mid$(tbl.Name, 5)
End If
Next

 

[Gerald Stanley]

Try

For Each tbl In CurrentDb.TD

Hope This Helps
Gerald Stanley MCSD

 

[Douglas J. Steele]

Actually, you don't need the TD object, but you want to instantiate a
reference to the Database:

Dim db As DAO.Database
Dim tbl As DAO.TableDef

Set db = CurrentDb()
For Each tbl In db.TableDefs
If Left$(tbl.Name, 4) = "dbo_" Then
tbl.Name = Mid$(tbl.Name, 5)
End If
Next tbl


For Each tbl In CurrentDb().TableDefs

 

[Paul Johnson]

Your runtime error is because your code doesn't set the object variables to
anything.

Need:
Set TD=CurrentDb.TableDefs
Then the For Each loop should be able to return the tabledefs.

Paul Johnson