vba to strip schema prefix from ODBC link tables 1

[DBAwhosaysNIE]

I used to have code to do this years ago but have need for it again. basically if you create a bunch of link tables (from Oracle in my case) it puts <schema_> as a prefix to <table_name>, i.e. you end up with schema_table_name. does anyone have a example or link to an example of the vba to strip the schema prefixes from the object names?

 

[PaulF]

Here is some code that will change the name of an ODBC table that contains an underscore in it. It will only remove the data up to the 1st underscore it finds in the name. May not be the best way to do it, but it works.



Dim db As DAO.Database, rst As DAO.Recordset, tdf As DAO.TableDef
Set db = CurrentDb
Set rst = db.OpenRecordset("MsysObjects")

With rst
.MoveFirst
Do While Not .EOF
If rst!Type = 4 Then 'ODBC connection
If InStr(rst!Name, "_") > 0 Then 'Contains a underscore in the name
Set tdf = db.TableDefs(rst!Name)
tdf.Name = Mid(tdf.Name, InStr(rst!Name, "_") + 1)
End If
End If
.MoveNext
Loop
Set db = Nothing
Set rst = Nothing
Set tdf = Nothing
MsgBox "done"



PaulF