Hello, Is there a way I can rena 2

[mikeyb540]

Hello,

Is there a way I can rename Tables in the MSysObjects table?

Txs in advance.

 

[vbajock]

Sounds risky. Why would you want to do that?

 

[mikeyb540]

I have 230 linked SQL server tables.

When linked i get the dbo_table1 and the code refrences to table1.

Any other way I can link the tables without the dbo?

Txs

 

[vbajock]

Do it on a BACKUP COPY. Set a reference to the DAO 3.6 object library

Public Sub RenameAllTables(strToFind As String, strToReplace)

On Error GoTo ErrHandler

Dim dbs As Database
Dim tdf As TableDef

Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
Dim tdfNewName As String


if mid$(tdf.name,1,4)="dbo_"


tdfNewName = mid$(tdf.Name,4,255)


DoCmd.Rename tdfNewName, acTable, tdf.Name


endif


NextTdf:
Next

Cleanexit:
Exit Sub

ErrHandler:
MsgBox Err.Number & " - " & Err.description, vbOKOnly
Resume NextTdf
GoTo Cleanexit
End Sub

 

[vbajock]

Just ignore (strToFind As String, strToReplace), it doesn't belong in the snippet I gave you

 

[mikeyb540]

You the man it worked

But it renamed them as _table1 ...etc
hmmm... how do I remove the _?

Txs

 

[mikeyb540]

I tried
If Mid$(tdf.Name, 1, 1) = "_" Then
tdfNewName = Mid$(tdf.Name, 1, 255)

doesnt do anything

 

[Golom]

That's 'cause you need

tdfNewName = Mid$(tdf.Name, 2, 255)

 

[mikeyb540]

That did it...

Txs

 

[vbajock]

Sorry about that! You might also want wrap it in a trim function

tdfNewName = Trim$(Mid$(tdf.Name, 2, 255))

so you don't get any unwanted spaces

 

[mikeyb540]

Vbajock,

Txs for all you help.

Happy Holidays