1

I'm having an issue where I've created an Access 2003 ADP and connected to a database on SQL Server 2008 R2. On my PC, the table and view names are the same as in the SQL db (e.g., Table1, View1, etc), and I linked the report forms I built to these tables using those names. However, when I granted a colleague permission to the SQL backend so she can open the ADP and run the reports I built, it fails because on her PC a "dbo_" prefix appears on each table and object name (e.g., dbo_Table1, dbo_View1, etc), which of course breaks the connection to the data source for her. I'm stuck with Access 2003 at the moment. Is there a way to control this either in Access or in the back end? I did change her schema in SQL to dbo as an attempt to fix this, no dice. Thoughts appreciated!

Clint Finch
  • 101
  • 1
  • 2
  • 8

1 Answers1

2

The team I work on uses a small VBA utility to remove all those "dbo_" prefixes before running code against newly linked tables. It works well for us. You can add it in your Access application in whatever way is most convenient for you. Hope it helps. Good luck with your project.

Public Sub Zap_dboLabelsOnLinks()

    Dim dbDef As Database
    Dim tblDef As TableDef

    Set dbDef = CurrentDb

    'loop through each table in the database
    For Each tblDef In dbDef.TableDefs  
        If UCase(Left(tblDef.Name, 4)) = UCase("dbo_") Then
            tblDef.Name = Right(tblDef.Name, (Len(tblDef.Name) - 4))
        End If    
    Next tblDef

Exit Sub
Marty
  • 1,896
  • 1
  • 12
  • 11
  • You can remove the "dtmEnterTime = Now()" piece as that sub doesn't use the variable at all. I'm guessing there's some code that uses that to determine how long it took to process? – Johnny Bones Aug 12 '14 at 19:58
  • Yes - and thank you for the suggestion @JohnnyBones. That statement incorrectly survived my cut/paste. I've edited the answer to correct it. – Marty Aug 12 '14 at 20:03
  • You got the DIM statement, but not the actual line of code that I mentioned above. :o) – Johnny Bones Aug 12 '14 at 20:23
  • I hate it when that happens! :-) Corrected. – Marty Aug 12 '14 at 20:30