[AccessD] Update Name Mapping for Name Autocorrect

Hello to everyone,

I like the concept of the Name Autocorrect option in Access, but everyone
seems to say to stay away from it.  I did a search in the Archive and
learned about Name Mapping in Access.  In the Archives you'll find an entry
with 'TsiNameFixup90' in the subject line from Sad Der dated 7/20/06.  This
last half of this entry explains what name mapping is.

So, to get name mapping up to date, I wrote the following procedure.  It
opens most objects in design view and then saves them.  Now I can use Name
Autocorrect as it was intended.  Time will tell if it helps or hurts!

If you want to give it a try, copy the following code into a standard module
and run it.

Good Luck!
Dan

----------------------------------------------------------------------------


Option Compare Database
Option Explicit

Private Sub ResetObjectNameMaps()
On Error GoTo EH

    '-- This procedure will open almost all of the tables, queries, forms,
and reports in design view, and then close them _
        with acSaveYes.  This will prepare these objects to be able to
correctly take advantage _
        of the Name Autocorrect option.  There is a behind the scenes
functionality called 'Name Mapping' that is managed by Access _
        when Name Autocorrect is turned on.  If it has been off, then each
table, query, report, and form must be _
        opened and saved in design view to completely update Name Mapping.

    '-- 1) Turn on Perform Name AutoCorrect in Access options. _
        2) Second, run this procedure. _
        3) Then leave Perform Name Autocorrect on.
        
    Dim tbl As TableDef
    Dim qry As QueryDef
    Dim frm As Object
    Dim rpt As Object
    Dim stgName As String
    Dim intObjectCount As Integer
    
    Application.Echo False
    
    '-- Tables
    For Each tbl In CurrentDb.TableDefs
        '-- Skip linked tables or Access System tables
        If Not (tbl.Attributes = dbAttachedTable Or tbl.Attributes =
dbAttachedODBC Or Left(tbl.Name, 4) = "Msys") Then
            intObjectCount = intObjectCount + 1
            stgName = tbl.Name
            DoCmd.OpenTable stgName, acViewDesign, acEdit
            DoCmd.Close acTable, stgName, acSaveYes
        End If
    Next
    MsgBox intObjectCount & " Tables!"
    intObjectCount = 0

    '-- Queries
    '-- Note:  If a query has Owner permission and you are not the Owner,
this will error out.
    For Each qry In CurrentDb.QueryDefs
        If Not (Left(qry.Name, 4)) = "~sq_" Then
            intObjectCount = intObjectCount + 1
            stgName = qry.Name
            DoCmd.OpenQuery stgName, acViewDesign, acEdit
            DoCmd.Close acQuery, stgName, acSaveYes
        End If
    Next
    MsgBox intObjectCount & " Queries!"
    intObjectCount = 0
    
    '-- Forms
    For Each frm In Application.CurrentProject.AllForms
        intObjectCount = intObjectCount + 1
        stgName = frm.Name
        DoCmd.OpenForm stgName, acDesign
        DoCmd.Close acForm, stgName, acSaveYes
    Next
    MsgBox intObjectCount & " Forms!"
    intObjectCount = 0
    
    '-- Reports
    For Each rpt In Application.CurrentProject.AllReports
        intObjectCount = intObjectCount + 1
        stgName = rpt.Name
        DoCmd.OpenReport stgName, acDesign
        DoCmd.Close acReport, stgName, acSaveYes
    Next
    MsgBox intObjectCount & " Reports!"
    
    Application.Echo True
    
    Exit Sub
    
EH:
    Application.Echo True
    MsgBox Err.Number & ":  " & Err.Description & vbCrLf & vbCrLf & "Object
Name: " & stgName
    
End Sub