Is there a workaround for the dbo_ bug?

[GuideRWhite]

As long as I can remember, Access has had a bug in it where it would rename your tables when you attach them from ODBC.

Does anyone know how to attach remote tables without it putting dbo_ in front of the real table name?

[June7]

I have links to dBase and Oracle tables and don't see a dbo_ prefix. Not sure what you are doing that causes this.

[GuideRWhite]

If I go to "External Data", "ODBC", then I choose my datasource and browse to my tables - when I go back to Access they all have dbo_ before them. So I have to rename every one or my code won't work.

[June7]

Was 'dbo' a generic reference in your example? My links to Oracle db have a prefix of the Oracle db name, like: PRODUCTION_AIRPORT.

No, don't know anyway around that. Don't think I would call that a 'bug'. Not an issue for me. I don't have any reason to change the name to eliminate prefix.

However, might be able to automate the renaming with VBA code. Google: VBA rename table
http://www.access-programmers.co.uk/...d.php?t=168515

[GuideRWhite]

"select * from tblcontacts" won't work if tblcontacts is spelled dbo_tblcontacts

I'm sure MS had a reason for doing this, but it would surprise me if anyone liked the idea of their tables automatically getting renamed.

[June7]

Your only options, however distasteful, appear to be:

1. change your code, queries, reports, forms

2. change the table names, try automating with code

[ssanfu]

Did you try the code? I did a little modifying, but it works great!

Option Compare Database
Option Explicit

Sub RenameTables()
   Dim dbCurr As DAO.Database
   Dim intLoop As Integer
   Dim tblName As String
   Dim tblNewName As String
   Dim str2Replace As String

   str2Replace = "dbo_"

   Set dbCurr = CurrentDb()

   For intLoop = (dbCurr.TableDefs.Count - 1) To 0 Step -1

      If Left$(dbCurr.TableDefs(intLoop).Name, 4) = str2Replace Then
         tblName = dbCurr.TableDefs(intLoop).Name
         tblNewName = Replace(tblName, str2Replace, "")
         'alternate method
         '  tblNewName = Mid(tblName, 5, Len(tblName) - 4)

         dbCurr.TableDefs(intLoop).Name = tblNewName
      End If

   Next intLoop

   Set dbCurr = Nothing

End Sub

I will be using it. Much better than manual method.... Thanks June7

[GuideRWhite]

It just seems like after all these versions of Access, they could put a checkbox that says "don't rename my tables" on the table selector page. I'm sure nearly 100% of users would check it.

[ssanfu]

I would say it would be SQL Server's "fault". SQL Server creats tables names with "dbo_" as a prefix.

When creating links, Access appears to use the default object name. If you link to a text file, the linked table name in Access is the name of the text file. So when you link to SQL Server tables, you get an Access linked table name beginning with "dbo_". Access isn't renameing the linked object name.

My $0.02 ........

[GuideRWhite]

So, if I use a non-Microsoft backend will it not rename my tables?

[June7]

As stated in earlier post I link to Oracle which must be prefixing tables with db name.

I also link to dBase tables and there is no prefix, tables are not renamed.

[ssanfu]

Again, whatever the linked object name is, that is the name that Access will give it. (Same as what June7 stated)