How to programmatically rename table names in access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have an access database linked to sql server database. The name of the
tables are: dbo_Student, dbo_Score etc. There are altogether 20 tables. How
does one rename the tables programmatically by taking out the dbo_ prefix
from each of the tables. Thanks.
 
Jack

Why?

Each time you (re-)link to those tables, you'd have to (re-)remove the
prefix. Why not just leave the dbo_ prefix as part of the tablename?

Please note -- I'm not asking out of prurient interest, but to try to
determine what your underlying business need is to change the table names.
More specific descriptions lead to more specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef

Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
If Left$(tdfCurr.Name, 4) = "dbo_" Then
tdfCurr.Name = Mid$(tdfCurr.Name, 5)
End If
Next tdfCurr
 
Hi,
I have an access database linked to sql server database. The name of the
tables are: dbo_Student, dbo_Score etc. There are altogether 20 tables. How
does one rename the tables programmatically by taking out the dbo_ prefix
from each of the tables. Thanks.
Click to expand...

I agree with Jeff that this is often unnecessary - you can use captions,
forms, aliases etc. to display any name for any table. But (because I have a
frontend that must be able to link to JET or SQL tables), I did toss this
little routine together:

Public Sub renamedbo()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
For Each tdf In db.TableDefs
If Left(tdf.Name, 4) = "dbo_" Then
tdf.Name = Mid(tdf.Name, 5)
End If
Next tdf
End Sub

John W. Vinson [MVP]
 
Not sure if you'll get this or not, since the post is over three years old, but I saw your name and thought I'd say "Hi".

Not too many of us old timers around left from the CompuServe days<g>

Jim.
Hi,
I have an access database linked to sql server database. The name of the
tables are: dbo_Student, dbo_Score etc. There are altogether 20 tables. How
does one rename the tables programmatically by taking out the dbo_ prefix
from each of the tables. Thanks.
On Monday, October 29, 2007 6:35 PM Jeff Boyce wrote:
Jack

Why?

Each time you (re-)link to those tables, you'd have to (re-)remove the
prefix. Why not just leave the dbo_ prefix as part of the tablename?

Please note -- I'm not asking out of prurient interest, but to try to
determine what your underlying business need is to change the table names.
More specific descriptions lead to more specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

news:[email protected]...
Click to expand...
Click to expand...
 
Jim Dettman said:
Not sure if you'll get this or not, since the post is over three years
old, but I saw your name and thought I'd say "Hi".

Not too many of us old timers around left from the CompuServe days<g>

Jim.
Click to expand...

Oh there's one or two of us knocking about. Hi Jim.
 
Actually Jim, I'm not sure John monitors this group any more. You may have
more luck finding him on formscoding or comp.databases.ms-access.
 
Actually Jim, I'm not sure John monitors this group any more. You may have
more luck finding him on formscoding or comp.databases.ms-access.
 
Not sure if you'll get this or not, since the post is over three years old, but I saw your name and thought I'd say "Hi".

Not too many of us old timers around left from the CompuServe days<g>

Jim.
Click to expand...

Hi Jim. Still here, crazy as ever - just got back from a week offline (camping
in National Parks without even cellphone service much less internet).

Of course these groups are going the way of CompuServe, alas.

Good to see you around!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
Back
Top