Prevent Dbo Prefix To Linked Tables

[doco]

Is there a way to prevent Access from the absurd notion it needs to concatenate 'DBO_' as prefix to tables linked from SQL Server db?

Thanks.

 

[boblarson]

No, but you can just rename them.

 

[doco]

Yup. And that is a pain. Not bad if just linking a couple tables. O well, it is what it is...

Thanks...

 

[boblarson]

Create a module to iterate through the table Defs and rename them.

 

[doco]

Create a module to iterate through the table Defs and rename them.

Yes! And indeed I will. I'm sure MS had a good reason for going through the excersize. But just like that simple solution - it escapes me at the moment. Thanks Bob.

I go through Portland often, maybe I can buy you a coffee/brewski sometime?

 

[gold007eye]

Create a module to iterate through the table Defs and rename them.

How exactly would one go about doing this; as I am looking for a quick way to be able to remove the "DBO_" prefix from SQL linked tables.

 

[doco]

'-------------------------------------------------------------------------------
' Procedure     :   TruncateDBO
' DateTime      :   3/13/2008 06:38
' Author        :   Donald R. Cossitt
'               :
' Purpose       :   MS has the completely absurd notion that it is somehow 
'               :   important to append 'DBO_' and or other database owner as a 
'               :   prefix to linked tables. This routine truncates 'DBO_' from 
'               :   the tables in the tabledefs collection having that 
'               :   particular malady. However, will truncate any string 
'               :   indicated by the user.
'-------------------------------------------------------------------------------
'
Public Sub TruncateDBO()
Dim tdfTable As DAO.TableDef
Dim i As Integer
Dim szDBOwner As String

    szDBOwner = InputBox("Enter prefix to truncate. [case sensitive]", _
                         "TRUNCATE DB OWNER", "dbo_")
    
    For Each tdfTable In CurrentDb.TableDefs
        If Left(tdfTable.Name, Len(szDBOwner)) = szDBOwner Then
            i = i + 1
            tdfTable.Name = Right(tdfTable.Name, _
                            Len(tdfTable.Name) - Len(szDBOwner))
        End If
    Next tdfTable

    MsgBox "Truncated : " & i & " table names", vbInformation, "'DBO_' GONE"
    
End Sub

This is what I did after Bob's suggestion...

HTH

 

[gold007eye]

Thanks for the code. Where exactly would I impliment this code or put it to make it work? Is this done in the Access FE or on the SQL side? Not sure how to use it. Thanks again for the help.

 

[georgedwilkinson]

Put it in a module and run it once (or as often as needed) with the ">" button in VBA.

 

[doco]

There are a number of ways. I just open the VBE and place my cursor inside the routine and hit F5.
You could create a command button on a user form or
You could replace the word 'Sub' with 'Function', then create a macro that runs the function Or
You could create a custom toolbar with a custom menu item that calls the routine when menu item is clicked.

 

[boblarson]

Hey Doco, great code! I just used it this morning on a database I'm working on because I had to relink to SQL Server and got all of the dbo_ but the db wasn't using that so I had to get rid of all of them so it would work and the code worked like a charm.

I'm adding to your reputation (and if you notice that when I add rep you get a LOT of points due to my length of time on the board and number of posts).

Thanks again - you needed help and then I did, so it worked out beautifully

 

[doco]

Very glad to have been of service.

 

[scottmsoule]

I just used that handy little program! That saved me so much time! THANK YOU, THANK YOU ,THANK YOU!!!

 

[ShellyMassey]

I have been lurking on this forum for about six months, but TODAY I registered specifically to say THANK YOU to doco for the code to remove dbo_. You saved me a ton of time!

 

[SOS]

A word of caution though - If you don't need to relink often, not a problem. But if you do, it can be a real pain so I just learned to deal with it.