Renaming Access table

[Guest]

Hi,
I have connected a Access database to a sql server database. Thus the tables
are linked and show as dbo_table1, dbo_table2 etc. I need to rename the
linked tables in Access so that the dbo_ part is deleted. Thus the lined
tables will be table1, table2 etc. This needs to be done programmatically as
there are about 500 linked table. I appreciate any help on this.
Thanks

 

[tina]

try

Public Sub isChangeName()

Dim tbl As TableDef

For Each tbl In CurrentDb.TableDefs
If Not tbl.Name Like "MSys*" Then
tbl.Name = Right(tbl.Name, Len(tbl.Name) - 4)
End If
Next tbl

MsgBox "done"

End Sub

note that the If statement excludes the system tables; if there are other
tables that you don't want changed, include exclusion criteria for those in
the If statement, as well.

hth

 

[Guest]

Thanks Tina so much for your help. It worked wonders. Is there any good
Access VBA book that you can suggest. I have decent Sql experience. However,
I am new to Access programming. Thanks

 

[tina]

you're welcome.
as for a VBA book, there's none i can recommend offhand. i got an
introduction to VBA in a data modeling class (of all places!), quite some
years ago. since then i'm mostly self-taught; learning to write code by
writing it - and by reading lots and lots of other people's code. one way to
begin to learn some VBA is to take any macros you have, and convert them to
VBA (on the database window's menu bar, look under "Tools | Macro | Convert
Macros to Visual Basic" in A2003, and probably similar location in A2000 and
A2002 as well). in the VB Editor window, you can place your cursor in
various spots in the code and press F1 to read up on the Help topic. for
example, using the | as the cursor:

DoCmd.OpenFo|rm

press F1 and it brings up the OpenForm topic in VBA Help. also, suggest you
google these newsgroup; i'm sure others have asked about a good VBA book,
and gotten various recommendations. VBA is powerful stuff, and really moves
you to the next level in application development - have fun with it, and
good luck!

hth