Table names when Attaching to an ODBC Datasource (XP)

Topic

New Reply

I have an Access front end that is used with both Jet and SQL backends. When I attach to the SQL backend the tables are named with the dbo_ prefix which I am having to manually rename. I have tried to iterate through the msysObject table to rename them but it is read only. Is there a way to rename the tables in code or is there another strategy to avoid this problem?

Reply | Quote

Replies

You can use DoCmd.Rename to rename database objects. You can loop through the MSysObjects table, or use code like this:

Sub RenameSQLTables()
Dim obj As AccessObject
For Each obj In CurrentData.AllTables
If Left(obj.Name, 4) = “dbo_” Then
DoCmd.Rename Mid(obj.Name, 5), acTable, obj.Name
End If
Next obj
Set obj = Nothing
End Sub

Reply | Quote

WOW! that was fast – thank you.

Reply | Quote

WOW! that was fast – thank you.

Reply | Quote