Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    Table names when Attaching to an ODBC Datasource (XP)

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Table names when Attaching to an ODBC Datasource (XP)

    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

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Table names when Attaching to an ODBC Datasour

    WOW! that was fast - thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •